Excel via ODBC returning null/nothing for SOME numeric fields

[QuickBooksDev2]

We have a VB.Net (201) program that uses Excel (2013) via a ODBC connection string.

We have found that we get a value of null (or Nothing in VB) for SOME NUMERIC fields but not all. Changing the Excel cell format does not help. Putting in a single quotes '6000 does work (this makes it a string vs a number). This occurs on our development machine and on our client's machine. (Windows 7)

Have tried small numbers i.e. 2 or 3 and large ones i.e. 1235678 but only the single quote works.

It would be quite a big deal to change it to Interop.Excel, etc.

Any advice?

Happy New Year!

 

[RoryA]

What's the connection string? Are you using IMEX=1 which can help with columns that contain mixed data types.

 

[QuickBooksDev2]

Not using IMEX. Does not let me. Get Could not find installable ISAM

Here is string
provider=Microsoft.Jet.OLEDB.4.0;Data Source='......xls';Extended Properties=Excel 8.0;

Is there a better one?

 

[QuickBooksDev2]

I upgraded the connection string to
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES"

and it seems to work.

Thanks for pointing me in that direction.

 

[RoryA]

For the original one you would have used something like:

provider=Microsoft.Jet.OLEDB.4.0;Data Source='......xls';Extended Properties='Excel 8.0;IMEX=1;HDR=YES'