Reading Excel File Using Oledb Data Provider

Reading excel file using OLEDB Data Provider

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.

FROM clause when reading excel file using OLEDB Data Provider in c#

I figured it out after some trial and error. The following works.

var queryString = "SELECT * FROM ['#Ticker$']";

The inclusion of the literal '#Ticker' seems to prevent the hashtag from being converted to the temporary table symbol.

how to Read xls file using OLEDB?

Well, you don't have a sheet called Sheet1 do you? Your sheet seems to be called "email address from username" so your query should be....

Select * From ['email address from username$']

Also please don't use Microsoft.Jet.OLEDB.4.0 as it's pretty much obsolete now. Use Microsoft.ACE.OLEDB.12.0. If you specify Excel 12.0 in the extended properties it will open both .xls and .xlsx files.

You can also load the DataTable with a single line...

dt.Load(new System.Data.OleDb.OleDbCommand("Select * From ['email address from username$']", conn).ExecuteReader());

To read the names of the tables in the file use...

DataTable dtTablesList = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow drTable in dtTablesList.Rows)
{
    //Do Something
    //But be careful as this will also return Defined Names. i.e ranges created using the Defined Name functionality
    //Actual Sheet names end with $ or $'
    if (drTable["Table_Name"].ToString().EndsWith("$") || drTable["Table_Name"].ToString().EndsWith("$'"))
    {
        Console.WriteLine(drTable["Table_Name"]);
    }
}

Missing data trying to read an excel file In c# using OleDb

It reads the first few records and decides that the column is of type integer, which fails when it finds data which is not of integer type.

You need to set HDR=No as a property in your connection string and IMEX=1

---

Related Topics