以编程方式将Ms Excel文件导入SQL Server

Question

我需要创建一个过程，以便在命令时从 MS Excel 电子表格上传数据到 SQL Server。我的背景是 Access VBA，我正在尝试使用以下任一分布式查询方法，如 Microsoft 支持网站所述: (https://support.microsoft.com/en-us/help/321686/how-to-import-data-from-excel-to-sql-server)。我的表名和文件名不同，否则我使用的是下面完全相同的代码。我收到错误消息："OLE DB 提供程序 'Microsoft.ACE.OLEDB.4.0' 未注册。" 我已经尝试了其他 OLEDB 版本，但没有成功。不确定如何检查需要哪个版本的驱动程序。我使用的 SQL Server 是 64 位，但我的本地机器是 32 位，我认为这可能会引起问题。如果有人能够帮助简化这个问题并指点我正确的方向，我将不胜感激。除了一些 SQL 背景外，我精通 VBA，其他方面的编程知识不多。谢谢！

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

- Chuck0185

---

这个过程还包括哪些元素？它们需要使用Access，或者使用VBA将数据从Excel移动到SQL Server吗？ - rd_nielsen

OLEDB 4.0不支持64位操作系统。您很可能需要使用12.0版本（在此下载32位或64位版本）。 - Jacob H

你在 SQL Server 上安装了 4.0 驱动程序吗？ - Jesse

尝试在SQL Server中使用Microsoft.ACE.OLEDB.12.0，而不是在Excel VBA中使用。 - Parfait

如果您可以自动将Excel表中的数据导出到文本文件，则“bcp”实用程序是从文本文件加载数据的简单方法。您可能还想了解T-SQL中的“BULK INSERT”命令以及使用链接服务器连接到Excel的方法。 - rd_nielsen

Answer 1

查询:

SELECT * INTO [TableName]
FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; Database=' + [SpreadsheetFullPath] + ';IMEX=1'' )...[' + [WorksheetName] + '$]'

注意：

1. 请记住，查询是在SQL服务器上执行的，因此[SpreadsheetFullPath]是服务器上的路径，而不是本地机器上的路径。
2. UNC路径（\\sharedDir\...）是可以接受的。
3. 保持要导入的Excel工作表简单。
4. 当出现问题时，这个驱动程序返回的错误信息非常不友好，所以请遵守第3条来避免这些错误。
5. 您可能需要在数据库服务器上安装驱动程序：Office 2010，Office 2016

- Alex

Answer 2

有一个名为SSIS（Sql Server Importing Services）的强大引擎可以完成这项任务，包括错误报告、数据转换、调度等功能。它可能已经存在于您的服务器上。这不是详细描述它的地方，但是在YouTube上有教程。多年来，设计环境要么是独立的，要么是Visual Studio的插件。

它肯定会解决您的导入问题，但是需要（陡峭的）学习曲线。（这可能会提高您的技能！）在使用SSIS之后，您可以继续使用SSRS和SSAS，但那是另一回事！

您当前的问题是“连接字符串”中的驱动程序名称。它通常取决于您服务器的操作系统版本。（即已安装或未安装的内容）。我建议您不要像我一样猜测（在XP上开发，然后盲目部署到Win7和Win10），而是请您的管理员在您的服务器上创建一个ODBC连接DSN文件，连接到您的Excel文件，然后查看该DSN文件的内容。它将包含您正在寻找的适合您服务器安装的连接字符串和驱动程序名称。我还发现，在连接到Sql Server时，“默认端口号”也可能突然需要在字符串中。

- P Oltergeist

---

嗨P，感谢回复。我会研究一下这个问题。我不是服务器管理员（我在服务器上有一个专用数据库），所以我遇到了许多权限问题，但我会与我们的SQL管理团队合作解决。 - Chuck0185