代码之家 › 专栏 › 技术社区 › Tester101

查询电子表格时转换字段时出现问题

spreadsheet vbscript sql

Tester101 · 技术社区 · 16 年前

我试图使用VBScript查询.xls电子表格,但在尝试强制转换字段时遇到问题。
我像这样连接到电子表格。

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataSource & ";Extended Properties=""Excel 8.0;HDR=No;"";"

Set objRecordset = CreateObject("ADODB.Recordset")
StrQuery = "SELECT * FROM [Sheet1$] WHERE F1 >= 2.3456 OR CAST(F1 AS VARCHAR) = '*'"
objRecordset.Open StrQuery, objConnection, adOpenDynamic, adLockOptimistic

这会导致未指定的错误80004005。我做错了什么?
注意:我也尝试了转换,但得到了一个未定义的函数错误。

5 回复 | 直到 16 年前

Kyle B. 16 年前

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes = Text
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0

我还将您的连接字符串修改为以下内容:

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/PathTo/YourFile/" & Filename) & ";"
If chkUploadFileColumnsFirstRow.Checked Then
  sConnectionString &= "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
Else
  sConnectionString &= "Extended Properties='Excel 8.0;IMEX=1'"
End If

如果设置这些条件后失败,我会觉得您的工作表名称或查询本身有问题。

Andomar 16 年前

IMEX=1; 到扩展属性。它告诉jetdb以文本形式读取混合的数字、日期和字符串。那么你应该能够:

SELECT * FROM [F1$]

也就是说,如果 F1 是第一个工作表的名称。

onedaywhen 16 年前

问题是Microsoft Jet 4.0引擎不支持 CAST() 功能,也没有 VARCHAR 关键词(nor) 数据类型,到此为止)。你为什么要投这个值?你需要处理这个问题吗 NULL

说到数据类型:

F1 >= 2.3456

2.3456 是类型的文本 DECIMAL 是定点小数。Excel没有本机的固定小数类型,因此您很可能正在比较浮点值和固定点值。。我相信你会理解这会带来的问题!

onedaywhen 16 年前

你的谓词

F1 >= 2.3456

暗示 F1 是数字数据类型。请解释如何将数值转换为 VARCHAR 并期望得到 *

如果列包含数值和 * FLOAT (在这种情况下,*字符将替换为 NULL 值)或类型 NVARCHAR(255) 也许

您可能可以使用注册表值来影响结果,但不希望在SQL代码中这样做,因为已经选择了数据类型。

更多详细信息请参见 Daily Dose of Excel: External Data - Mixed Data Types 一位所谓的专家说:)评论中有很多细节。

onedaywhen 16 年前

我认为你需要:

移除 CAST 和 VARCHAR 根据您的查询(它们是非法的);
钥匙 TypeGuessRows = 0 扫描所有行应确定列为混合类型;
确保本地计算机注册表钥匙 ImportMixedTypes = Text 将混合类型作为文本导入,如下所示: 默认值)。有关详细信息,请参阅前面的答案。

完成后,“*”字符将出现在列中(即不再显示 NULL )。但是,您的浮点值将被强制为文本。您可以在SQL中强制/强制将浮点值转换为文本值,使其返回为数值,但首先必须测试 无效的 和一个数值,例如。

SELECT F1, 
       IIF(
           F1 IS NULL, 
           '{{Excel cell is blank}}', 
           IIF(
               F1 = '*', 
               '{{Excel cell is the ''*'' character}}', 
               IIF(
                   ISNUMERIC(F1), 
                   CDBL(F1), 
                   '{{Excel cell is non-nmeric and not the ''*'' character}}'
                  )
              )
       ) AS result       
FROM [F1$];

SWITCH() 过巢 IIF() IIF() 如果为Access数据库引擎SQL重载,则 TRUE 或者 FALSE 开关()