Excel "External table is not in the expected format."

Question

I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External table is not in the expected format." error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first? Thanks

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);
}

Answer 1

Instead of OleDb, you could use the Excel Interop and open the worksheet as read-only.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open(office.11).aspx

Answer 2

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

Answer 3

Public Class Form1
    Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=P:\Pdrive\Karnav\Test1.xlsx;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;"""

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim pram As OleDb.OleDbParameter
        Dim olecon As OleDb.OleDbConnection
        Dim olecomm As OleDb.OleDbCommand
        Dim oleadpt As OleDb.OleDbDataAdapter
        Dim ds As DataSet
        Dim a As String
        Dim b As String
        Dim c As Date
        Dim d As Date
        Dim f As String
        Dim g As String
        Dim h As String
        Dim i As String

        Try
            a = purchase.Text
            b = ListBox3.Text
            c = DateTimePicker1.Text
            d = DateTimePicker2.Text
            f = TextBox3.Text
            g = ListBox1.Text
            h = ListBox2.Text
            i = TextBox4.Text

            olecon = New OleDb.OleDbConnection
            olecon.ConnectionString = connstring
            olecomm = New OleDb.OleDbCommand
            olecomm.CommandText = "Insert into [Sheet1$]" & _
            "(purchaseitemcode, crosssellitemcode, startdate, enddate, discount, displayonprod, displayoncart, show) values ('" + purchase.Text + "', '" + ListBox3.Text + "' ,'" + DateTimePicker1.Text + "' , '" + DateTimePicker2.Text + "', '" + TextBox3.Text + "', '" + ListBox1.Text + "', '" + ListBox2.Text + "', '" + TextBox4.Text + "')"

            olecomm.Connection = olecon
            pram = olecomm.Parameters.Add("'" + purchase.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "purchaseitemcode"
            pram = olecomm.Parameters.Add("'" + ListBox3.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "crosssellitemcode"
            pram = olecomm.Parameters.Add("'" + DateTimePicker1.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "startdate"
            pram = olecomm.Parameters.Add(" '" + DateTimePicker2.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "enddate"
            pram = olecomm.Parameters.Add("'" + TextBox3.Text + "'", OleDb.OleDbType.Integer)
            pram.SourceColumn = "discount"
            pram = olecomm.Parameters.Add("'" + ListBox1.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "displayonprod"
            pram = olecomm.Parameters.Add("'" + ListBox2.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "displayoncart"
            pram = olecomm.Parameters.Add("'" + TextBox4.Text + "'", OleDb.OleDbType.VarChar)
            pram.SourceColumn = "show"
            oleadpt = New OleDb.OleDbDataAdapter(olecomm)
            ds = New DataSet
            olecon.Open()
            oleadpt.Fill(ds, "Sheet1")
            olecon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            olecon = Nothing
            olecomm = Nothing
            oleadpt = Nothing
            ds = Nothing
            pram = Nothing
        End Try
    End Sub
End Class

Here is my code I am getting same error. How can I resolve it?

Answer 4

Thanks for this code :) I really appreciate it. Works for me.

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

So if you have diff version of Excel file, get the file name, if its extension is .xlsx, use this:

Private Const connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

and if it is .xls, use:

Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""