Reading from Excel (.xlsx)

Question

I followed a few examples on the internet. Installed AccesDataBaseEngine.

But still I get this error message:

System.Data.OleDb.OleDbException: 'External table is not in the expected format.'

I tried some things that people suggestes, but nothing works.

This is the code:

string path = @"C:\...\...\Desktop\MyFile.xlsx";
           string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

            var dataAdapter = new OleDbDataAdapter("SELECT * FROM [HHH$]", connectionString);
            var dataTable = new DataTable();
            dataAdapter.Fill(dataTable);

What did I forgot?

Better use a specialised library - you'll save yourself a lot of grief. Have a look on this: https://github.com/zorgoz/EPPlus.TableAsEnumerable or this: https://github.com/ipvalverde/EPPlus.DataExtractor — trailmax, Jan 05 '18 at 16:20
However this seems to have an answer to your question: https://stackoverflow.com/a/16051/809357 — trailmax, Jan 05 '18 at 16:21

Robert Groves · Answer 1 · 2018-01-05T16:47:50.000

Without knowing the version of the Excel file your working with or it's data contents it's hard to say for sure. Have you tried including HDR and IMEX options in your connection string?

HDR=Yes; - Indicates the first row is a header row containing column names and not data

HDR=No; - Indicates there is no header row

IMEX=1; - Indicates to always read mixed data columns (columns containing numbers, dates, strings, etc.) as text. Note that this option may cause problems if you're writing back to the Excel sheet since it's essentially ignoring types on read. An alternative would be to explicitly define the columns of your DataTable.

Reading from Excel (.xlsx)

1 Answers1