Excel, OleDb, and Leading zeros

Question

I have a process that using OleDb reads data from an excel file into a DataSet. Everything was working well until I started to see data with leading 0's in it. The text is formatted as General or Text.

I have set IMEX=1 in the OleDb connection, yet I still get a null value in the dataset for each entry with a leading 0.

Does anyone know a way around this that WORKS?

Edit

As a bit of added information, I MUST keep the leading 0's and the column is formatted as General. I have also tried Text.

The first entry that has a leading zero is in row 11 and a null is returned. All other data on this specific sheet is numbers for this column.

Answer 1

I bet the data is stored in the Excel spreadsheet as text; because it wouldn't natively have leading zeroes (although the cells could be formatted to show them.) I expect OleDb is asking Excel what datatype, and Excel thinks they are strings.

Look at one of the Excel cells nad see if it has a loading zero in the data input strip at the top; or does it have a leading apostrophe or some such?

If ihis is the case (or even if it's not, but you want unformatted data), one way off the top of my head would be to copy the worksheet to another worksheet and transform the Excel column with the Val(address) function, and reformat it without leading zeroes; then read from that.

Answer 2

The best thing that I can see is that, you have to create a preformatted excel file, then use it for the data that you are requiring.

Answer 3

Are your first 10 rows pure numeric data?

Then the driver is making a judgment call that the whole column is numeric. It chokes when it runs into data that doesn't fit. You are probably seeing this as NULL or 0.

I would try:
1) drop the IMEX - experiment without it
2) Try adding a single quote as the first character to all the data in that column.
This "forces text" in Excel. As a test just try it on the first 11 rows.
This should force the driver to see the column as text. The NULLs should disappear.

There is some way to increase the row "sample size"... but I can't remember the keyword to use in the connection string.