Keeping Speech Marks In a Field When Using ADODB To Read/Write a CSV

Hi,

Another project of mine required looking up account numbers from an Excel spreadsheet but the machine does not have Excel on it, so ADODB was the way to go. For the below code I found using a select statement would be easier and quicker to simply sort a comma separated CSV file in this new project. However, there is a field in this database that contains a full address separated by commas and enclosed by speech marks. (aka text qualifiers) Is there a way to keep the speech marks that are wrapping the field when using ADODB? Because when I output the data, or write it to console, the qualifiers are gone and the data is not incorrect. (FYI I'm using MS VB 2010 to debug my vbscript)

Code:

---

        Dim cn, rs, strPathtoTextFile, fso, ts, Line
        Const adOpenStatic = 3
        Const adOpenForwardOnly = 0
        Const adLockOptimistic = 3
        Const adCmdText = &H1

        'Use 'Set' when in Workflow
        cn = CreateObject("ADODB.Connection")
        rs = CreateObject("ADODB.Recordset")

        strPathtoTextFile = "C:\Users\my data location\"

        cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & strPathtoTextFile & ";" & _
                  "Extended Properties=""text;HDR=YES;FMT=Delimited""")
       
        'Sort recordset by Contract number
        rs.Open("SELECT * FROM Statement.csv ORDER BY Contract", _
                  cn, adOpenForwardOnly, adLockOptimistic, adCmdText)

        'Create file system obj and output file
        fso = CreateObject("Scripting.FileSystemObject")
        ts = fso.OpenTextFile(strPathtoTextFile & "output.csv", 2, True)

        Line = ""

        'Store header in Line
        For Each tmp In rs.Fields
            Line = Line & tmp.Name & ","
        Next
       
        Dim afield
        afield = rs("BR ADD").Value
        Console.Write(afield)

        'Write header while omitting a comma from been added to end of string
        ts.WriteLine(Left(Line, Len(Line) - 1))

        'Write recordset in batches of 1000 to increase performance
        'GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
        Do Until rs.EOF
            ts.Write(rs.GetString(, 1000, ",", vbCrLf, ""))
        Loop

        'Clean up
        Line = ""
        rs.close()
        rs = Nothing
        ts.close()
        cn.Close()

---

Your advice is much appreciated.

Regards,
S

You could try using a Tab delimited .csv file instead of Comma delimited. That should allow you to read in the fields with quotes and apostrophes intact. If that part works, and you can read in the data with punctuation, then you should be able to write it back out the same way, to a Tab delimited file.

Quote:

---

Originally Posted by jdc2000

You could try using a Tab delimited .csv file instead of Comma delimited. That should allow you to read in the fields with quotes and apostrophes intact. If that part works, and you can read in the data with punctuation, then you should be able to write it back out the same way, to a Tab delimited file.

---

Hi jdc,

Thanks for your reply. However I prefer to manipulate the data as I received it. At the expense of performance (+-2 seconds), I managed a known workaround. Using FSO I created a temp file, I then looped through the input data and used the Split function to break the record down into an array. I then targeted the array indexes and escaped the qualifiers, used Join and wrote the records to said temp file. Now that the qualifiers were escaped, the temp file was used via ADO to sort the data and the field in question retains its qualifiers.

Code:

---

        x = 0
        Do Until inputObjFile.AtEndOfStream
            ReDim Preserve newDataLine(x)
            If x <> 0 Then
                'Modify & write records
                oldDataLine = Split(inputObjFile.ReadLine, ",")
                oldDataLine(29) = """""" & oldDataLine(29)
                oldDataLine(32) = oldDataLine(32) & """"""
                newDataLine(x) = Join(oldDataLine, ",")
                NewRecordsFile.Write(Left(newDataLine(x), Len(newDataLine(x)) - 1) & vbCrLf)
            Else
                'Write header
                NewRecordsFile.Write(inputObjFile.ReadLine & vbCrLf)
            End If
            x = x + 1
        Loop
        NewRecordsFile.close()
        inputObjFile = Nothing
        NewRecordsFile = Nothing
        inputObjFSO = Nothing

---

Now I knew I could have done this all along. I just hoped there was a more elegant way that I was not aware of.

So if anyone does know a way, please do share.

Regards,
S