从 Excel 中的活动工作表导入到 SQL Server 的最快方法是什么？
直接从 Excel 中的活动工作表将数据导入 SQL Server 数据库的最快方法是什么？
我已经取得了巨大的成功，只需循环遍历行和列、生成 SQL 字符串、打开 ADODB.Connection 并执行 SQL。然而，该解决方案对于大型数据集来说太慢了。
所以我正在使用 ADO 测试 Jet Provider。它肯定更快，但它要求保存工作簿，并且我不能要求用户在上传之前保存工作簿。
如果工作簿已保存，则以下代码有效，但如果 ActiveWorkbook 从未保存过，则 ActiveWorkbook.Path 返回空字符串，并且代码失败。
Sub UploadViaJet()
Dim objADO As New ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set objADO = New ADODB.Connection
objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & _
"\" & ActiveWorkbook.Name & ";" & _
"Extended Properties=Excel 8.0"
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<server>;Database=<database>;" & _
"UID=<UID>;PWD=<PWD>].test_table " & _
"FROM [" & ActiveSheet.Name & "$]"
objADO.Execute strSQL, lngRecsAff, adExecuteNoRecords
End Sub
有没有更简单、更快的方法来做到这一点？
What is the fastest way to import data into a SQL Server database directly from the active worksheet in Excel?
I have had great success simply looping through the rows and columns, generating a SQL string, opening an ADODB.Connection and executing the SQL. That solution, however, is too slow for large datasets.
So I am testing the Jet Provider with ADO. It's definitely faster, but it requires that the workbook be saved, and I cannot require the user to save the workbook before uploading from it.
The following code works if the workbook has been saved, but if ActiveWorkbook has never been saved ActiveWorkbook.Path returns a null string and the code fails.
Sub UploadViaJet()
Dim objADO As New ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set objADO = New ADODB.Connection
objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & _
"\" & ActiveWorkbook.Name & ";" & _
"Extended Properties=Excel 8.0"
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<server>;Database=<database>;" & _
"UID=<UID>;PWD=<PWD>].test_table " & _
"FROM [" & ActiveSheet.Name & "$]"
objADO.Execute strSQL, lngRecsAff, adExecuteNoRecords
End Sub
Is there a simpler and faster way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以创建一个数据源（使用链接服务器或
OPENROWSET），然后在SQL Server端执行SELECT：带有
的路径>Excel工作簿应可由SQL Server访问。以下是关于如何执行此操作的知识库文章。
You can create a datasource (using a linked server or
OPENROWSET) and just do aSELECTonSQL Serverside:The path with the
Excelworkbook should be accessible by theSQL Server.Here is a KB article on how to do this.
使用导入 Excel 文件的解决方案
由于您有不强制用户保存文件的限制，并且大多数建议的解决方案都要求保存文件，因此我将使用 VBA
SAVEAS工作簿到临时文件名 (TempExcelFile.xls) 和路径这样用户不会强制他们保存工作表，并且系统会提示他们保存它，就像在正常工作过程中一样。
为了便于同时导入多个活动工作表，我将使用时间戳或 GUID 来创建文件名。
使用 ADO 查询的解决方案
让您的用户从内置 VBA 宏的模板创建工作簿 确保保护工作簿以防止未经授权访问宏/隐藏代码。
执行一个宏，该宏将循环遍历行和列、读取单元格值、构建 SQL 查询并执行它 - 使用带参数的存储过程来防止注入攻击。
Solution using Import Excel File
Since you have a restriction on not forcing the user to save the file, and most proposed solutions require that the file be saved, I would use VBA to
SAVEASworkbook to a temp file name (TempExcelFile.xls) and pathThis way the user will not be forced to save the worksheet, and they will be prompted to save it just like they would during the natural course of their work.
To facilitate multiple active worksheets being simultaneously imported, I would use either a timestamp or a GUID to create the file names.
Solution using ADO Queries
Have your users create their workbooks from a template that has VBA macros built in Make sure you protect the workbook to prevent unauthorized access to the macros/code behind.
Execute a macro that will loop through the rows and columns, read the cell values, build a SQL query and execute it - use stored procedures with parameters to prevent injection attacks.
通常我使用 SSIS 导入大型 Excel 文件。
Generally I import large Excel files using SSIS.
您是否尝试过将 ActiveWorkBook.SaveAs("some temp filename") 放在顶部，然后从中导入？我使用宏执行类似的操作，将 ActiveSheet 的内容通过 FTP 传输到远程服务器，并且它可以工作。
Have you tried just putting an ActiveWorkBook.SaveAs("some temp filename") at the top, and importing from that? I do something similar with a macro to FTP the contents of the ActiveSheet to a remote server and it works.