Excel 文件作为数据库源时如何判断数据类型

作者: , 共 1579 字

在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式

  • 通过名称引用。比如如果定义一个数据区域为Industry,那么select * from industry这样是可行的。这种方法最多支持 65535 行数据,当数据行数过多时, Excel 会提示找不到该数据表。同一张工作表里可以有多个数据表,通过定义不同的名称去引用。
  • 通过工作表名引用。比如一个工作表名为Quotes,那么select * from Quotes$这样是可行的。这里工作表名后面的 $ 号表示这是一个工作表。工作表可以包含高达 100 万行数据。但同一个工作表内只能有一个数据表。
  • 可以通过数据表的地址进行引用。比如select * from Quotes$A1:B10000
  • 上面的引号可以用中括号代替。比如select * from [Quotes$A1:B10000]
  • 如果数据表不在目前工作的文件内,需要在上面的数据表名前添加数据文件的路径和文件名,比如select * from [D:\test.xlsx].[Quotes$]

当数据量较大时,使用vlookupmatch之类的数据表联立的方法计算效率太低,所以我之前在工作已经大量使用上述技术,将 Excel 的表当作数据库表进行join操作。 但随之而来的一个问题是, Excel 如何确认表中的每一列的数据类型。若不搞清楚这个问题,操作过程中很可能发生莫名奇妙的问题。

目前我在Excel 数据库操作函数类中推荐的 Excel 数据库连接字符串为:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & ThisWorkbook.FullName & """; Extended Properties=""Excel " & Application.Version & ";HDR=Yes;"";"

上面的HDR=Yes代表所引用的数据表含列名。此时, Excel 将扫描每一列的前 8 行,根据前 8 行元素的大多数确定该列的数据类型:

  1. 若该列前 8 行均为空,该列被认为是文本类型。
  2. 若该列前 8 行非全空,并且空行和数字行超过 4 行,则该列被认为数字类型。
  3. 其它情况下,该列被认为是文本类型。

当该行被认为是数字类型,所有文本都会被丢弃,空行保留原样。当该行被认为是文本类型时,数字将被转化为文本显示。

为防止丢失数据,可在Extended Properties中指定IMEX=1,此时当前 8 行中既存在非空文本行,又存在数字行,那么该列将被认为是文本类型。不过IMEX=1为只读导入模式,所以不能用在当前文件(否则会引起 Excel 文件奔溃,慎用)。

通过修改注册表,可以让 Excel 扫描更多行来确定每一列的数据类型。具体位置为[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel] 下的TypeGuessRows键,默认为 8。设置为 0 时可强制 Excel 扫描所有行确定数据类型。注册表位置中的 12.0 需改成 Excel 对应的版本号。

不过除上所述外,还有其它因素影响类型的判断。因为我还遇到过以下情况:

  • 一列只包含空行以及文本行,在操作时所有文本都丢失,该列全空。
  • 一列数据被识别为日期。对于一个负数,在转为日期,再进行 sum 求和时,就会减少 1 ,所以该问题引起分析数据异常。

具体原因还未找到。

Q. E. D.

类似文章:
2014-03-25 更新:我已经将该类修改成函数形式,并增加新功能,参见更新 Excel 的数据库查询函数库
Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
Excel 有一个很有用的功能是直接导入外部数据库或者使用外部数据源建立数据透视表和数据透视图。但比较可惜的是,这个数据源的查询语句是静态的,它无法根据日期自动修改(比如在应用中,我们希望每天获取的外部数据都是当天最新的数据),下面两个函数是修改外部数据源的 VBA 代码,调用它们就可以建立动态的数据源。
Excel、Matlab 在与数据库交互数据时,通常需要事先配置好 ODBC 数据源,这导致一个文件换到另一台机器还需要重新配置 ODBC。手工配置太麻烦,从国外一个网站看到一种解决方案:
Matlab 的 database 工具箱只支持 ODBC 数据库连接,在使用之前需要先设置 ODBC 数据源。之前在 Excel 里用的是 ADODB ,使用 ADODB 的好处是无需设置 ODBC 源,似乎效率也要高一些(但我一直没弄懂 ADODB 是什么东西,所以效率这东西我也说不清)。
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
编程 » Excel, VBA
VBA 的 Date 类型比较奇怪。
编程 » Excel
最近看到一个比较有趣的问题, Excel 中以下表达式代表什么含义:
比特币协议里使用了 ECDSA (椭圆曲线签名算法),我之前以为它和基于大数分解的 RSA 公钥密码体系差不多。这两天看了下维基百科,才发现它们之间的差异挺大。
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。