Importing from Excel

[Amy]

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

spreadsheet into a OleDbDataAdapte r, 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]

Re: Importing from Excel

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]

Re: Importing from Excel

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


*** Sent via Developersdex http://www.developersdex.com ***

[Terry Olsen]

Re: Importing from Excel

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=Micros oft.Jet.OLEDB.4 .0;Data Source=ExcelFil e.xls;Extended
Properties="Exc el 8.0;HDR=Yes"

Using HDR=No doesn't work either.

Thanks for any help.

*** Sent via Developersdex http://www.developersdex.com ***

[JoseValencia]

Re: Importing from Excel

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

Provider=Micros oft.Jet.OLEDB.4 .0;Data Source=C:\\file .xls;Extended
Properties="Exc el 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
"Terry Olsen" wrote:
[color=blue]
> 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=Micros oft.Jet.OLEDB.4 .0;Data Source=ExcelFil e.xls;Extended
> Properties="Exc el 8.0;HDR=Yes"
>
> Using HDR=No doesn't work either.
>
> Thanks for any help.
>
> *** Sent via Developersdex http://www.developersdex.com ***
>[/color]