Why Does My C# Code Get an 'External Table Not in Expected Format' Error When Reading Excel Files, and How Can I Fix It?

Solving the C#code to read the excel file, the "external form format does not match" error

When reading the Excel (XLSX) file with OLEDBDATADAPTER, you may encounter a "external form format" error. This error usually occurs when the XLSX file is located in a shared network position and does not open in advance in Microsoft Excel.

Question:

When reading the XLSX file with the following code, unless the file has been opened in Excel, an error will be encountered:

Solution:

<code class="language-c#">string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}</code>

In order to solve this error and read the excel file without the need to open the file in advance, you need to update the connection string. The error message shows that "the external format does not match" because the connection string uses an old format that is not compatible with the Excel 2007 file format.

The recommended solution is to replace the connection string to the following:

The updated connection string uses microsoft.ace.OLEDB.12.0 to provide a program and sets Extended Properties to Excel 12.0. This combination is compatible with the file formats of Excel 2007 and higher versions, which should be able to solve the "external format format" error.

The above is the detailed content of Why Does My C# Code Get an 'External Table Not in Expected Format' Error When Reading Excel Files, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!