Reading excel file using OLEDB Data Provider

by

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.

More Related Contents:

  1. Scientific notation when importing from Excel in .Net
  2. Reading Excel files from C#
  3. How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
  4. Using Excel OleDb to get sheet names IN SHEET ORDER
  5. Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine
  6. OLEDB Parameterized Query
  7. OleDbParameters and Parameter Names
  8. How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries
  9. How to read file using NPOI
  10. How to open an Excel file in C#?
  11. EPPlus number format
  12. How to read data from excel file using c# [duplicate]
  13. BUG: Can’t choose dates on a DatePicker that fall outside a floating VSTO Add-In
  14. How to make correct date format when writing data to Excel
  15. Importing Excel into a DataTable Quickly
  16. How to save workbook without showing save dialog with Excel interop?
  17. Optimal way to Read an Excel file (.xls/.xlsx)
  18. Why appending AutoFilter corrupts my excel file in this example?
  19. Is there a “proper” way to read CSV files [duplicate]
  20. How to import all the Excel sheets to DataSet in C#
  21. EPPlus – LoadFromCollection – Text converted to number
  22. As of today, what is the right way to work with COM objects? [duplicate]
  23. When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?
  24. Import Excel to Datagridview
  25. .Net Excel Interop Deleting a worksheet
  26. Get all checked nodes of a treeview
  27. Is DateTime.Now the best way to measure a function’s performance? [closed]
  28. Difference between lock(locker) and lock(variable_which_I_am_using)
  29. Sql Bulk Copy/Insert in C#
  30. Target 32 Bit or 64 Bit native DLL depending on environment

Leave a Comment