Please help, Excel ADO OLEDB SQL Statement problem

Question

I'm reading from an excel XLS worksheet with no header rows. Some cells in the column have a list of numbers like 12345,12346,12347,12348. Other cells only have one number 12345.

The "LIKE" operator finds the number when there is multiple numbers in a cell, but doesn't find the cells where only one number exists.

SQL = "SELECT * FROM [2010 VIP$] WHERE F9 LIKE '%" & sDealer & "%'"

I tried changing my connection string from: "Data Source=" & dS & ";Extended Properties=""Excel 8.0;HDR=No"""

To (adding IMEX for mixed data types): "Data Source=" & dS & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

But I get an unknown error when IMEX is added. It's my understanding you can't use the F1, F2, F3 field names without HDR=No.

I tried using the first connection string but changed my SQL to: SQL = "SELECT * FROM [2010 VIP$] WHERE F9 LIKE '%" & sDealer & "%' OR F9='" & sDealer & "'"

But it still doesn't find the cells with only one number.

WHAT GIVES.

Any help would be greatly appreciated.

EDIT: Thanks for the helpful feedback guys, I ended up just using a slower method but it works and still checks 1200 rows in like 2 seconds:

Dim cN As New ADODB.Connection
Dim rS As New ADODB.Recordset
Dim SQL As String
Dim dDealer As Double
Dim WS As Worksheet
Dim sDealer As String, sAmount As String
Dim bFound As Boolean
Set WS = ActiveSheet
cN.Provider = "Microsoft.Jet.OLEDB.4.0"
cN.Open "Data Source=" & MostRecentPath & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1"""
For dDealer = 2 To WS.Range("a60000").End(xlUp).Row
    sAmount = WS.Range("c" & dDealer).Value
    If Len(sAmount) > 0 Then GoTo skipOne
    sDealer = Trim(WS.Range("i" & dDealer).Value)
    If Len(sDealer) <> 5 Then GoTo skipOne
    If IsNumeric(sDealer) = False Then GoTo skipOne
    SQL = "SELECT * FROM [2010 VIP$]"
    rS.Open SQL, cN, adOpenStatic, adLockOptimistic
    bFound = False
    Do While rS.EOF = False
        If InStr(1, rS.Fields(8).Value, sDealer) > 0 Then
            bFound = True
            Exit Do
        End If
        rS.MoveNext
    Loop
    rS.Close
    If bFound = True Then WS.Range("l" & dDealer).Value = "VIP"
    DoEvents
skipOne:
Next dDealer
cN.Close

Answer 1

The single-number cells are numeric and won't be found using the string-based like operator. You could try forcing the cells to be text cells by prepending the number with an apostrophe (eg. '12345).

You could also try

SELECT * FROM [2010 VIP$] WHERE F9 LIKE '%" & sDealer & "%' OR F9=" & sDealer & "

(no single quotes in the second part of the where clause)

Answer 2

As well as setting the IMEX option, sort the data in the worksheet to ensure that the first eight rows contain cells that will be interpreted as text - i.e. ones with multiple values like 12345,12346,12347,12348

Quoting from this KB article:

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

Alternatively, consider normalising your data in the spreadsheet as this is the real problem here

Answer 3

First, I agree with @barrowc: the underlying problem is that your 'lists of numbers' violates first normal form (1NF) and SQL is not designed to query non-scalar data types (i.e. lacks operators to exploit multi-valued data).

Second, you need to get your connection string and registry settings correct for ADO to 'see' the column as text. This article may help with this.

If you must work with the not first normal form (NFNF) data, you will need to handle the comma delimiters.

Here is some standard SQL with test data to demonstrate the point:

WITH Dealers (dealer_ID, delear_list)
     AS
     (
      SELECT dealer_ID, delear_list
        FROM (
              VALUES (1, '12345,12346,12347,12348'), 
                     (2, '12344,12345,12346'), 
                     (3, '12343,12344,12345'), 
                     (4, '12345'), 
                     (5, '12399,12346,12347,12348'), 
                     (6, '12344,12399,12346'), 
                     (7, '12343,12344,12399'), 
                     (8, '12399')
             ) AS Dealers (dealer_ID, delear_list)
     )
SELECT dealer_ID, delear_list
  FROM Dealers
 WHERE (',' + delear_list + ',') LIKE ('%,12345,%');

Obviously, you'd need to port this to ACE/Jet dialect code e.g.

WHERE (',' & delear_list & ',') ALIKE ('%,12345,%');