Using SQL to search a text file for double quotes

deutz

New member
Joined
May 28, 2012
Messages
27
Reaction score
0
Points
0
Hi and thanks in advance,

I am using Excel 2010 and have some vba that writes values in a column to a text file. When I write to the file I enclose each value in double quotes. I then want to run some SQL on the text file to return a count of instances where there is no data, where there is just the two double quotes ... "". I have tried a number of variations of WHERE criteria but I always get back a count of zero despite there being a couple of rows in the text file which contain the two double quotes ""

Here is the SQL ...

Code:
        Open ThisWorkbook.Path & "\777.txt" For Output As 1
        Print #1, Chr(34) & "Test" & Chr(34)
        For Each rng In rngSel
            Print #1, Chr(34) & rng & Chr(34)
        Next rng
        Close #1
        Set db1 = CreateObject("ADODB.Connection")
        db1.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=" & ThisWorkbook.Path & ";"
        strSQL = "SELECT  Count([Test]) as myCount "
        strSQL = strSQL & "FROM [777.txt] "
        strSQL = strSQL & "WHERE [Test] = '" & Chr(34) & Chr(34) & "'"
 
Think I've solved this by simply subtracting the count of values from the total rows ...

Code:
strSQL = "SELECT  " & lngTotalRows & "-Count([Test]) "
strSQL = strSQL & "FROM [777.txt] "
strSQL = strSQL & "WHERE [Test] IS NOT NULL"
 
Back
Top