Results 1 to 3 of 3

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

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2016
    Posts
    12

    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
    Last edited by SeanVb; Mar 13th, 2018 at 02:23 AM. Reason: formatting

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

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

    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.

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2016
    Posts
    12

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

    Quote Originally Posted by jdc2000 View Post
    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
    Last edited by SeanVb; Mar 14th, 2018 at 05:17 AM.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width