Commas in CSV fle

[sacsac]

I have a large CSV file, and I need to enclose every field with double quotes (a requirement for the application importing it). I had thought it would be an easy task, but it has beaten me!
I have tried inserting double quotes each side of the commas, which I though would work, but I either get no quote marks or 'double-double' quote marks. Can anyone suggest a simple bit of code to put me out of my misery

 

[gmmastros]

This is probably an easy fix, but I think more information would be helpful.

Are you starting with a CSV file, or are you creating it?

If you are creating it, what is the source?

What does your code look like now?

 

[Andrzejek]

I would try something simple, like:

Dim strText As String
strText = "This is,my test,for commas,the,end"
strText = Chr(34) & Replace(strText, ",", Chr(34) & "," & Chr(34)) & Chr(34)
Debug.Print strText

 

[strongm]

Something like the following:

Public Function QuoteCSVItems(strList As String) As String
    strList = Replace(strList, ", ", ",")
    QuoteCSVItems = Chr(34) & Join(Split(strList, ","), """, """) & Chr(34)
End Function

But be warned, this could result in errors if any of the fields in the CSV already have quotes. So George's question is important.

 

[sacsac]

This is bizarre. Both the methods suggested by strongm and Andrezek appear to work as far as the debug.print line. All looks good at that point and I see something like this:

Debug.Print sNewLine
"item1","item2","item3"........

However, if I write this to a text file like this:

intF = FreeFile
Open sDest For Output As intF
Write #intF, sNewLine
Close #intF

when I read it back in it shows as:
""item1"",""item2"",""item3""........

with double double quotes.

This is driving me crazy! Is it something to do with the way I'm writing it to the text file?

 

[gmmastros]

Try changing

Write #intF, sNewLine

to

Print #intF, sNewLine

 

[gmmastros]

From here: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/writestatement

Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file. You don't have to put explicit delimiters in the list. Write # inserts a newline character, that is, a carriage return-linefeed (Chr(13) + Chr(10) ), after it has written the final character in outputlist to the file.

The link here takes you to VBA documentation, which can sometimes be different from VB6, but in this case, they are the same.

 

[sacsac]

Try changing

Write #intF, sNewLine

to

Print #intF, sNewLine

Thanks so much. That was a very simple mistake I was making - I feel rather embarrassed. All fixed now. Very grateful!

 

[gmmastros]

This was not a simple mistake. It required knowledge of both commands, Print and Write. It also required knowing the nuanced difference between them.

Regardless, I'm glad you got your problem sorted.