全面掌握MS ACCESS SQL（50）

时　间:2018-01-30 16:59:32
作　者:Big Young   ID：252  城市：襄阳
摘　要:    用SQL操作EXCEL工作表中数据。
正　文:

第三节 用SQL操作EXCEL工作表中数据

在现实的应用中，我们经常会在ACCESS与EXCEL之间交换数据，这就涉及到二者之间的互操作问题。如果我们有一个大型的Excel清单，并想充分利用Access的数据管理能力、安全性或多用户处理能力，那么可以将 Excel 数据转换到Access数据库。有时我们需要利用EXCEL的数据分析工具对数据进行灵活的分析研究，我们又需要将ACCESS数据库中的数据导出为EXCEL格式，在EXCEL下进行操作。这些在ACCESS与EXCEL间相互导入导出数据的事情经常性的发生，为了在工作中能更好地更高效地完成这类任务，下面我们就来对在ACCESS数据中通过SQL对EXCEL文档中的数据进行访问这个问题进行研究。

一、ACCESS SQL操作EXCEL的基本语法

在ACCESS中对EXCEL只能进行各种SQL操作，如同操作本地表一样，这时我们就把EXCEL工作薄看成是一个数据库，它其中的工作表就相当于数据库表，我们可以查询、修改、追加数据，并能删除数据表等操作。这里为了简化描述，我们只以选择查询语句为例来给出操作EXCEL的SQL语法：

Select * | field1,field2,…fieldn

FROM sheet$

IN 'path\excelfile' 'EXCEL 12.0 XML;HDR=YES;IMEX=1;'

或者：

Select * | field1,field2,…fieldn

INTO sheet$ IN 'path\excelfile' 'EXCEL 12.0 XML;HDR=YES;IMEX=1;'

FROM table

语法中各部分的说明：

关于EXCEL类型串的几点说明：

由于OFFICE系统不断的升级，它有多个版本，因而ACCESS和EXCEL也有多个版本。如果OFFICE是1997-2003版，类型串中应是：“EXCEL 8.0;HDR=YES;IMEX=1;”；如果OFFICE的版本是在2007-2016中，类型串应有几种不同的情况：一个是“EXCEL 12.0 XML;HDR=YES;IMEX=1;”，这是针对XML格式的EXCEL文档（*.xlsx），第二种是“EXCEL 12.0 MACRO;HDR=YES;IMEX=1;”，这是操作带宏的EXCEL文档（*.xlsm）的，再一种就是“EXCEL 12.0;HDR=YES;IMEX=1;”，这是对二进制格式的excel文档（*. xlsb）进行操作的类型串。

另外类型字串中的“HDR”表示“表头”的意思，就是表的各列标题，或者叫字段名。串中的“IMEX”表示“输入输入出”模式，可能的取值为：

0，表示输出模式

1，表示输入模式

2，表示是被链接的模式

我们使用中通常在查询浏览记录时设置为1，这样在导入EXCEL数据时会把数据库不能识别的数据格式以文本格式导入，避免出现错误。在修改、追加或者删除数据时设置为0。

二、利用SQL语句操作EXCEL实例操作

1、利用SQL语句查看EXCEL表中的数据

例如我们有一个EXCEL文档“SAMPLE.xlsx”，里面有一个工作表“sheet1”，我们要查看或浏览其中的数据，我们用SQL语句：

Select *

FROM [sheet1$]

IN 'D:\SQL for ACCESS\SAMPLE.xlsx' 'EXCEL 12.0 XML;HDR=YES;IMEX=1;'

或者：

Select *

FROM [sheet1$]

IN '' 'EXCEL 12.0 XML;HDR=YES;IMEX=1;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx'

也可以是这样：

Select *

FROM [EXCEL 12.0 XML;HDR=YES;IMEX=1;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx].[sheet1$];

这里需要指出的是在查询EXCEL中的工作表时，表名后一定要加上“$”美元符号。

2、利用SQL把ACCESS库中的数据导出到EXCEL工作表中

Select * INTO [EXCEL 12.0 XML;HDR=YES;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx].[SHEET2]

FROM 发票;

或者：

Select *

INTO [Sheet3]

IN 'D:\SQL for ACCESS\SAMPLE.xlsx' 'EXCEL 12.0 XML;HDR=YES;'

FROM 发票;

或者：

Select *

INTO [Sheet4]

IN '' 'EXCEL 12.0 XML;HDR=YES;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx'

FROM 发票;

这里需要说明的是当将ACCESS数据导出为EXCEL工作时，工作表的名字后不能有“$”美元符号。

3、用SQL语句删除EXCEL工作表中的数据

例如我们在上例中我们将ACCESS表中的数据导出到了“SAMPLE.xlsx”文档的“sheet4”工作表中了，现我们想将此表中的数据全部删除，我们可以用以下SQL语句：

Drop TABLE [EXCEL 12.0 XML;HDR=YES;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx].[sheet4];

此查询运行后，我们再打开“SAMPLE.xlsx”文档，会发现工作表“sheet4”中的数据全部被删除了，但表本身不会被删除。

4、利用SQL将ACCESS表中的记录导追加到EXCEL工作表中

Insert INTO [sheet2] IN 'D:\SQL for ACCESS\SAMPLE.xlsx' 'EXCEL 12.0 XML;HDR=NO;IMEX=0;'

Select *

FROM 发票 IN 'D:\SQL for ACCESS\罗斯文商贸.accdb';

这个语句可以将“D:\SQL for ACCESS\罗斯文商贸.accdb”数据库中的“发票”表中的数据追加到“D:\SQL for ACCESS\SAMPLE.xlsx”文档的“SHEET2”工作表中。

需要说明的是这个追加操作语句中工作表名后可以带“$”美元符号，当然也可以不带，效果是一样的。

5、运用SQL语句更新EXCEL工作表中的数据

Update [sheet2$] IN 'D:\SQL for ACCESS\SAMPLE.xlsx' 'EXCEL 12.0 XML;HDR=NO;IMEX=0;'

SET 发票日期=#2018-05-22#

Where [发票 ID]=6;

这个SQL语句执行后将会把示“D:\SQL for ACCESS\SAMPLE.xlsx”例工作薄中的“sheet2”工作表中的“发票 ID”为“6”的记录的“发票日期”列的值更改为“2018-05-22”。

6、自用SQL在EXCEL工作薄中创建数据表

Create TABLE [EXCEL 12.0 XML;HDR=NO;IMEX=0;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx;].[学生]

(

  学号 INT,

  姓名 TEXT(10),

  性别 TEXT(1),

  年龄 INT,

  生日 DATE,

  电话 TEXT(11)

);

这个语句执行后会在工作薄中健创建一个有列标题的工作表。

总之，EXCEL工作薄就是一个数据库，我们完全可以可以用SQL把它当数据库操作，只是它的数据没有数据库那样有严格的定义罢了。唯一不能操作的是“Delete”语句，当我们执行“Delete FROM [EXCEL 12.0 XML;HDR=YES;IMEX=0;DATABASE=D:\SQL for ACCESS\SAMPLE.xlsx].[sheet2] Where [发票 ID]=7;”这样的语句时，系统会提示“该ISAM不支持在链接表中删除数据”。不过如果真想一定要用SQL来删除EXCEL工作表中的一行也不是不可能的，只是变通一下，我们可以用“Update”语句将所有的有数据的列都设置成“NULL”就可以了，大家可以自己尝试一下。