Excel
-
Ok, This is a coding horror, but this one belongs to Microsoft. As it turns out, if you are trying to connect to an Excel spreadsheet using the OLEDB driver, it tries to infer the datatypes of the columns in your spreadsheet based on the first few (8 by default) rows. All well and good, except when the first 8 rows have numeric values for a particular column, but some of the others have alphanumeric. Then, the driver just inserts nulls (because data integrity isn't important). Now, you can somewhat fix this with a change to a couple of registry keys and a change to your connection string, but it seems really dumb to have to do that. Check this craziness out. http://www.sqldts.com/254.aspx And no, I'm not submitting this to get help. I'm doing the registry stupidity instead and hoping that it doesn't randomly get broken by admins on the web server.
-
Ok, This is a coding horror, but this one belongs to Microsoft. As it turns out, if you are trying to connect to an Excel spreadsheet using the OLEDB driver, it tries to infer the datatypes of the columns in your spreadsheet based on the first few (8 by default) rows. All well and good, except when the first 8 rows have numeric values for a particular column, but some of the others have alphanumeric. Then, the driver just inserts nulls (because data integrity isn't important). Now, you can somewhat fix this with a change to a couple of registry keys and a change to your connection string, but it seems really dumb to have to do that. Check this craziness out. http://www.sqldts.com/254.aspx And no, I'm not submitting this to get help. I'm doing the registry stupidity instead and hoping that it doesn't randomly get broken by admins on the web server.
Oh, yeah, I've bumped up against that sort of thing before too, but I've forgotten how I dealt with it, certainly not the (evil) registry. It's been... so long.
-
Oh, yeah, I've bumped up against that sort of thing before too, but I've forgotten how I dealt with it, certainly not the (evil) registry. It's been... so long.
I've dealt with this one. dang what was it. it was an easy fix. Here it is: System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _ "data source=" & result & ";Extended Properties='Excel 8.0;IMEX=1';") note the use of the extended properties and how they have single quotes. [edit] the above treats everything as a string, but does solve the null issue. I found the code from when I had to do a similar program a while back.