Excel data extraction - problem with column data type

Question

I am writing a C # library to read in Excel files (both xls and xlsx) and I am facing a problem.

Just as was expressed in this question , if my Excel file has a column with string values ​​but has a numeric value in the first row, the OLEDB provider assumes the column will be numeric and returns NULL for values ​​in this column that are not numeric .

I know that in the provided answer I can make changes to the registry, but since this is a library that I plan to use on many machines and do not want to change the values ​​of each registry user, I was wondering if there is a better solution.

Perhaps a database provider other than ACE.OLEDB (and it seems that JET is no longer supported well enough to be considered)?

In addition, since this is necessary for working with XLS / XLSX, parameters such as EPPlus / XML readers will not work for xls.

Answer 1

The connection string should look like this:

 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcelfile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"; 

IMEX = 1 in the connection string is the part that needs to be processed by the column as a mixed data type. This should work fine, without having to edit the registry.

HDR = Yes - just mark the first row as column headings and not needed in your specific problem, however I included it anyway.

To always use IMEX = 1, this is a safer way to get data for mixed data columns.

Source: https://www.connectionstrings.com/excel/

Edit:

Here are the data I use:

Here is the result:

This is the exact code I used:

 string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""; using (DbClass db = new DbClass(connString)) { var x = db.dataReader("SELECT * FROM [Sheet1$]"); while (x.Read()) { for (int i = 0; i < x.FieldCount; i++) Console.Write(x[i] + "\t"); Console.WriteLine(""); } } 

DbClass is a simple wrapper that I made to make life easier. It can be found here:

http://tech.reboot.pro/showthread.php?tid=4713