Importing from Excel

[Amy]

I have an ASP.NET (VB) routine that imports a lot of data from an Excel

spreadsheet into a OleDbDataAdapter, and then into a database table.
The import
has been working fine until now, but my latest spreadsheet has a column

that can be either completely numeric or alphanumeric. As long as the
first row contains alphanumeric everything is fine, but the latest
spreadsheet I received contained completely numeric data in the first
10 rows. This caused some of the alphanumeric data to not be read in.
I have been using IMEX=1 in my connection string which fixed this type
of situation I had when I first started working on this project, but it

doesn't seem to be working on this particular field.

Any suggestions?

Thanks,
Amy Bolden

 

[Amy]

I was able to solve my problem by putting HDR=No in the connection
string. Then I insert the header text as the first record for the
field, thereby forcing Excel to make the field a string. This seems to
work well, although it is a little goofy.
Thanks,
Amy

 

[Terry Olsen]

Amy,

Could you please post your entire connection string? I have the same
problem, but when I added HDR=No to my connection string, I get "Unable
to find installable ISAM" on the Connection.Open method.

Thanks,
Terry

 

[Terry Olsen]

Nevermind. I figured out the connection string but it didn't solve my
problem.

I have an excel worksheet that i'm trying to read. One column contains
Customer ID's. Some are alpha numeric, some are numeric. The numeric
data doesn't get read in, even using Amy's suggestion.

5AC170
T99518
Y99887
6M9Z56
975219 <- All numeric, doesn't read in.
80090L
74Q248
0506XG

The connection string i'm using is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelFile.xls;Extended
Properties="Excel 8.0;HDR=Yes"

Using HDR=No doesn't work either.

Thanks for any help.

 

[Guest]

Hi Terry, I think you need to add IMEX=1 parameter in the Extended
properties of the connection string.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\file.xls;Extended
Properties="Excel 8.0;IMEX=1;"

The only problem is that it converts the big numeric values to the format
9.9+e9, example 300015532 -> 3.00016e+008.

If you fix this problem please let me know


Regards