none
How to create CSV from ADODB.Recordset from variable RRS feed

  • Question

  • Is it possible to create a new CSV file from ADODB.Recordset and append data from variable?
    Monday, July 23, 2018 3:20 AM

Answers

  • Hello Frederick Lim,

    You could try to export data in the  record set to a string and then write the string to the csv file.

    For example,

    ResultString = ""
    Set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile("C:\Users\terryx\Desktop\TestFolder\Test.csv")
    If Not (rs.BOF Or rs.EOF) Then
    rs.MoveFirst
    'set title row
    For i = 0 To rs.Fields.Count - 1
     ResultString = ResultString & rs.Fields(i).Name & ","
    Next i
    ResultString = Left(ResultString, Len(ResultString) - 1) & vbCrLf
    
    Do
    For i = 0 To rs.Fields.Count - 1
     ResultString = ResultString & rs.Fields(i).Value & ","
    Next i
    ResultString = Left(ResultString, Len(ResultString) - 1) & vbCrLf
    rs.MoveNext
    Loop Until rs.EOF
    End If
    Set rs = Nothing
    outFile.WriteLine ResultString

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Frederick Lim Wednesday, September 12, 2018 7:54 AM
    Monday, July 23, 2018 8:27 AM
  • Thanks for answer.

    I didn't try the above solution yet but eventually I use ADO.Stream in order to create utf-8 file properly.

    I use the following to append text

    With objstream

    .LoadFromFile somefile

    .Position = .Size

    .SetEOS

    .WriteText

    .SaveToFile somefile, 2

    End With

    • Marked as answer by Frederick Lim Wednesday, September 12, 2018 7:54 AM
    Friday, August 10, 2018 6:13 AM

All replies

  • Hello Frederick Lim,

    You could try to export data in the  record set to a string and then write the string to the csv file.

    For example,

    ResultString = ""
    Set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile("C:\Users\terryx\Desktop\TestFolder\Test.csv")
    If Not (rs.BOF Or rs.EOF) Then
    rs.MoveFirst
    'set title row
    For i = 0 To rs.Fields.Count - 1
     ResultString = ResultString & rs.Fields(i).Name & ","
    Next i
    ResultString = Left(ResultString, Len(ResultString) - 1) & vbCrLf
    
    Do
    For i = 0 To rs.Fields.Count - 1
     ResultString = ResultString & rs.Fields(i).Value & ","
    Next i
    ResultString = Left(ResultString, Len(ResultString) - 1) & vbCrLf
    rs.MoveNext
    Loop Until rs.EOF
    End If
    Set rs = Nothing
    outFile.WriteLine ResultString

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Frederick Lim Wednesday, September 12, 2018 7:54 AM
    Monday, July 23, 2018 8:27 AM
  • Thanks for answer.

    I didn't try the above solution yet but eventually I use ADO.Stream in order to create utf-8 file properly.

    I use the following to append text

    With objstream

    .LoadFromFile somefile

    .Position = .Size

    .SetEOS

    .WriteText

    .SaveToFile somefile, 2

    End With

    • Marked as answer by Frederick Lim Wednesday, September 12, 2018 7:54 AM
    Friday, August 10, 2018 6:13 AM