技术说明
本文中的示例使用以下工具导入 Excel 数据: SQL Server 数据传输服务 (DTS) Microsoft SQL Server Integration Services (SSIS) SQL Server 链接服务器 SQL Server 分布式查询 ActiveX 数据对象 (ADO) 和 Microsoft OLE DB Provider for SQL Server ADO 和 Microsoft OLE DB Provider for Jet 4.0要求
下面的列表列出了推荐使用的硬件、软件、网络架构以及所需的 Service Pack: Microsoft SQL Server 7.0、Microsoft SQL Server 2000 或 Microsoft SQL Server 的可用实例 Microsoft Visual Basic 6.0(针对使用 Visual Basic 的 ADO 示例) 本文的部分内容假定您熟悉下列主题: 数据传输服务 链接服务器和分布式查询 Visual Basic 中的 ADO 开发示例
导入与追加
本文使用的示例 SQL 语句演示了“创建表”查询。该查询通过使用 SELECT...INTO...FROM 语法将 Excel 数据导入新的 SQL Server 表。如这些代码示例所示,在继续引用源对象和目标对象时,可以通过使用 INSERT INTO...SELECT...FROM 语法将这些语句转换成追加查询。使用 DTS 或 SSIS
可以使用“SQL Server Data Transformation Services (DTS) 导入向导”或“SQL Server 导入和导出向导”将 Excel 数据导入到 SQL Server 表中。在逐步执行向导并选择 Excel 源表时,要记住附加美元符号 ($) 的 Excel 对象名称代表工作表(例如,Sheet1$),而没有美元符号的普通对象名称代表 Excel 指定的范围。使用链接服务器
要简化查询,可以将 Excel 工作簿配置为 SQL Server 中的链接服务器。 有关其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章: 306397如何:结合 SQL Server 链接的服务器和分布式查询使用 Excel 下列代码将 Excel 链接服务器“EXCELLINK”上的 Customers 工作表数据导入新的名为 XLImport1 的 SQL Server 表:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
还可以通过按照以下方式使用 OPENQUERY 以全通过方式对源数据执行查询:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,'SELECT * FROM [Customers$]')
使用分布式查询
如果不想将对 Excel 工作簿的永久连接配置为链接服务器,可以通过使用 OPENDATASOURCE 或 OPENROWSET 函数为特定目的导入数据。下列代码示例也能将 Excel Customers 工作表数据导入新的 SQL Server 表:
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$])SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
使用 ADO 和 SQLOLEDB
当通过使用 Microsoft OLE DB for SQL Server (SQLOLEDB) 在 ADO 应用程序中连接到 SQL Server 时,可以使用与 “使用分布式查询”一节中相同的“分布式查询”语法将 Excel 数据导入 SQL Server。下列 Visual Basic 6.0 代码示例要求添加对 ActiveX 数据对象 (ADO) 的项目引用。此代码示例还演示了如何在 SQLOLEDB 连接上使用 OPENDATASOURCE 和 OPENROWSET。
Dim cn As ADODB.ConnectionDim strSQL As StringDim lngRecsAff As LongSet cn = New ADODB.Connectioncn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _"Initial Catalog=<database>;User ID=<user>;Password=<password>"'Import by using OPENDATASOURCE.strSQL = "SELECT * INTO XLImport6 FROM " & _"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _"'Data Source=C:\test\xltest.xls;" & _"Extended Properties=Excel 8.0')...[Customers$]"Debug.Print strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected: " & lngRecsAff'Import by using OPENROWSET and object name.strSQL = "SELECT * INTO XLImport7 FROM " & _"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _"'Excel 8.0;Database=C:\test\xltest.xls', " & _"[Customers$])"Debug.Print strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected: " & lngRecsAff'Import by using OPENROWSET and SELECT query.strSQL = "SELECT * INTO XLImport8 FROM " & _"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _"'Excel 8.0;Database=C:\test\xltest.xls', " & _"'SELECT * FROM [Customers$]')"Debug.Print strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected: " & lngRecsAffcn.CloseSet cn = Nothing
使用 ADO 和 Jet Provider
上一节中的示例使用 ADO 和 SQLOLEDB Provider 连接到从 Excel 到 SQL 导入的目标。也可以使用 OLE DB Provider for Jet 4.0 来连接到 Excel 源。Jet 数据引擎可以通过使用具有三种不同格式的特殊语法来在 SQL 语句中引用外部数据库: [Full path to Microsoft Access database].[Table Name] [ISAM Name;ISAM Connection String].[Table Name] [ODBC;ODBC Connection String].[Table Name] 本节使用第三种格式创建到目标 SQL Server 数据库的 ODBC 连接。可以使用 ODBC 数据源名称 (DSN) 或者 DSN-less 连接字符串:
DSN:[odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]DSN-less:[odbc;Driver={SQL Server};Server=<server>;Database=<database>;UID=<user>;PWD=<password>]
下列 Visual Basic 6.0 代码示例要求添加对 ADO 的项目引用。此代码示例演示了如何使用 Jet 4.0 Provider 通过 ADO 连接将 Excel 数据导入到 SQL Server。
Dim cn As ADODB.ConnectionDim strSQL As StringDim lngRecsAff As LongSet cn = New ADODB.Connectioncn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=C:\test\xltestt.xls;" & _"Extended Properties=Excel 8.0"'Import by using Jet Provider.strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _"Server=<server>;Database=<database>;" & _"UID=<user>;PWD=<password>].XLImport9 " & _"FROM [Customers$]"Debug.Print strSQLcn.Execute strSQL, lngRecsAff, adExecuteNoRecordsDebug.Print "Records affected: " & lngRecsAffcn.CloseSet cn = Nothing
也可以通过使用该语法(Jet Provider 支持)将 Excel 数据导入其他 Microsoft Access 数据库、索引顺序存取方法 (ISAM)(“desktop”)数据库或 ODBC 数据库。