Values changing when importing from Excel with C#/OLEDB

Question

Hey all,

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

A lot of people having same problem with loading data from Excel.

Regardless what you use all Ole DB, ODBC or Jet the all work the same ways

For my point of view the best solution is to write macros and save excel file as flat file than it works,

OR IMEX=1,TypeGuessRows=1 and make sure that the first line of your excel file have data in expected format.

Eg only numbers, only dates, only characters, more or less than 255 characters.

There is no other way of doing it without modifying excel file

There are several Microsoft KB's of how it all is works (or does not work)

Here is condensed information

ODBC/MS Jet scans first TypeGuessRows to determine field type

Here how Excel ODBC/MS Jet works

(TypeGuessRows=8 IMEX=1)

In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.

In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.

In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.

In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters

In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters

NOTE:

Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default data type to Text; it will remain numeric.

More information and how to solve it

http://www.etl-tools.com/imex1.html