SqlBulkCopy - 外部表不是预期的格式
我正在尝试按照 使用 SqlBulkCopy 将 Excel 电子表格数据导入 SQL Server 数据库表。
我已经创建了表格和 Excel 工作表并完成了编码，但我不断收到
外部表的格式不符合预期。
在 connection.open() 行。
我该如何摆脱这个问题？
// 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);
}
}
}
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);
}
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我得到了它。我不得不更改连接字符串的格式并将其更改为
I got it. I had to change the format of the connection string and changed it to
对我来说，此错误是由于尝试从较新的 .xlsx 文件格式的电子表格中批量复制而引起的。将电子表格转换为旧的 .xls 格式后，我就可以进行复制而不会收到错误。
我使用与上面指定的相同连接字符串，
Provider=Microsoft.Jet.OLEDB.4.0 和 Extended Properties=Excel 8.0
所以显然 .xlsx 文件需要不同的 Provider 和/或扩展属性
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