By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,326 Members | 881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,326 IT Pros & Developers. It's quick & easy.

Importing from Excel

P: n/a
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

Mar 31 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Mar 31 '06 #2

P: n/a
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 ***
Mar 31 '06 #3

P: n/a
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.

*** Sent via Developersdex http://www.developersdex.com ***
Mar 31 '06 #4

P: n/a
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
"Terry Olsen" wrote:
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.

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

Jun 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.