External table is not in the expected format c#

asp.net c# oledbconnection sqlbulkcopy sql-server-2008

Question

While searching for solutions, I came across more, but they didn't help me with my issue. I created the following code to insert an excel sheet into a SQL server table.

        string strConnection = ConfigurationManager.ConnectionStrings[0].ToString();
        string path = @"D:/Projects/sample.xlsx";
        string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";  

        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select [Name], [City], [Address], [Designation] from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "temp1";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();

while the command "excelConnection.Open();" is running. "External table is not in the intended format," says the error message.

Additionally, "sample.xlsx" is really a file in ods format. I converted it to xlsx format.

What may be wrong with this code. Help me out, please.

1
0
3/25/2015 7:00:14 AM

Accepted Answer

According to the discussion in the comments, you must convert theODS record intoXLS or XLSX not simply changing its name. You may utilizeExcel or Online tools to changeODS files.

0
3/25/2015 7:22:56 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow