Values changing when importing from Excel with C#/OLEDB

Question

I'm trying to import some data from an Excel spreadsheet into my C# application using OleDB. I use the standard connection string with the IMEX=1,HDR=YES options

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0;HDR=YES;IMEX=1"

I have a column where the values are mostly the text: TRUE or FALSE. Partway down there is a couple of values of BLANK. When the import happens, the BLANK values are replaced empty strings. This is presumably because OleDB determined the data type of the column to be boolean and doesn't import any non-boolean values, like BLANK.

I tried the solution in this answer but it did not work. Even when I pre-create the DataTable and set its columns to all have a datatype of string, the BLANK values continue to be removed.

I have tried hacking the registry key suggested in the same question but to no effect.

Is there any recourse? Or am I going to have to try to use COM? We don't have the budget for SpreadsheetGear and I can't use excellibrary because I have to deal with .xlsx files mostly.

Thanks in advance

Answer 1

Try to save your Excel file to CSV format and process it.

Answer 2

I know I am three years late, but this might help somebody. All credit goes to Andrew Garrison who posted his answer here. You need to modify a registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\

Change TypeGuessRows to 0 and ImportMixedTypes to Text under this key.

ACE scans first 8 rows and tries to guess column formats. Everything else that doesn't match the format afterwards returns as NULL. Setting TypeGuessRow to 0 forces the driver to scan all rows to determine format (of course there is some performance hit). ImportMixedTypes=text specifies that columns with mixed format, as in your case, should be imported as text.