SqlBulkCopy - External table is not in the expected format

c# sqlbulkcopy visual-studio-2010

Question

I'm trying to use SqlBulkCopy as per the example in Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy.

I've created the table and Excel sheet OK and done the coding, but I keep getting

External table is not in the expected format.

at the connection.open() line.

How do I get rid of this problem?

    // Connection String to Excel Workbook
    String savePath = @"C:\TEMP\";

    String fileName = "upload.xls";
    savePath += fileName;
    //newpath += fileName;
    FileUpload1.SaveAs(savePath);

    string excelConnectionString =
        "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data    Source=" + savePath + ";" +
        "Extended Properties=Excel 8.0;";

    // Create Connection to Excel Workbook
    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
    {
        OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

        connection.Open();

        // Create DbDataReader to Data Worksheet
        using (DbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = "Data Source=GRACC011334\\SQLEXPRESS;Initial Catalog=ComputerBroadcastNetwork;Integrated Security=True";

            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "ExcelData";
                bulkCopy.WriteToServer(dr);
            }
        }
    }

Accepted Answer

I got it. I had to change the format of the connection string and changed it to

string excelConnectionString = (@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\TEMP\Book1.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"); 

Popular Answer

For me this error was caused by trying to bulk copy from a spreadsheet in the newer .xlsx file format. After I converted the spreadsheet to the older .xls format, then I was able to do teh copy without receiving the error.

I am using the same connection string that you specified above, with

Provider=Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

So apparently .xlsx files need different Provider and/or Extended Properties



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why