Reading excel file using OLEDB Data Provider

Question

I am using OLEDB Data Provider to read excel file, but the problem is that in excel sheet some cloumn has an invalid value for example instead of number string is there, When I read this invalid value I get an empty string instead of actual value.

for above screenshot when i read value john getting empty string.

So is there any way to read this invalid value?

Any help will be appreciated.

The Code is to read excel file

private DataTable ReadExcelFile(string sheetName, string path)
{

    using (OleDbConnection conn = new OleDbConnection())
    {
        DataTable dt = new DataTable();
        string Import_FileName = path;
        string fileExtension = Path.GetExtension(Import_FileName);
        if (fileExtension == ".xls")
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
        if (fileExtension == ".xlsx")
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        using (OleDbCommand comm = new OleDbCommand())
        {
            comm.CommandText = "Select * from [" + sheetName + "$]";

            comm.Connection = conn;

            using (OleDbDataAdapter da = new OleDbDataAdapter())
            {
                da.SelectCommand = comm;
                da.Fill(dt);
                return dt;
            }

        }
    }
}

Belting

Answer 1

You need to set value for TypeGuessRows Registry key to 0, this way driver will set data type based on all column values instead of first 8 (default).

The location of the key differs from version to version of driver, you can easily Google it based on your specific version. For example for Access Connectivity Engine 2007 it would be

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

By the way, you do not need Jet to read XLS files, ACE is perfectly capable of this as well.

Maw

I think you can accomplish the same thing by setting ;IMEX=1" in the connection string after HDR=YES. – Maintopmast

@DougGlancy from what I remember there was a problem with this approach, but it's definitely worth a try and if it works, it's better than Registry editing. – Maw

IMEX=1 does not affect this. See this topic #10102649 – Indignity

Answer 2

This worked for me

        using (OleDbConnection conn = new OleDbConnection())
        {
            DataTable dt = new DataTable();
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
            + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";
            using (OleDbCommand comm = new OleDbCommand())
            {
                comm.CommandText = "Select * from [" + sheetName + "$]";
                comm.Connection = conn;
                using (OleDbDataAdapter da = new OleDbDataAdapter())
                {
                    da.SelectCommand = comm;
                    da.Fill(dt);
                    return dt;
                }
            }
        }

The MAXSCANROWS=0 overrides the registry default and scans all rows before determining types. IMEX=1 still needs to be included.

For example, given this table:

Header | Header
------ | ------
Cell1  | 2456354
Cell2  | 2456354
Cell3  | 2456354
Cell4  | 2456354
Cell5  | 2456354
Cell6  | 2456354
Cell7  | 2456354
Cell8  | 2456354
Cell9  | A5341

The following connection strings will lose A5341

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path 
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path
   + ";Extended Properties='Excel 12.0 Xml;HDR=YES;MAXSCANROWS=0'"

But it works when it has both.

Indignity