Results 1 to 2 of 2

Thread: Using SQL to search a text file for double quotes

  1. #1

    Using SQL to search a text file for double quotes

    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts