哪将数据由 Excel 导入到 SQL Server

正文循序渐进地示范如何用不同之法将数据由 Microsoft Excel 工作表导入到
Microsoft SQL Server 数据库。

 

术验证

正文中的言传身教使用以下工具导入 Excel 数据:

  • SQL Server 数据传输服务 (DTS)
  • Microsoft SQL Server 2005 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 2005 的可用实例
  • 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  (http://support.microsoft.com/kb/306397/EN-US/ )
如何:结合 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.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.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 strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.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 strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.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 strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set 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.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.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 strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

呢可以经过行使该语法(Jet Provider 支持)将 Excel 数据导入外 Microsoft
Access 数据库、索引顺序存取方法 (ISAM)(“desktop”)数据库或 ODBC
数据库。

相关文章