'External table is not in the expected format.' Got this error when importing a .xls Excel file

Question

Hi,

In my C# winform application, I use the following code to import the Excel file and get a list of worksheets names.

public static List<string> ListSheetInExcel(string filePath)
        {
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder
            {
                DataSource = filePath,
                Provider = "Microsoft.ACE.OLEDB.12.0"
            };
            string strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
            sbConnection.Add("Extended Properties", strExtendedProperties);
            List<string> listSheet = new List<string>();
            using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
            {
                conn.Open(); // This line throw out the exception.
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
              // The code to create the list of worksheets name
            }
            return listSheet;
        }

This code works well with .xlsx files and most of .xls files. Until yesterday it threw out an error when importing a .xls file. I am not sure how that .xls file was created. I saved it as Excel 97-2003 Excel file in my laptop. After that the code works as usual. However, I cannot tell the difference between the original file from customer and my copy. I wish I can upload the .xls files here so that you might be able to finger it out.

I did read through the other answers about this issue and tested as below:
I opened two files in Notepad. None of them are displayed as a HTML file.
I also changed the ExtendedProperties to "Excel 8.0;HDR=Yes;IMEX=1". Got the same error.
I checked the file Properties. Both indicate Microsoft Excel 97-2003 Worksheet in Type attribute.

Could anyone help on this? Let me know if more info needed. Thanks.

Answer 1

At this point, pretty much everything has been tried for the older Excel file might be wise to ask for them to save the file as a comma delimited file which you can open in Excel, save and read as there is no table format. I would suggest a repair as shown below.