I have the following comma-separated string that I need to split. The problem is that some of the content is within quotes and contains commas that shouldn't be used in the split.
String:
111,222,"33,44,55",666,"77,88","99" I want the output:
111 222 33,44,55 666 77,88 99 I have tried this:
(?:,?)((?<=")[^"]+(?=")|[^",]+) But it reads the comma between "77,88","99" as a hit and I get the following output:
111 222 33,44,55 666 77,88 , 99 Depending on your needs you may not be able to use a csv parser, and may in fact want to re-invent the wheel!!
You can do so with some simple regex
(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*) This will do the following:
(?:^|,) = Match expression "Beginning of line or string ,"
(\"(?:[^\"]+|\"\")*\"|[^,]*) = A numbered capture group, this will select between 2 alternatives:
This should give you the output you are looking for.
Example code in C#
static Regex csvSplit = new Regex("(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)", RegexOptions.Compiled); public static string[] SplitCSV(string input) { List<string> list = new List<string>(); string curr = null; foreach (Match match in csvSplit.Matches(input)) { curr = match.Value; if (0 == curr.Length) { list.Add(""); } list.Add(curr.TrimStart(',')); } return list.ToArray(); } private void button1_Click(object sender, RoutedEventArgs e) { Console.WriteLine(SplitCSV("111,222,\"33,44,55\",666,\"77,88\",\"99\"")); } Warning As per @MrE's comment - if a rogue new line character appears in a badly formed csv file and you end up with an uneven ("string) you'll get catastrophic backtracking (https://www.regular-expressions.info/catastrophic.html) in your regex and your system will likely crash (like our production system did). Can easily be replicated in Visual Studio and as I've discovered will crash it. A simple try/catch will not trap this issue either.
You should use:
(?:^|,)(\"(?:[^\"])*\"|[^,]*) instead
whatever,"""1,2,3,4,6(31/01/14)11(5) ,12 (MINIMUM 4 STAR WELS, TAPS , SHOWER & WC'S ),13,14,15,A""",another column as it gets split into whatever, "","1,2,3,4,6(31/01/14)11(5) ,12 (MINIMUM 4 STAR WELS, TAPS , SHOWER & WC'S ),13,14,15,A","",another column. Any thoughts for an update to the Regex?
(?:^|,)(\"(?:[^\"])*\"|[^,]*) covers it without this issue, and is simpler.Fast and easy:
public static string[] SplitCsv(string line) { List<string> result = new List<string>(); StringBuilder currentStr = new StringBuilder(""); bool inQuotes = false; for (int i = 0; i < line.Length; i++) // For each character { if (line[i] == '\"') // Quotes are closing or opening inQuotes = !inQuotes; else if (line[i] == ',') // Comma { if (!inQuotes) // If not in quotes, end of current string, add it to result { result.Add(currentStr.ToString()); currentStr.Clear(); } else currentStr.Append(line[i]); // If in quotes, just add it } else // Add any other character to current string currentStr.Append(line[i]); } result.Add(currentStr.ToString()); return result.ToArray(); // Return array of all strings } With this string as input :
111,222,"33,44,55",666,"77,88","99" It will return :
111 222 33,44,55 666 77,88 99 
i really like jimplode's answer, but I think a version with yield return is a little bit more useful, so here it is:
public IEnumerable<string> SplitCSV(string input) { Regex csvSplit = new Regex("(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)", RegexOptions.Compiled); foreach (Match match in csvSplit.Matches(input)) { yield return match.Value.TrimStart(','); } } Maybe it's even more useful to have it like an extension method:
public static class StringHelper { public static IEnumerable<string> SplitCSV(this string input) { Regex csvSplit = new Regex("(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)", RegexOptions.Compiled); foreach (Match match in csvSplit.Matches(input)) { yield return match.Value.TrimStart(','); } } } This regular expression works without the need to loop through values and TrimStart(','), like in the accepted answer:
((?<=\")[^\"]*(?=\"(,|$)+)|(?<=,|^)[^,\"]*(?=,|$)) Here is the implementation in C#:
string values = "111,222,\"33,44,55\",666,\"77,88\",\"99\""; MatchCollection matches = new Regex("((?<=\")[^\"]*(?=\"(,|$)+)|(?<=,|^)[^,\"]*(?=,|$))").Matches(values); foreach (var match in matches) { Console.WriteLine(match); } Outputs
111 222 33,44,55 666 77,88 99 
whatever,"""1,2,3,4,6(31/01/14)11(5) ,12 (MINIMUM 4 STAR WELS, TAPS , SHOWER & WC'S ),13,14,15,A""",another column as it gets split into whatever, "","1,2,3,4,6(31/01/14)11(5) ,12 (MINIMUM 4 STAR WELS, TAPS , SHOWER & WC'S ),13,14,15,A","",another column. Any thoughts for an update to the Regex?
None of these answers work when the string has a comma inside quotes, as in "value, 1", or escaped double-quotes, as in "value ""1""", which are valid CSV that should be parsed as value, 1 and value "1", respectively.
This will also work with the tab-delimited format if you pass in a tab instead of a comma as your delimiter.
public static IEnumerable<string> SplitRow(string row, char delimiter = ',') { var currentString = new StringBuilder(); var inQuotes = false; var quoteIsEscaped = false; //Store when a quote has been escaped. row = string.Format("{0}{1}", row, delimiter); //We add new cells at the delimiter, so append one for the parser. foreach (var character in row.Select((val, index) => new {val, index})) { if (character.val == delimiter) //We hit a delimiter character... { if (!inQuotes) //Are we inside quotes? If not, we've hit the end of a cell value. { Console.WriteLine(currentString); yield return currentString.ToString(); currentString.Clear(); } else { currentString.Append(character.val); } } else { if (character.val != ' ') { if(character.val == '"') //If we've hit a quote character... { if(character.val == '\"' && inQuotes) //Does it appear to be a closing quote? { if (row[character.index + 1] == character.val) //If the character afterwards is also a quote, this is to escape that (not a closing quote). { quoteIsEscaped = true; //Flag that we are escaped for the next character. Don't add the escaping quote. } else if (quoteIsEscaped) { quoteIsEscaped = false; //This is an escaped quote. Add it and revert quoteIsEscaped to false. currentString.Append(character.val); } else { inQuotes = false; } } else { if (!inQuotes) { inQuotes = true; } else { currentString.Append(character.val); //...It's a quote inside a quote. } } } else { currentString.Append(character.val); } } else { if (!string.IsNullOrWhiteSpace(currentString.ToString())) //Append only if not new cell { currentString.Append(character.val); } } } } } With minor updates to the function provided by "Chad Hedgcock".
Updates are on:
Line 26: character.val == '\"' - This can never be true due to the check made on Line 24. i.e. character.val == '"'
Line 28: if (row[character.index + 1] == character.val) added !quoteIsEscaped to escape 3 consecutive quotes.
public static IEnumerable<string> SplitRow(string row, char delimiter = ',') { var currentString = new StringBuilder(); var inQuotes = false; var quoteIsEscaped = false; //Store when a quote has been escaped. row = string.Format("{0}{1}", row, delimiter); //We add new cells at the delimiter, so append one for the parser. foreach (var character in row.Select((val, index) => new {val, index})) { if (character.val == delimiter) //We hit a delimiter character... { if (!inQuotes) //Are we inside quotes? If not, we've hit the end of a cell value. { //Console.WriteLine(currentString); yield return currentString.ToString(); currentString.Clear(); } else { currentString.Append(character.val); } } else { if (character.val != ' ') { if(character.val == '"') //If we've hit a quote character... { if(character.val == '"' && inQuotes) //Does it appear to be a closing quote? { if (row[character.index + 1] == character.val && !quoteIsEscaped) //If the character afterwards is also a quote, this is to escape that (not a closing quote). { quoteIsEscaped = true; //Flag that we are escaped for the next character. Don't add the escaping quote. } else if (quoteIsEscaped) { quoteIsEscaped = false; //This is an escaped quote. Add it and revert quoteIsEscaped to false. currentString.Append(character.val); } else { inQuotes = false; } } else { if (!inQuotes) { inQuotes = true; } else { currentString.Append(character.val); //...It's a quote inside a quote. } } } else { currentString.Append(character.val); } } else { if (!string.IsNullOrWhiteSpace(currentString.ToString())) //Append only if not new cell { currentString.Append(character.val); } } } } }
For Jay's answer, if you use a 2nd boolean then you can have nested double-quotes inside single-quotes and vice-versa.
private string[] splitString(string stringToSplit) { char[] characters = stringToSplit.ToCharArray(); List<string> returnValueList = new List<string>(); string tempString = ""; bool blockUntilEndQuote = false; bool blockUntilEndQuote2 = false; int characterCount = 0; foreach (char character in characters) { characterCount = characterCount + 1; if (character == '"' && !blockUntilEndQuote2) { if (blockUntilEndQuote == false) { blockUntilEndQuote = true; } else if (blockUntilEndQuote == true) { blockUntilEndQuote = false; } } if (character == '\'' && !blockUntilEndQuote) { if (blockUntilEndQuote2 == false) { blockUntilEndQuote2 = true; } else if (blockUntilEndQuote2 == true) { blockUntilEndQuote2 = false; } } if (character != ',') { tempString = tempString + character; } else if (character == ',' && (blockUntilEndQuote == true || blockUntilEndQuote2 == true)) { tempString = tempString + character; } else { returnValueList.Add(tempString); tempString = ""; } if (characterCount == characters.Length) { returnValueList.Add(tempString); tempString = ""; } } string[] returnValue = returnValueList.ToArray(); return returnValue; } The following regular expression will already cover many complex cases:
public static Regex regexCSVSplit = new Regex(@"(?x) ( (?<FULL> ( (?<QUODAT> (?<QUO>[""'])(?<DAT>(([^,;\t])|(?<!\k<QUO>[ \r\n]*)[,;\t])*)\k<QUO>) | (?<QUODAT> (?<DAT> [^""',;\t]* )) ) [ \r\n]*([,;\t][ \r\n]*|$) ) )", RegexOptions.Compiled); With the example from the question: (([^,;\t])|(?[+\r\n]*)[,;\t])*)\k
It can process empty, simple and complex items too: 
This is how to feed the result into an array:
var data = regexCSVSplit.Matches(line_to_process).Cast<Match>(). Select(x => x.Groups["DAT"].Value).ToArray(); The main rule here is that every item may contain anything but the <quotation mark><whitespace><separators> sequence AND each item shall being and end with the same <quotation mark>.
<quotation mark>: <">, <'><separators>: <,>, <;>, <tab><whitespace>: <space>, <nl>, <cr>Currently I use the following regex:
public static Regex regexCSVSplit = new Regex(@"(?x:( (?<FULL> (^|[,;\t\r\n])\s* ( (?<QUODAT> (?<QUO>[""'])(?<DAT>([^,;\t\r\n]|(?<!\k<QUO>\s*)[,;\t\r\n])*)\k<QUO>) | (?<QUODAT> (?<DAT> [^""',;\s\r\n]* )) ) (?=\s*([,;\t\r\n]|$)) ) | (?<FULL> (^|[\s\t\r\n]) ( (?<QUODAT> (?<QUO>[""'])(?<DAT> [^""',;\s\t\r\n]* )\k<QUO>) | (?<QUODAT> (?<DAT> [^""',;\s\t\r\n]* )) ) (?=[,;\s\t\r\n]|$) ) ))", RegexOptions.Compiled); This solution can handle pretty chaotic cases too like below: 
This is how to feed the result into an array:
var data = regexCSVSplit.Matches(line_to_process).Cast<Match>(). Select(x => x.Groups["DAT"].Value).ToArray(); Note: The regular expression contains two set of <FULL> block and each of them contains two <QUODAT> block separated by "or" (|). Depending on your task you may only need one of them.
Note: this regular expression gives us one string array, and works on single line with or without <carrier return> and/or <line feed>.
Edit notes: I added some more explanation to make it easier to understand and replaces the text "CO" with "QUO".
Try this:
string s = @"111,222,""33,44,55"",666,""77,88"",""99"""; List<string> result = new List<string>(); var splitted = s.Split('"').ToList<string>(); splitted.RemoveAll(x => x == ","); foreach (var it in splitted) { if (it.StartsWith(",") || it.EndsWith(",")) { var tmp = it.TrimEnd(',').TrimStart(','); result.AddRange(tmp.Split(',')); } else { if(!string.IsNullOrEmpty(it)) result.Add(it); } } //Results: foreach (var it in result) { Console.WriteLine(it); } 
I know I'm a bit late to this, but for searches, here is how I did what you are asking about in C sharp
private string[] splitString(string stringToSplit) { char[] characters = stringToSplit.ToCharArray(); List<string> returnValueList = new List<string>(); string tempString = ""; bool blockUntilEndQuote = false; int characterCount = 0; foreach (char character in characters) { characterCount = characterCount + 1; if (character == '"') { if (blockUntilEndQuote == false) { blockUntilEndQuote = true; } else if (blockUntilEndQuote == true) { blockUntilEndQuote = false; } } if (character != ',') { tempString = tempString + character; } else if (character == ',' && blockUntilEndQuote == true) { tempString = tempString + character; } else { returnValueList.Add(tempString); tempString = ""; } if (characterCount == characters.Length) { returnValueList.Add(tempString); tempString = ""; } } string[] returnValue = returnValueList.ToArray(); return returnValue; } Don't reinvent a CSV parser, try FileHelpers.
I needed something a little more robust, so I took from here and created this... This solution is a little less elegant and a little more verbose, but in my testing (with a 1,000,000 row sample), I found this to be 2 to 3 times faster. Plus it handles non-escaped, embedded quotes. I used string delimiter and qualifiers instead of chars because of the requirements of my solution. I found it more difficult than I expected to find a good, generic CSV parser so I hope this parsing algorithm can help someone.
public static string[] SplitRow(string record, string delimiter, string qualifier, bool trimData) { // In-Line for example, but I implemented as string extender in production code Func <string, int, int> IndexOfNextNonWhiteSpaceChar = delegate (string source, int startIndex) { if (startIndex >= 0) { if (source != null) { for (int i = startIndex; i < source.Length; i++) { if (!char.IsWhiteSpace(source[i])) { return i; } } } } return -1; }; var results = new List<string>(); var result = new StringBuilder(); var inQualifier = false; var inField = false; // We add new columns at the delimiter, so append one for the parser. var row = $"{record}{delimiter}"; for (var idx = 0; idx < row.Length; idx++) { // A delimiter character... if (row[idx]== delimiter[0]) { // Are we inside qualifier? If not, we've hit the end of a column value. if (!inQualifier) { results.Add(trimData ? result.ToString().Trim() : result.ToString()); result.Clear(); inField = false; } else { result.Append(row[idx]); } } // NOT a delimiter character... else { // ...Not a space character if (row[idx] != ' ') { // A qualifier character... if (row[idx] == qualifier[0]) { // Qualifier is closing qualifier... if (inQualifier && row[IndexOfNextNonWhiteSpaceChar(row, idx + 1)] == delimiter[0]) { inQualifier = false; continue; } else { // ...Qualifier is opening qualifier if (!inQualifier) { inQualifier = true; } // ...It's a qualifier inside a qualifier. else { inField = true; result.Append(row[idx]); } } } // Not a qualifier character... else { result.Append(row[idx]); inField = true; } } // ...A space character else { if (inQualifier || inField) { result.Append(row[idx]); } } } } return results.ToArray<string>(); } Some test code:
//var input = "111,222,\"33,44,55\",666,\"77,88\",\"99\""; var input = "111, 222, \"99\",\"33,44,55\" , \"666 \"mark of a man\"\", \" spaces \"77,88\" \""; Console.WriteLine("Split with trim"); Console.WriteLine("---------------"); var result = SplitRow(input, ",", "\"", true); foreach (var r in result) { Console.WriteLine(r); } Console.WriteLine(""); // Split 2 Console.WriteLine("Split with no trim"); Console.WriteLine("------------------"); var result2 = SplitRow(input, ",", "\"", false); foreach (var r in result2) { Console.WriteLine(r); } Console.WriteLine(""); // Time Trial 1 Console.WriteLine("Experimental Process (1,000,000) iterations"); Console.WriteLine("-------------------------------------------"); watch = Stopwatch.StartNew(); for (var i = 0; i < 1000000; i++) { var x1 = SplitRow(input, ",", "\"", false); } watch.Stop(); elapsedMs = watch.ElapsedMilliseconds; Console.WriteLine($"Total Process Time: {string.Format("{0:0.###}", elapsedMs / 1000.0)} Seconds"); Console.WriteLine(""); Results
Split with trim --------------- 111 222 99 33,44,55 666 "mark of a man" spaces "77,88" Split with no trim ------------------ 111 222 99 33,44,55 666 "mark of a man" spaces "77,88" Original Process (1,000,000) iterations ------------------------------- Total Process Time: 7.538 Seconds Experimental Process (1,000,000) iterations -------------------------------------------- Total Process Time: 3.363 Seconds 
I once had to do something similar and in the end I got stuck with Regular Expressions. The inability for Regex to have state makes it pretty tricky - I just ended up writing a simple little parser.
If you're doing CSV parsing you should just stick to using a CSV parser - don't reinvent the wheel.
Here is my fastest implementation based upon string raw pointer manipulation:
string[] FastSplit(string sText, char? cSeparator = null, char? cQuotes = null) { string[] oTokens; if (null == cSeparator) { cSeparator = DEFAULT_PARSEFIELDS_SEPARATOR; } if (null == cQuotes) { cQuotes = DEFAULT_PARSEFIELDS_QUOTE; } unsafe { fixed (char* lpText = sText) { #region Fast array estimatation char* lpCurrent = lpText; int nEstimatedSize = 0; while (0 != *lpCurrent) { if (cSeparator == *lpCurrent) { nEstimatedSize++; } lpCurrent++; } nEstimatedSize++; // Add EOL char(s) string[] oEstimatedTokens = new string[nEstimatedSize]; #endregion #region Parsing char[] oBuffer = new char[sText.Length]; int nIndex = 0; int nTokens = 0; lpCurrent = lpText; while (0 != *lpCurrent) { if (cQuotes == *lpCurrent) { // Quotes parsing lpCurrent++; // Skip quote nIndex = 0; // Reset buffer while ( (0 != *lpCurrent) && (cQuotes != *lpCurrent) ) { oBuffer[nIndex] = *lpCurrent; // Store char lpCurrent++; // Move source cursor nIndex++; // Move target cursor } } else if (cSeparator == *lpCurrent) { // Separator char parsing oEstimatedTokens[nTokens++] = new string(oBuffer, 0, nIndex); // Store token nIndex = 0; // Skip separator and Reset buffer } else { // Content parsing oBuffer[nIndex] = *lpCurrent; // Store char nIndex++; // Move target cursor } lpCurrent++; // Move source cursor } // Recover pending buffer if (nIndex > 0) { // Store token oEstimatedTokens[nTokens++] = new string(oBuffer, 0, nIndex); } // Build final tokens list if (nTokens == nEstimatedSize) { oTokens = oEstimatedTokens; } else { oTokens = new string[nTokens]; Array.Copy(oEstimatedTokens, 0, oTokens, 0, nTokens); } #endregion } } // Epilogue return oTokens; } Try this
private string[] GetCommaSeperatedWords(string sep, string line) { List<string> list = new List<string>(); StringBuilder word = new StringBuilder(); int doubleQuoteCount = 0; for (int i = 0; i < line.Length; i++) { string chr = line[i].ToString(); if (chr == "\"") { if (doubleQuoteCount == 0) doubleQuoteCount++; else doubleQuoteCount--; continue; } if (chr == sep && doubleQuoteCount == 0) { list.Add(word.ToString()); word = new StringBuilder(); continue; } word.Append(chr); } list.Add(word.ToString()); return list.ToArray(); } This is Chad's answer rewritten with state based logic. His answered failed for me when it came across """BRAD""" as a field. That should return "BRAD" but it just ate up all the remaining fields. When I tried to debug it I just ended up rewriting it as state based logic:
enum SplitState { s_begin, s_infield, s_inquotefield, s_foundquoteinfield }; public static IEnumerable<string> SplitRow(string row, char delimiter = ',') { var currentString = new StringBuilder(); SplitState state = SplitState.s_begin; row = string.Format("{0}{1}", row, delimiter); //We add new cells at the delimiter, so append one for the parser. foreach (var character in row.Select((val, index) => new { val, index })) { //Console.WriteLine("character = " + character.val + " state = " + state); switch (state) { case SplitState.s_begin: if (character.val == delimiter) { /* empty field */ yield return currentString.ToString(); currentString.Clear(); } else if (character.val == '"') { state = SplitState.s_inquotefield; } else { currentString.Append(character.val); state = SplitState.s_infield; } break; case SplitState.s_infield: if (character.val == delimiter) { /* field with data */ yield return currentString.ToString(); state = SplitState.s_begin; currentString.Clear(); } else { currentString.Append(character.val); } break; case SplitState.s_inquotefield: if (character.val == '"') { // could be end of field, or escaped quote. state = SplitState.s_foundquoteinfield; } else { currentString.Append(character.val); } break; case SplitState.s_foundquoteinfield: if (character.val == '"') { // found escaped quote. currentString.Append(character.val); state = SplitState.s_inquotefield; } else if (character.val == delimiter) { // must have been last quote so we must find delimiter yield return currentString.ToString(); state = SplitState.s_begin; currentString.Clear(); } else { throw new Exception("Quoted field not terminated."); } break; default: throw new Exception("unknown state:" + state); } } //Console.WriteLine("currentstring = " + currentString.ToString()); } This is a lot more lines of code than the other solutions, but it is easy to modify to add edge cases.
