Search code examples
c#.netexceloledb

Excel driver not reading data as text even if IMEX=1

I'm trying to read an .Xlsx file as text using OleDB driver with the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;"

but still getting numbers and dates as number and dates. Killed hours on that.
What am I doing wrong?

Solution

  • IMEX=1 means that when the driver encounters mixed types in the first 8 rows that it should treat the column as text. Without it it will scan the first 8 rows, determine the data type and return null for those cells that do not correspond to the data type. It does not mean return everything as text.

    Sadly the Microsoft.ACE.OLEDB.12.0 provider ignores ImportMixedTypes and TypeGuessRows in the connection string. They are set in the Registry. This is backwards step in my opinion.

    If you have a header and it's text that will definately help return text for the columns. Try IMEX=1 and HDR=No and discard the header in subsequent processing.