SQL to count double quotes in a text file

[Deutz]

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. If there is no value in the cell then I just write two sets of double quotes to the file. 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.

Open ThisWorkbook.Path & "\myTextFile.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 [myTextFile.txt] "
strSQL = strSQL & "WHERE [Test] = '" & Chr(34) & Chr(34) & "'"<!-- END TEMPLATE: bbcode_code -->

 

[Fazza]

what if you use
WHERE [Test] Is Null

 

[Deutz]

Hi Fazza,

Tried Is Null but that still returns zero despite there being two rows in the text file with nothing but ""

 

[Deutz]

Think I have solved this by subtracting count of values from total rows ...

strSQL = "SELECT  " & lngTotalRows & "-Count([Test]) "
strSQL = strSQL & "FROM [777.txt] "
strSQL = strSQL & "WHERE [Test] IS NOT NULL"<!-- END TEMPLATE: bbcode_code -->

 

[Fazza]

very good