1

I am generating Excel files using OpenXML, the file opens without any issues on my desktop with Excel 2016 installed. The file also passes validation using Open XML Productivity Tool 2.5.

The problem is that the file is rejected from the SQL Server Integration Services (SSIS) processing. The error is "External table is not in the expected format"

As far as I know SSIS is using OLEDB to process the files. I have tried to read the file locally using the following code and was able to reproduce the same error.

The code is as follows:

    var fileName = "C:\\Temp\\myExcel.xlsx";
    var connectionString = String.Empty;

    if (Path.GetExtension(fileName) == ".xlsx")
    {
        connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"", fileName);
    }
    else //.xls
    {
        connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", fileName);
    }

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        var adapter = new OleDbDataAdapter("select * from [test$]", conn);
        var excelDataSet = new DataSet();
        adapter.Fill(excelDataSet, "anyNameHere");

        var data = excelDataSet.Tables["anyNameHere"];
        foreach (DataRow row in data.Rows)
        {
            Console.Out.WriteLine(row[NAME].ToString());
        }
    }

All I was able to find in the net that this error is due to wrong connection string (Microsoft.Jet.OLEDB.4.0 and Excel 8.0 instead of Microsoft.ACE.OLEDB.12.0 and Excel 12.0) but I already have this.

This is definitely something wring with the file itself. The problem disappears if I open the file in Excel, and use save as.

I was trying to unpack the original and resaved files and compare the contents but the difference is huge, when saving excel adds lots of styles, themes, and xmls references. I really would not like to follow that path.

Do you know what parts are important for OleDB provider when reading Excel??

Sebastian Widz
  • 1,962
  • 4
  • 26
  • 45
  • Perhaps test the connection string described [here](https://stackoverflow.com/a/28933319/111794) – Zev Spitz Nov 11 '19 at 22:04
  • 1
    An ugly hack would be automating Excel to open and save the file. But there might be [third-party libraries](https://stackoverflow.com/a/29149737/111794) which could do that. Note that this problem has been reported [here](https://answers.microsoft.com/en-us/msoffice/forum/all/why-does-the-oleddb-interface-to-excel-fail-to/f225a24c-c9b0-4e16-ba55-81314e4a82c2) and [here](https://stackoverflow.com/questions/29428208/read-using-oledbconnection-created-xlsx-file-using-open-xml-sdk). – Zev Spitz Nov 11 '19 at 22:06
  • Hi, I do not believe this is related to connection string. The connection works for the file saved from Excel. I have started to fill missing parts compared to the document saved from Excel, but so far no luck. The validation passes without any errors for Excel 2007, 2010, 2013. – Sebastian Widz Nov 12 '19 at 09:22

0 Answers0