1.1 熟悉Excel的SQL语言编写环境

本节主要通过相关案例来介绍Excel 2010编写SQL语言的操作界面及相关技巧，逐步帮助读者熟悉编写SQL语句的工作环境，掌握最基本的入门技能。

注意

为了让读者更好地实现与书中示例对照学习的效果，本书示例文件均需放在D盘根目录下的“千寻千解SQL”文件夹下。

疑难1 如何获取外部源数据

用户若要使用SQL语句，首先需要获取外部源数据。如图1-1所示为某公司的客户订单表，现需导入客户订单的相关记录，该数据表保存在D盘目录下的“千寻千解SQL>第一章>疑难1>客户订单表.xlsx”。如何获取外部源数据？

■ 图1-1 客户订单

解决方案一

利用导入外部数据的现有连接功能。

操作方法

※ 通过OLE DB 接口获取外部数据源 ※

步骤1 在任意目录下新建一个工作簿，将其命名为“疑难1”，然后打开该工作簿，删除Sheet2工作表和Sheet3工作表，并将Sheet1工作表改名为“结果表”。

步骤2 选择“数据”选项卡，单击“获取外部数据”组中的“现有连接”按钮，在弹出的“现有连接”对话框中单击“浏览更多”按钮，弹出“选取数据源”对话框，如图1-2所示。

■ 图1-2 选取数据源

步骤3 在“选取数据源”对话框的“导航窗格”中，在D盘中选择需要导入的目标文件所在路径，双击“客户订单表.xlsx”，单击“打开”按钮，打开“选择表格”对话框，如图1-3所示。

■ 图1-3 选择表格

步骤4 保留“选择表格”对话框中的默认设置，单击“确定”按钮，激活“导入数据”对话框，指定返回的查询数据位置为现有工作表的“Sheet1!$A$1”，如图1-4所示，然后单击“确定”按钮，即可将外部源数据导入当前工作表中。

■ 图1-4 导入数据

原理分析

OLE DB是微软战略性的通向不同数据源的低级应用程序接口，其中不仅包括微软资助的标准数据接口开放数据库连通性（ODBC）的SQL语言能力，还具有面向其他非SQL数据类型的通路。作为微软组件对象模型的一种设计，OLE DB是一组读/写数据的方法。

OLE DB的存在为用户提供了一种统一的方法来访问不同种类的数据源。OLE DB可以在不同的数据源中进行转换，本例就是从Excel数据库中提取相关记录。通过OLE DB接口，可以在Excel中自如地使用SQL语句。

解决方案二

使用Microsoft Query导入外部源数据。

操作方法

※ 使用Microsoft Query导入外部源数据 ※

步骤1 选择“数据”选项卡，单击“获取外部数据”组中的“自其他来源”按钮，在下拉菜单中选择“来自Microsoft Query ”选项，弹出“选择数据源”对话框，取消“使用‘查询向导’创建/编辑查询”复选框的选择，在“数据库”列表框中选择“Excel Files *”选项，如图1-5所示，单击“确定”按钮。

■ 图1-5 选择数据源

步骤2 弹出“选择工作簿”对话框，在“驱动器”下拉列表中选择D盘，在“数据库名”列表框中选择“客户订单表.xlsx”，如图1-6所示，单击“确定”按钮。

■ 图1-6 选择工作簿

步骤3 在弹出的“添加表”对话框中，选中“客户订单$”表，如图1-7所示，单击“添加”→“关闭”按钮，关闭“添加表”对话框。

■ 图1-7 添加表

注意

在使用过程中，可能会遇到如图1-8所示的情况而无法添加表。此时需要按照如图1-9所示的步骤1→2选中“表选项”对话框中的所有复选框即可。

■ 图1-8 无法添加表

■ 图1-9 表选项

步骤4 在出现的“Microsoft Query”视图中，双击“客户订单$”表中的星号，显示客户订单中的所有记录，如图1-10所示。

■ 图1-10 所有的记录表

步骤5 选择“文件”→“将数据返回Microsoft Excel”命令，弹出“导入数据”对话框，指定“数据的放置位置”为“现有工作表”的A1单元格，单击“确定”按钮，关闭对话框，即可返回外部源数据，导入结果如图1-11所示。

■ 图1-11 导入结果

原理分析

Microsoft Query是用于将外部数据源检索到Microsoft Office程序（特别是Microsoft Excel ）中的一种程序。通过使用Query可以从企业的数据库和文件中检索数据，而不必重新输入需要在Excel中分析的数据，也可以在每次更新数据库时，自动通过源数据库中的数据来更新Excel报表和汇总数据。

一般在导入外部源数据时，常常用到Microsoft Query结合SQL语句来进行数据查询或者数据重构。

知识扩展

在本例中，分别使用了OLE DB及Microsoft Query两种方法导入外部源数据，这两种方法也是使用SQL语句的必经之道。下面分别介绍在OLE DB以及在Microsoft Query中如何使用SQL语句。

在OLE DB中使用SQL语句的操作过程如下：

步骤1 重复解决方案一中的步骤1至步骤3。

步骤2 保留“选择表格”对话框中对名称的默认选择，单击“确定”按钮，激活“导入数据”对话框，单击“属性”按钮，打开“连接属性”对话框，在“定义”选项卡下清除“命令文本”框中的原有内容，如图1-12所示，输入SQL语句：

■ 图1-12 输入SQL语句窗口

SELECT  *
FROM  [客户订单$]

单击“确定”按钮，关闭“连接属性”对话框并返回“导入数据”对话框，指定返回的查询数据位置为现有工作表的“$A$1”，然后单击“确定”按钮，即可结合SQL语句将外部源数据导入到当前工作表。

那么在Microsoft Query中使用SQL语句又该如何操作？具体步骤如下：

步骤1 重复解决方案二中的步骤1至步骤4。

步骤2 在“Microsoft Query”视图中，单击按钮，即可弹出“SQL”文本框，如图1-13所示。在本例中自动生成的SQL语句如下：

■ 图1-13 “SQL”文本框

SELECT '客户订单$'.客户, '客户订单$'.'ERP CO号', '客户订单$'.工单号, '客户订单$'.产品码, '客户订单$'.款号, '客户订单$'.订单数量, '客户订单$'.订单交期
FROM 'D:\疑难1客户订单表.xlsx'.'客户订单$' '客户订单$'

注意

利用“Microsoft Query”视图操作界面，可以自动生成最原始的SQL语句，然后对其进行修改简化，即可写出专业的SQL语句。

简化后的SQL语句如下：

SELECT *
FROM  [客户订单$]

步骤3 将简化后的SQL语句输入“SQL”文本框中，并单击“确定”按钮，在弹出的“Microsoft Query”对话框中单击“确定”按钮，如图1-14所示。

■ 图1-14 “Microsoft Query”对话框

步骤4返回客户订单的所有记录，如图1-15所示，在“Microsoft Query”视图中，选择“文件”→“将数据返回Microsoft Excel”命令，弹出“导入数据”对话框，指定“数据的放置位置”为“现有工作表”的A1单元格，单击“确定”按钮，关闭对话框，即可得到客户订单中的所有记录。

■ 图1-15 客户订单的所有记录

通过本例，可以掌握在OLE DB及Microsoft Query中使用SQL语句，并且熟悉导入外部源数据，为学习SQL打下了初步的基础。

▲ 本书约定：因Microsoft Query需要安装完整版的Excel组件才可使用，在本书以下疑难操作演示中，将以OLE DB操作SQL语句为基准，并且省略了在OLE DB中使用SQL语句的操作过程的步骤1（即解决方案一中的步骤1至步骤3）。

疑难2 如何刷新导入的外部数据

某用户在Excel中导入外部源数据，但源数据却每天都在添加新的记录，现在想在外部源数据添加新记录后，导入在Excel中的数据也能同时实现自动更新。如图1-16所示，应该如何实现对导入Excel中的数据进行及时更新？

■ 图1-16 导入外部数据的结果表

解决方案

设置导入外部数据的自动刷新功能。

操作方法

※ 刷新导入的外部数据源 ※

步骤1 在导入的数据区域中单击区域内的某个单元格（本例单击单元格A1），选择“数据”选项卡，单击“连接”组中的“全部刷新”按钮，在下拉菜单中选择“连接属性”选项，如图1-17所示，弹出“连接属性”对话框。

■ 图1-17 寻找“连接属性”

注意

在使用过程中，若想手动刷新，可在“全部刷新”按钮下选择“全部刷新”或“刷新”按钮，但该步骤不能进行自动更新，灵活性较差。

步骤2 在弹出的“连接属性”对话框中，勾选“使用状况”选项卡中的“刷新频率”复选框（系统默认刷新频率为60分钟）及“打开文件时刷新数据”复选框，然后单击“确定”按钮，如图1-18所示，再次打开该工作簿或者等待60分钟后，系统自动更新Excel中的数据。

■ 图1-18 连接属性对话框

原理分析

通过刷新操作可获得最新的数据，因为连接定义了访问和检索外部数据源中的数据所需的所有信息，它可以访问以下多种数据源：OLAP、SQL Server、Access、OLEDB、ODBC、其他Excel文件和文本文件，并将最新数据添加到当前工作簿中。

注意

当用户连接到外部数据源并尝试刷新数据时，要注意可能出现的安全问题，还要知道在出现安全问题时该如何处理，这一点很重要。

知识扩展

数据连接文件通常包含一个或多个用于刷新外部数据的查询。通过替换此文件，恶意用户可以设计查询来访问机密信息并将这些信息分发给他人，或执行其他有害操作。因此，请务必确保：

1.连接文件必须是由可靠人员创建的。

2.连接文件是安全的，来自可信任的位置（文档库、网络文件夹或Web文件夹）。

疑难3 在数据源移动后如何修改数据源的连接路径

在Excel中导入外部数据时，必须事先指定数据源表的位置，一旦数据源表的位置发生了变化，就必须相应地修改连接路径，否则无法刷新导入的数据。如图1-19所示，展示了一张在D盘根目录下“手工修改导入数据路径”文件夹内的两个工作簿，其中“导入成绩表.xlsx”是以“成绩表.xlsx”为数据源通过导入外部数据功能生成的数据表。

■ 图1-19 数据源未移动的导入Excel表中的成绩表

如果将该文件夹移动到E盘根目录下，再次打开“导入成绩表.xlsx”工作簿，刷新数据后则会出现以下错误提示，如图1-20所示，那么应该如何解决移动后的数据源路径更新问题？

■ 图1-20 数据源移动后再刷新工作表出现的错误提示

解决方案一

根据弹出提示自动修改路径。

操作方法

※ 根据弹出提示自动修改移动的文件路径 ※

在弹出的“Microsoft Excel”对话框中，单击“确定”按钮，弹出“数据文件：未找到‘D:\成绩表.xlsx’，希望连接到E:\手工修改导入数据路径\成绩表.xlsx来代替吗？”的“Microsoft Excel ”对话框，单击“是”按钮，如图1-21所示，即可自动修改外部源数据路径。

■ 图1-21 自动提示更改

解决方案二

修改连接字符串中的相应路径。

操作方法

※ 通过修改连接字符串来修改移动的文件路径 ※

步骤1 在导入的数据区域中单击区域内某个单元格（本例单击单元格A1），选择“数据”选项卡，单击“连接”组中的“全部刷新”按钮，在下拉菜单中选择“连接属性”选项，弹出“连接属性”对话框。

步骤2 在弹出的“连接属性”对话框中，单击“定义”选项，将“连接字符串”对话框中的“Data Source=D:\手工修改导入数据路径\成绩表.xlsx”变更为“Data Source=E:\手工修改导入数据路径\成绩表.xlsx”，如图1-22所示，最后单击“确定”按钮。

■ 图1-22 变更“连接字符串”

疑难4 如何将混合类型的字段强制设置为文本字段

如图1-23所示为某公司的订单表，在录入时，代码是数值及文本等混合类型存在的，其为订单表的唯一标识。利用该订单表作为源数据表导入到新的工作表，发现订单表中代码字段中的文本类型记录出现了缺失，如图1-24所示。那么如何把代码中所有的记录都显示出来呢？

■ 图1-23 订单表

■ 图1-24 导入到新工作簿的订单结果表

解决方案

把混合类型的字段强制设置为文本类型字段。

操作方法

步骤1 在导入的数据区域中单击区域内某个单元格（本例单击单元格A1），选择“数据”选项卡，单击“连接”组中的“全部刷新”按钮，在下拉菜单中选择“连接属性”选项，弹出“连接属性”对话框。

步骤2 在弹出的“连接属性”对话框中，单击“定义”选项，在“连接字符串”对话框中得到以下语句：

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=D:\疑难4.xlsx;
Mode=Share Deny Write;
Extended Properties="HDR=YES;";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Engine Type=37;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=False

将语句中的

Extended Properties="HDR=YES; ";

更改为

Extended Properties="HDR=YES;IMEX=1";

然后单击“连接”组中的“全部刷新”按钮即可。

原理分析

※ 强制设置导入的外部数据源的字段为文本形式 ※

通过强制设置字段为文本形式，把数据作为文本看待。下面通过介绍ACE.OLEDB.12.0连接方式，进一步了解其原理，以下是语法格式：

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=D:\疑难4.xlsx;
Mode=Share Deny Write;
Extended Properties="HDR=YES;IMEX=1";

其中，"HDR=YES;"表示第一列是列名而不是数据，而"HDR=No;"正好与前面的相反。IMEX则是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。

如果同一列中既有数字值，也有文本值，则会出现严重的问题。通过外部导入数据连接，将返回占多数类型的数据，但对于占少数的数据类型，则会返回NULL（空）值。如果该列中两种类型数据的数量相等，则程序将优先选择数字型数据，放弃文本型数据。

当设置IMEX=1时，将强制混合数据转换为文本，但仅仅采用这种设置并不可靠，IMEX=1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为做了略微的改变。例如，某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行中的含有文本的数据仍然变空。

针对上面情况的一个改进措施是IMEX=1与注册表值TypeGuessRows配合使用， TypeGuessRows值决定了ISAM驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的注册表值来更改采样行数。但是这种改进需要根据Excel版本的不同来修改，Excel 2010的行数是104 8576行，则将TypeGuessRows设置为104 8576行，同时把IMEX设为“1”，则可从根本上解决该问题。

产生这种问题的根源与Excel ISAM[3]（Indexed Sequential Access Method，即索引顺序存取方法）驱动程序的限制有关，Excel ISAM驱动程序通过检查前几行中实际的值确定一个Excel列的类型，然后选择能够代表其样本中大部分值的数据类型。也即Excel ISAM查找某列前几行（默认情况下是8行），把占多数的类型作为其处理类型。例如，如果数字占多数，那么其他含有字母等文本的数据项就会置空；相反，如果文本居多，纯数字的数据项就会被置空。

知识扩展

※ SQL中的连接字符串详解 ※

在“连接字符串”文本框中出现的那些语句如何解释呢？下面将一一说明OLE DB Provider for Microsoft Jet。

OLE DB Provider for Microsoft Jet允许ADO访问Microsoft Jet数据库，在Excel连接里面典型的连接字符串是

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=用户名;
Data Source=数据库完整的路径及文件名;
Mode=模式;
Extended Properties="HDR=YES;";

它包含的关键字如表1-1所示。

■ 表1-1 关键字一览表

OLE DB Provider for Microsoft Jet还支持特有的动态属性，具体的动态属性参数及说明如表1-2所示。

■ 表1-2 动态属性表说明

疑难5 如何对外部数据进行安全设置

当打开包含外部数据的连接工作簿并对其中的数据进行刷新时，弹出一个“安全声明”对话框的提示，如图1-25所示，如果在该对话框中单击“确定”按钮，将启用所有的外部数据连接。如果单击“取消”按钮，所有的外部数据连接将继续禁用且不会刷新。那么，如何在数据刷新时禁止该对话框出现呢？

■ 图1-25 安全声明

解决方案

在信任中心页面进行相应的设置即可解决该问题。

操作方法

※ 安全设置外部数据 ※

步骤1 单击“文件”→“选项”，打开“Excel选项”对话框。

步骤2 选择对话框左侧的“信任中心”选项，在对应的右侧单击“信任中心设置”按钮，如图1-26所示，打开“信任中心”对话框。

■ 图1-26 “Excel选项”对话框

步骤3 选择对话框左侧的“外部内容”选项，在对应的右侧选中“启用所有数据连接（不建议使用）”单选按钮，单击“确定”按钮，关闭“信任中心”对话框，如图1-27所示，返回“Excel选项”对话框，单击“确定”按钮关闭该对话框，此时外部连接自动启用。

■ 图1-27 “信任中心”对话框

注意

如果局域网内的网络管理权设置了安全策略，则无法进行上述操作改变信任中心设置。