谷歌搜索时我看到了更多的答案,但无法解决我的问题。 这是我编写的将excel表插入sql server表的代码。
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();执行“excelConnection.Open();”行时。 我收到一个错误“外部表格不是预期的格式。”
而“sample.xlsx”实际上是一个ods格式文件。 我将其格式更改为xlsx。
这段代码会出错。 请建议我。
I have seen more answers while googling it, but couldn't solved my problem. This is the code I have written to insert excel sheet into 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 executing the line "excelConnection.Open();". I am getting an error "External table is not in the expected format."
And "sample.xlsx" is actually an ods format file. I changed its format as xlsx.
What would be wrong in this code. Please suggest me.
最满意答案
根据评论中的对话,您必须将ODS文件转换为XLS或XLSX格式,而不仅仅是重命名它。 您可以使用Excel或Online tools转换ODS文件。
As per the conversation in the comments, you have to convert the ODS file into XLS or XLSX format not just renaming it. You can use Excel or Online tools to convert ODS files.
更多推荐
发布评论