Splitting text based on comma

Question

Possible Duplicate:
CSV parser/reader for C#?

I want to split the text using the Split function:

string str = "ZBEE10364,\"Cobler, CHARLOTTE J\",Whiskey,,Brandy,0:00:00,20110912,CHECK,2918,117.33,1,117.33,0,EDM0,Yu789";
string[] strArr = str.Split(',');

This works fine but "Cober and "CHARLOTTE are in different records. I don't want that. It is a CSV file and when I open it using Excel it works perfectly.

Cobler, CHARLOTTE J appears in a single column. How can I solve this?

http://stackoverflow.com/questions/906841/csv-parser-reader-for-c — Vlad, Nov 13 '11 at 13:59
http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file — Andreas, Nov 13 '11 at 14:00
Great. I eneded up using FileHelpers. Vlad you can post it as an asnwer. — Jaggu, Nov 13 '11 at 14:07

score 3 · Answer 1 · edited May 23 '17 at 10:24

3

Because you want to ignore , in records that are between quotes, the only way to do this is to parse the line.

Loop over the string with a boolean (true if between quotes) and then hand build the list/array only making a new item when the boolean is false.

As Andreas points out in the comments there is complete source located in this question:

Dealing with commas in a CSV file

edited May 23 '17 at 10:24

Community

1
1

answered Nov 13 '11 at 14:00

Hogan

69,564
10
76
117

score 2 · Answer 2 · edited May 23 '17 at 12:18

2

Might be overkill, but the OLE DB provider for JET can also read CSV files, and can also give you data of the proper types for each column. Example use in this question.

If you want to parse it by hand (which should be doable), you can refer to the Wikipedia article on CSV, which details the syntax a bit.

edited May 23 '17 at 12:18

Community

1
1

answered Nov 13 '11 at 14:07

Vlad

18,195
4
41
71

score 1 · Accepted Answer · answered Nov 13 '11 at 14:16

This helper method does the trick:

public static class StringSplitHelper{

  public static string[] SplitNonQuoted(this string str, char separator){
    if(string.IsNullOrEmpty(str)) return new string[]{};
    if(separator == '\"') throw new ArgumentException("Separator cannot be a quotation mark", "separator"); 
    List<string> fields = new List<string>();
    bool inQuotes = false;
    StringBuilder sb = new StringBuilder();
    foreach(var c in str){
        if(c == '\"')
        {
            inQuotes = !inQuotes;
        }
        else if(c == separator){
            if(inQuotes) {
                sb.Append(c);
            }
            else {
                fields.Add(sb.ToString());
                sb.Clear();
            }
        }
        else{
            sb.Append(c);
        }
    }
    return fields.ToArray();
  }
}

Then, instead of strArr = str.Split(',');, do strArr = str.SplitNonQuoted(this string str, ',');

@Jaggu, The source in the post that Hogan linked to has **a better generic solution** for parsing CSV files. (although it doesn't let you use a `Split` method) — smartcaveman, Nov 13 '11 at 14:19

score 0 · Answer 4 · answered Nov 13 '11 at 14:54

using System;
using System.Text;
using Microsoft.VisualBasic.FileIO; //Microsoft.VisualBasic.dll
using System.IO;

public class Sample {
    static void Main(){
        string str = "ZBEE10364,\"Cobler, CHARLOTTE J\",Whiskey,,Brandy,0:00:00,20110912,CHECK,2918,117.33,1,117.33,0,EDM0,Yu789";
        string[] strArr = str.Split(',');
        var reader = new StringReader(str);
        using(var csvReader = new TextFieldParser(reader)){
            csvReader.SetDelimiters(new string[] {","});
            csvReader.HasFieldsEnclosedInQuotes = true;
            strArr = csvReader.ReadFields();
        }

        //check print
        foreach(var item in strArr){
            Console.WriteLine("\"{0}\"",item);
        }
    }
}

RESULT

"ZBEE10364"
"Cobler, CHARLOTTE J"
"Whiskey"
""
"Brandy"
"0:00:00"
"20110912"
"CHECK"
"2918"
"117.33"
"1"
"117.33"
"0"
"EDM0"
"Yu789"

Splitting text based on comma

4 Answers4

Linked