How to remove a special character (precisely double quotes) from the adodb recordset while exporting it to CSV in vb6?

user_8129743 0
Viewed 88

My requirement is to remove the special characters especially double quotes from the adodb recordset while exporting to CSV in VB6 using the below code.

if any double quotes is present the value after double quotes is moved to next column.

Specifically, the double quotes are present in rsData.Fields(K).Value not in rsData.Fields(K).Name. I'm not sure how to remove the double quotes in rsData.Fields(K).Value.

Any help to fix this issue will be greatly appreciated. Thanks in advance.

'Converting the recordset 

Public Function RecordsetToCSV(rsData As ADODB.Recordset, Optional ShowColumnNames As Boolean = True, Optional NULLStr As String = "") As String
    Dim K As Long, RetStr As String
    
    If ShowColumnNames Then
        For K = 0 To rsData.Fields.Count - 1
            RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"
        Next K
        
        RetStr = Mid(RetStr, 2) & vbNewLine
    End If
    
    RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)
    RetStr = Left(RetStr, Len(RetStr) - 3)
    
    RecordsetToCSV = RetStr
End Function 

'Creating CSV file
    Dim CSVData As String
    CSVData = RecordsetToCSV(rsData, True)

    Open "C:\test.csv" For Binary Access Write As #1
        Put #1, , CSVData
    Close #1

reference : https://www.vbforums.com/showthread.php?481705-VB6-Save-Recordset-to-CSV-format

vb6 csv
2 Answers

You need to escape the double quotes, which is done by doubling them up.

RetStr = RetStr & ",""" & Replace(rsData.Fields(K).Name, """", """""") & """"
user_41743570

You could just use the defaults to import the data with double quotes by using the character as the delimiter

Change:

RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"

To:

RetStr = RetStr & "," & vbTab & rsData.Fields(K).Name & vbTab

And

RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)

To:

RetStr = RetStr & rsData.GetString(adClipString)

And you might have to change the last line that removes those last characters to

RetStr = Left(RetStr, Len(RetStr) - 2)

EDIT - If you really truly feel the double quotes must be removed from your data, you can do it by using a different delimiter initially - say the squiggle "~" and then replace double quotes in one step, and the squiggle in next step

As in:

RetStr = RetStr & "~" & rsData.GetString(adClipString, -1, "~")

Last lines would then be

RetStr = Replace(RetStr,"""","")    
RecordsetToCSV = Replace(RetStr,"~","""")

And of course remember to use the "~" in your column header loop

RetStr = RetStr & ",~" & rsData.Fields(K).Name & "~"
user_24191280
Related