返回首页
当前位置: 主页 > 精通Office > Excel教程 >

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

时间:2014-04-30 23:22来源:知行网www.zhixing123.cn 编辑:麦田守望者

在前面的文章里,我已经提到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,所以该问题引起分析数据异常。

具体原因还未找到。

------分隔线----------------------------
标签(Tag):EXCEL excel2013 Excel技巧 Excel2007 Excel2003 Excel2010 Excel教程 Excel实例教程
------分隔线----------------------------
推荐内容
猜你感兴趣