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

[SeanVb]

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)

        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