Problem casting field when querying spreadsheet

Question

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.

Answer 1

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.

Answer 2

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

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes = Text
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 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.

Answer 3

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!

Answer 4

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.

Answer 5

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.