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.
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.