如何将数据从,一场精彩漂亮的比赛

今天早上,一篇来自MySQL 用户讨论会的高级销售专家Gerardo Narvaja的博客

此文章主要向大家讲述的是在Windows环境下的MySQL数据库,所谓是一场精彩的比赛的缘由,我们大家都知道MySQL数据库的使用率可以说越来越广泛了,那么MySQL数据库在Windows环境下的情境又如何呢?

本文循序渐进地演示如何用不同的方法将数据从 Microsoft Excel 工作表导入到
Microsoft SQL Server 数据库。

在Bruam的文章
Microsoft与MySQL的合作竞争比作一场“精彩漂亮的比赛”,
或者象巴西人喜欢说的那样叫做“jogo
bonito”。接下来我要试着来例证一下整个情景。我会基于他在MySQL用户讨论大会的发言

今天早上,一篇来自MySQL数据库用户讨论会的高级销售专家Gerardo
Narvaja的博客

 

现状:
你是否经常想了解是否整洁又庞大的电子数据表能够快速导入到数据库中,从而在Excel功能之外进行查询和数据操作?亦或,有多少次,我们常常最初以Excel为原型来为既有问题给予解决方案,而现在需要重新创建数据库来开始开发自己的应用程序?

在Bruam的文章中他用了一个比喻,把
Microsoft与MySQL(和PHP搭配之最佳组合)的合作竞争比作一场“精彩漂亮的比赛”,
或者象巴西人喜欢说的那样叫做“jogo
bonito”。接下来我要试着来例证一下整个情景。我会基于他在MySQL(和PHP搭配之最佳组合)用户讨论大会的发言

技术说明

本文中的示例使用以下工具导入 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

问题: 为了举例说明这个问题,我选择使用Nike+
iPod附件产生的数据。该数据以XML格式存储在iPod文件系统中,可以非常容易地导入到Excel。为了将问题简单点,我就只摘取了部分数据,也没有把字段转换足够多的数据类型请参考文章末尾的架构)。同时为了简化,我略去了导入数据的步骤,毕竟网上可以随处找到很多这样的例子。

现状:
你是否经常想了解是否整洁又庞大的电子数据表能够快速导入到数据库中,从而在Excel功能之外进行查询和数据操作?亦或,有多少次,我们常常最初以Excel为原型来为既有问题给予解决方案,而现在需要重新创建数据库来开始开发自己的应用程序?

要求

下面的列表列出了推荐使用的硬件、软件、网络架构以及所需的 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 开发

解决方案:
下面就是一系列在Windows环境使用Microsoft和MySQL快速将一个简单的Excel表格转换到实际数据库的步骤。本文假定连接MySQL的DSN已经配置好了,否则请查看Port25网站相关文章再继续后面的内容。

问题: 为了举例说明这个问题,我选择使用Nike+
iPod附件产生的数据。该数据以xml(标准化越来越近了)格式存储在iPod文件系统中,可以非常容易地导入到Excel。为了将问题简单点,我就只摘取了部分数据,也没有把字段转换足够多的数据类型请参考文章末尾的架构)。同时为了简化,我略去了导入数据的步骤,毕竟网上可以随处找到很多这样的例子。

示例

1)首先看到的是一张表格,它的首行显示了每一列对应字段的名称,随后连续的每一行就是每个字段对应的值了。

解决方案:
下面就是一系列在Windows环境使用Microsoft和MySQL数据库快速将一个简单的Excel表格转换到实际数据库的步骤。本文假定连接MySQL(和PHP搭配之最佳组合)的DSN已经配置好了,否则请查看Port25网站相关文章再继续后面的内容。

导入与追加

本文使用的示例 SQL 语句演示了“创建表”查询。该查询通过使用
SELECT…INTO…FROM 语法将 Excel 数据导入新的 SQL Server
表。如这些代码示例所示,在继续引用源对象和目标对象时,可以通过使用
INSERT INTO…SELECT…FROM 语法将这些语句转换成追加查询。

图1

1)首先看到的是一张表格,它的首行显示了每一列对应字段的名称,随后连续的每一行就是每个字段对应的值了。
(和PHP搭配之最佳组合): 一场精彩漂亮的比赛图一)” width=”450″
height=”189″ />

使用 DTS 或 SSIS

可以使用“SQL Server Data Transformation Services (DTS) 导入向导”或“SQL
Server 导入和导出向导”将 Excel 数据导入到 SQL Server
表中。在逐步执行向导并选择 Excel 源表时,要记住附加美元符号 ($) 的 Excel
对象名称代表工作表(例如,Sheet1$),而没有美元符号的普通对象名称代表
Excel 指定的范围。

2)确保MySQL服务器已经运行并打开了MySQL工具的系统托盘监视器。

2)确保MySQL(和PHP搭配之最佳组合)服务器已经运行并打开了MySQL(和PHP搭配之最佳组合)工具的系统托盘监视器。

使用链接服务器

要简化查询,可以将 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$]')

3)通过菜单DataàListàCreate
List将数据范围转换为列表,并保存电子表格。

3)通过菜单DataàListàCreate List将数据范围转换为列表,并保存电子表格。

使用分布式查询

如果不想将对 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$]')

4)启动Access,并通过菜单FileàOpen导入Excel电子表格,键入表单的名称。
完成这些之后,利用Link Spreadsheet Wizard
(链接表单向导)会出现创建数据库表的各种选择项。

4)启动Access,并通过菜单FileàOpen导入Excel电子表格,键入表单的名称。
完成这些之后,利用Link Spreadsheet Wizard
(链接表单向导)会出现创建数据库表的各种选择项。 (和PHP搭配之最佳组合):
一场精彩漂亮的比赛图二)” width=”450″ height=”361″ />

使用 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
图2

5)进入FileàExport把数据库导出到MySQL(和PHP搭配之最佳组合),然后在下拉菜单里面选择ODBC
Databases。

使用 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
数据库。

5)进入FileàExport把数据库导出到MySQL,然后在下拉菜单里面选择ODBC
Databases。

6)在Machine Data Source
(数据源)中选择正确的DSN连接MySQL(和PHP搭配之最佳组合)服务器。如果Access不能识别出正确的主键值,
它会报出错信息,通常你可以忽略它。 (和PHP搭配之最佳组合):
一场精彩漂亮的比赛图三)” width=”450″ height=”396″ />

6)在Machine Data Source
(数据源)中选择正确的DSN连接MySQL服务器。如果Access不能识别出正确的主键值,
它会报出错信息,通常你可以忽略它。

7)通过MySQL数据库工具箱里的浏览器你可以创建需要的主键值。
(和PHP搭配之最佳组合): 一场精彩漂亮的比赛” width=”450″ height=”203″
/>

图3

8。还是用这个浏览器,你需要能确保数据都清空了。这是由于列表在Excel中总是最后一行可能包含的都是空置,所以如果存在请先都删除它们。

7)通过MySQL工具箱里的浏览器你可以创建需要的主键值。

9。最后,打开一个已创建的数据库表,就可以使用Access和ODBC,开始选择数据子集,窗体,页面用来创建查询,编辑数据和报表了。

图4

见于初时数据的复杂性整个过程不会超过30分钟。下一步就是对数据库规格化了,扩展数据库架构等。使用Access可以快速开发基于概念的原型。同时MySQL(和PHP搭配之最佳组合)服务器也能通过。NET连接器进行被访问。

8。还是用这个浏览器,你需要能确保数据都清空了。这是由于列表在Excel中总是最后一行可能包含的都是空置,所以如果存在请先都删除它们。

结论:
大多时候,当我们面对如何制定IT方案时,一般会先用一组数据来描述问题。用上文讲述的Microsoft工具很容易对数据库数据进行描述,然后快速创建必要的工具一块解决问题。运用
MySQL(和PHP搭配之最佳组合)服务器和
MySQL数据库工具能够创建一个客户/服务器端后台用来实现把初始数据库原型转化为最终解决方案。所有这些操作时在Windows环境下完成的。

9。最后,打开一个已创建的数据库表,就可以使用Access和ODBC,开始选择数据子集,窗体,页面用来创建查询,编辑数据和报表了。
见于初时数据的复杂性整个过程不会超过30分钟。下一步就是对数据库规格化了,扩展数据库架构等。使用Access可以快速开发基于概念的原型。同时MySQL服务器也能通过。NET连接器进行被访问。

结论:
大多时候,当我们面对如何制定IT方案时,一般会先用一组数据来描述问题。用上文讲述的Microsoft工具很容易对数据库数据进行描述,然后快速创建必要的工具一块解决问题。运用
MySQL服务器和
MySQL工具能够创建一个客户/服务器端后台用来实现把初始数据库原型转化为最终解决方案。所有这些操作时在Windows环境下完成的。

原文地址:

用户讨论会的高级销售专家Gerardo Narvaja的博客
在Bruam的文章…