vb.net 将数据从 Excel 导入 SQL Server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19838394/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import data from Excel to SQL Server 2008
提问by Swicky
I'm doing a redesign on a site that uses VB.Net. The system will get a fair bit of data from Excel 97-2003 files, which are uploaded by a third party, then uploaded to SQL 2008. Here's the problem, the files that are uploaded have the extension .P. I've used the below code to try and grab the data and upload to the database.
我正在一个使用 VB.Net 的网站上进行重新设计。系统会从 Excel 97-2003 文件中获取相当多的数据，这些数据由第三方上传，然后上传到 SQL 2008。问题是，上传的文件的扩展名为 .P。我使用下面的代码来尝试获取数据并上传到数据库。
Dim xlApp As New Excel.Application
xlApp.Workbooks.Open(Filename:=Server.MapPath("~/ExtractFiles/10-31-13.P"))
xlApp.ActiveWorkbook.SaveAs(Filename:=Server.MapPath("~/ExtractFiles/10-31-13.xls"), FileFormat:=51)
If Not xlApp Is Nothing Then
xlApp.ActiveWorkbook.Close()
xlApp.Quit()
xlApp = Nothing
End If
PrmPathExcelFile = Server.MapPath("~/ExtractFiles/10-31-13.P")
plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""")
cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [10-31-13]", plmExcelCon)
Dim dt As System.Data.DataTable
dt = New System.Data.DataTable
cmdLoadExcel.Fill(dt)
plmExcelCon.Close()
When the code hits the line cmdLoadExcel.Fill(dt), I'm only getting an error that says "External table is not in the expected format.". I'm assuming that this has to do with the fact that I'm trying to change the file extension. However, I can't seem to open the file with the extension .P.
当代码到达 cmdLoadExcel.Fill(dt) 行时，我只会收到一条错误消息，指出“外部表不是预期的格式。”。我假设这与我试图更改文件扩展名的事实有关。但是，我似乎无法打开扩展名为 .P 的文件。
Is there a method I'm overlooking here? Or is this just not possible when working from a file with a custom extension.
有没有我在这里忽略的方法？或者在使用具有自定义扩展名的文件工作时这是不可能的。
采纳答案by Swicky
Alright, I found the answer thanks to the hint by varocarbas. For future reference, double check the OledbConnection against the Excel file type. After I put in the following changes it worked:
好的，多亏了瓦罗卡巴斯的提示，我找到了答案。为了将来参考，请根据 Excel 文件类型仔细检查 OledbConnection。在我进行以下更改后，它起作用了：
plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""")
cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [10-31-13$]", plmExcelCon)
First, to work with my version of Excel, I needed to use ACE.OLEDB.12.0 and Excel 8.0. Second, I had to add a simple '$' to the end of the table select, otherwise it wouldn't be able to find the right worksheet.
首先，要使用我的 Excel 版本，我需要使用 ACE.OLEDB.12.0 和 Excel 8.0。其次，我必须在表选择的末尾添加一个简单的“$”，否则将无法找到正确的工作表。
