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

score 8 · Accepted Answer · edited May 23 '17 at 12:25

8

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

edited May 23 '17 at 12:25

Community

1
1

answered Jun 06 '09 at 10:55

Vinko Vrsalovic

330,807
53
334
373

1

I disagree about the need to necessarily use a CSV library. If you know the CSV file is well-formatted, then a simple method using ReadLine and Split will do the job perfectly well. – Noldorin Jun 06 '09 at 11:29
However, in this situation it is indeed advisable, given that there are comma-delimited numbers in fields. – Noldorin Jun 06 '09 at 11:30
2

So, in other words, you are saying you completely agree with me :) – Vinko Vrsalovic Jun 06 '09 at 12:46
Great pair programming guys :) – D'Arcy Rittich Jun 06 '09 at 14:10
You're assuming the OP is parsing a file's worth of CSV. Taking a dependence on a CSV parsing library would certainly be overkill to split a string (which is what was asked). – Mark Brackett Jun 06 '09 at 16:00
@Mark: I agree, and is one of the meanings I meant with 'a one off task' (third paragrah). Because it seems to me very likely he's actually parsing a CSV file instead of only one string I added the warning. Additionally, this library is very small and efficient so it's not a big concern. – Vinko Vrsalovic Jun 06 '09 at 19:27
This is crazy. Why not use the built in [TextParser class](http://msdn.microsoft.com/en-us/library/cakac7e6.aspx)? – Adam Zuckerman Feb 20 '14 at 00:42

score 2 · Answer 2 · answered Jun 27 '11 at 13:58

If just for this example, no need to use regexp, the Split-function (Member of Microsoft.VisualBasic.Strings) can take a string as delimeter, so just enter ", " to catch only those commas with space after:

    Dim s As String = "1, 2, '1,233,333', '8,444,555'"
    Dim r() As String = Split(s, ", ")

score 1 · Answer 3 · answered Jun 06 '09 at 13:05

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

score 0 · Answer 4 · answered Jun 06 '09 at 14:05

0

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

answered Jun 06 '09 at 14:05

iburlakov

4,164
7
38
40

Split CSV String

4 Answers4

Linked