Excel field is confusing me! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Excel field is confusing me!

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-04-28 : 11:42:25

I have a spreadheet that has a column that is formatted by default to General and contains alpha numeric data.

1008
1008G
1008H

etc.

When I open the spreadsheet any field in the coumn that is purely numeric is right justified by default and the alpha numeric ones are left justified.

When I import the data the fields containing numeric only data come thru as NULL? But the alpha numeric comes thru OK.

The destination field in my table in varchar(20)

I have tried various formats including Text in the spreadsheet but I still get the numeric fields coming thru as NULL.

Any points please?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-28 : 11:52:48

Moved from Data Corruption Issues. This is not a database corruption issue.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-28 : 11:54:17

the column type is determined by the first 8 rows (i think that's the correct number) in the excel when importing.
set them explicitly to text in excel and you should be ok.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-04-29 : 03:45:09

HI, yes I already tried that, however whichever way I do it it still get the same result?

the contents are as follows:

row 1 = column heading (text)
row 2 = 1008
row 3 = 1008G
row 4 = 1008H
row 5 = 1109B
...
...
...
row 27 = 2108C
row 28 = 2108

so row 1 is a number then the rest are alpha numeric until row 28 and the mixing goes on for the entire speadsheet.

If I have the field set to text or general or whatever it is the same?
If I change row 1 to be 1008X it outputs correctly?
It just wants to class numbers as null??????

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-20 : 08:12:13

Ho, this has still got me stumped!

Anyone out there have any clues?......please?????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 08:36:02

Change IMEX mode to ensure all records are imported as text.

E 12°55'05.25"
N 56°04'39.16"

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-21 : 04:21:07

ooooooooooooooooow!

Thanks, had not heard of IMEX but it sounds promising!

I will try this out and post back, thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 04:59:46

See http://support.microsoft.com/kb/194124/en-us

quote:
The possible settings of IMEX are:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

E 12°55'05.25"
N 56°04'39.16"

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-21 : 10:44:35

Hi,

this now gives me the dreaded "cannot find installable ISAM" error.

This is the connection string I am using:

cnn = New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';IMEX=1; Extended Properties=Excel 8.0", strFilePath))

This worked OK, apart from the null values where a cell was all numbers, prior to adding the IMX=1 bit?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 10:53:05

IMEX is part of Extended Properties.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties="Excel 8.0;IMEX=1"

E 12°55'05.25"
N 56°04'39.16"

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-21 : 11:22:41

Hi,

I see that your example and the ones on the web show a quote before Excel but I assgin it as a string like so:

cnn = New System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';Extended Properties=Excel 8.0;IMEX=1", strFilePath))

Which during runtime comes out as:

provider=Microsoft.Jet.OLEDB.4.0; data source='C:\Documents and Settings\pp3\My Documents\Book2.xls';Extened Properties=Excel 8.0;IMEX=1

I have tried to double quote to encapsulate the Extended part to no avail?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-21 : 11:33:22

Interesting?

I have now got it to correctly encapsulate the Extended portion and it displays as you show but now I get a cannot connect to server error?

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2008-05-21 : 11:43:59

Ho, scrub that it all works a treat :)

The db connection error is further down in another section of code that I can deal with, the important thing it the IMEX bit works!

Thanks Peso....a star you are :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources