Split CSV String

Question

How would I split the following string?

test, 7535, '1,830,000', '5,000,000'

The result should be

test
7535
'1,830,000'
'5,000,000'

I try:

Dim S() as string = mystring.split(",")

But I get,

test
7535
'1
830
000'
'5
000
000'

Thanks

Answer 1

Don't parse CSV manually when you have handy good quality libraries available. Please!

CSV parsing has many many potential pitfalls and this library, according to my testing, solves most of them neatly.

That said, if this is a one off task and the strings are always like your example, you can use regex, like this (VB.NET syntax might be wrong, please fix):

        Dim s as string = "1, 2, '1,233,333', '8,444,555'";
        Dim r as Regex = new Regex(",\s");
        Dim re() as string = r.Split(s);

This counts on that there is always a space after the separating comma and that there is no space in the commas between the numbers. If that's not always the case you can:

• Make the regex more complex (look here to see how messy things could get)
• Use the library and be happier

Answer 2

Dim words as New List(Of String)()
Dim inQuotes as Boolean
Dim thisWord as String
For Each c as Char in String
    If c = "'"c Then inQuotes = Not inQuotes
    If c = ","c AndAlso Not inQuotes Then
        words.Add(thisWord)
        thisWord = Nothing
    Else
        thisWord &= c
    End If
Next

Answer 3

Try to use this RegExp: "('([^']|'')*'|[^',\r\n]*)(,|\r\n?|\n)?"