PDA

View Full Version : Using SQL to search a text file for double quotes



deutz
2015-06-29, 04:54 AM
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 ...



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) & "'"

deutz
2015-07-02, 06:45 AM
Think I've solved this by simply subtracting the count of values from the total rows ...



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