English 中文(简体)
Problem casting field when querying spreadsheet
原标题:

I am trying to query an .xls spreadsheet with VBScript, but I have run into an issue when trying to cast a field.
I connect to the spreadsheet like this.

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

Then I try to query the spreadsheet. The field I am interested in contains decimal values, but can also contain a * as a wildcard. So what I m trying to do is cast the field to a varchar so I can check for the *.

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

This causes an unspecified error 80004005. What am I doing wrong here?
NOTE: I also tried CONVERT, but got an Undefined Function error.

最佳回答

You could try to append IMEX=1; to the extended properties. That tells JET DB to read mixed numbers, dates and strings as text. Then you should be able to:

SELECT * FROM [F1$]

That is, if F1 is the name of the first worksheet.

问题回答

I would change the following registry keys on your server (after having a backup of course):

HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcelImportMixedTypes = Text
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcelTypeGuessRows = 0

I would also modify your connection string to the following:

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

If it fails after setting those criteria, I would feel something is wrong with either your sheet name, or the query itself.

The problem is that the Microsoft Jet 4.0 engine does not support the CAST() function, nor does it has a VARCHAR keyword (nor VARCHAR data type, come to that). Why do you need to cast this value? Do you need to handle the NULL value?

Speaking of data types:

F1 >= 2.3456

The value 2.3456 is a literal of type DECIMAL being fixed point decimal. Excel has no native fixed decimal type so you are most likely comparing a floating point value to fixed point value.. I trust you appreciate the problems this can cause!

Your predicate

F1 >= 2.3456 

suggests F1 is a numeric data type. Please explain how you can cast a numeric value to VARCHAR and expect to get * as a result...? I can t see it happening myself!

If your column contains numeric values and * characters then it will be of mixed types and the Jet 4.0 engine needs to to decide whether to choose FLOAT (in which case your * characters will be replaced by the NULL value) or of type NVARCHAR(255) (in which case your numeric values may be converted to scientific notation).

You may be able to use registry values to influence the result but have no hope of doing it in the SQL code because the data type has already been chosen.

More more details see Daily Dose of Excel: External Data - Mixed Data Types by a so-called expert :) There s a lot of good detail buried in the comments.

I think you need to:

  1. remove the CAST and VARCHAR keywords from your query (they are illegal);
  2. change the local machine registry key TypeGuessRows = 0 (force to scan scan all rows should determine the column is of mixed types...);
  3. ensure the local machine registry key ImportMixedTypes = Text (...to import mixed types as text, being the default value). See previous answers for details.

Once done your * characters will appear in the column (i.e. will no longer be NULL). However, your float values will have been coerced to text. You can cast/coerce the float-as-text values back to being numeric in the SQL but first you will have to test for NULL and a numeric value e.g.

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$];

Note I d normally prefer SWITCH() over nest IIF() but in this case it must be IIF(). Reason: IIF() if overloaded for Access Database Engine SQL and either the TRUE or the FALSE condition will be evaluated but never both. In VBA the opposite is the case i.e. both conditions are always evaluated. SWITCH() does not shortcut, either in Access Database Engine SQL or VBA.





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...

热门标签