Vb.net convert array to comma delimeted string

Question

I am reading through a csv file. Each line contains a comma delimeted string. I am trying to convert the string into an array in order to update a few strings with new values. I am using

 Lines(0) = "test, "The quick brown fox, ran through a log", Another sentence"
 words = Lines(0).Split(New Char() {","c})

The "The quick brown fox, ran through a log" is creating 2 entries in the words array

 words(0) = "test"
 words(1) = ""The quick brown fox"
 words(2) = "ran through a log""
 words(3) = "Another sentence"

how can I get the array to display as

 words(0) = "test"
 words(1) = ""The quick brown fox, ran through a log""
 words(2) = "Another sentence"

I then convert the words array back to a comma delimited string after I made my string changes

 newWords = String.Join(",", words.ToArray())

Handling escaped quotes is non-trivial. You should use a proven CSV parsing library instead, like `CsvHelper`: https://joshclose.github.io/CsvHelper/ — Dai, Oct 19 '21 at 17:13
Does this answer your question? [read csv file in vb.net](https://stackoverflow.com/questions/26791786/read-csv-file-in-vb-net) — GSerg, Oct 19 '21 at 17:16
Does this answer your question? [A string feeding a textfieldparser in vb.net](https://stackoverflow.com/q/13426037/11683) — GSerg, Oct 19 '21 at 17:24
In this case it makes more sense to split by `"`. Is there a case where that doesn't work? — djv, Oct 19 '21 at 17:34
`Lines(0) = "test, "The quick brown fox, ran through a log", Another sentence"` This line will not compile. The inner quotes need to single quotes. — Mary, Oct 19 '21 at 20:45

score 0 · Answer 1 · answered Oct 21 '21 at 00:17

You should consider using a library to do this. CSV can be hard.

Your string shows why - you have invalid CSV data. There shouldn't be a space after the , delimiter. A library can help you deal with these situations.

Try to NuGet "CsvHelper", then you can do this:

Using reader = New StringReader("test, ""The quick brown fox, ran through a log"" ,Another sentence")
    Dim config = New CsvHelper.Configuration.CsvConfiguration(System.Globalization.CultureInfo.InvariantCulture)
    config.HasHeaderRecord = False
    config.TrimOptions = CsvHelper.Configuration.TrimOptions.Trim 
    Using csv = New CsvHelper.CsvReader(reader, config)
        Dim anonymousTypeDefinition = New With
        {
            .FieldA = "",
            .FieldB = "",
            .FieldC = ""
        }
        Dim records = csv.GetRecords(anonymousTypeDefinition)
        Dim record = records.First()
        Console.WriteLine(record.FieldA)
        Console.WriteLine(record.FieldB)
        Console.WriteLine(record.FieldC)
    End Using
End Using

The output I get is:

test
The quick brown fox, ran through a log
Another sentence

Miguel · Answer 2 · 2021-10-19T19:32:12.837

One way is to replace the comma that is between quotation for some symbol that you know you'll not use anywhere, for example with "|". After the splitting is done you replace back the | by comma.

Dim i As Integer
Dim words() As String
Dim bQuoteStart As Boolean = False
Dim sTmp As String = "test, ""The quick brown fox, ran through a log"", Another sentence"
For i = 1 To sTmp.Length
    If Mid(sTmp, i, 1) = Chr(34) Then bQuoteStart = Not bQuoteStart
    If bQuoteStart AndAlso Mid(sTmp, i, 1) = "," Then sTmp = sTmp.Remove(i - 1, 1).Insert(i - 1, "|")
Next

words = sTmp.Split(New Char() {","c})
For i = 0 To words.Length - 1
    MsgBox(words(i).Replace("|", ","))
Next

Vb.net convert array to comma delimeted string

2 Answers2