CSV change delimiter

Question

i'm reading a CSV file and changing the delimiter from a "," to a "|". However i've noticed in my data (which I have no control over) that in certain cases I have some data that does not want to follow this rule and it contains quoted data with a comma in it. I'm wondering how best to not replace these exceptions?

For example:

ABSON TE,Wick Lane,"Abson, Pucklechurch",Bristol,Avon,ENGLAND,BS16 9SD,37030,17563,BS0001A1,,

Should be changed to:

ABSON TE|Wick Lane|"Abson, Pucklechurch"|Bristol|Avon|ENGLAND|BS16 9SD|37030|17563|BS0001A1||

The code to read and replace the CSV file is this:

var contents = File.ReadAllText(filePath).Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries).ToArray();
var formattedContents = contents.Select(line => line.Replace(',', '|'));

don't use a string operation, then. parse the CSV back into separate data fields, then build a new csv from scratch. — Marc B, Aug 12 '14 at 16:14
If you really want to use string operations, see the regex posted in [this answer](http://stackoverflow.com/a/11457952/1524136) — dehrg, Aug 12 '14 at 16:15
possible duplicate of [Split a string by commas but ignore commas within double-quotes using Javascript](http://stackoverflow.com/questions/11456850/split-a-string-by-commas-but-ignore-commas-within-double-quotes-using-javascript) — dehrg, Aug 12 '14 at 16:17
Can the double-quoted fields contain double-quotes in them? If not, then it would be kind of easy to follow `Marc B`'s suggestion. If so, then it wouldn't be much harder to do, but you should still do it. — Jashaszun, Aug 12 '14 at 16:34

score 2 · Accepted Answer · answered Aug 14 '14 at 13:02

For anyone else struggling with this, I ended up using the built in .net csv parser. See here for more details and example: http://coding.abel.nu/2012/06/built-in-net-csv-parser/

My specific code:

 // Create new parser object and setup parameters
var parser = new TextFieldParser(new StringReader(File.ReadAllText(filePath)))
{
    HasFieldsEnclosedInQuotes = true,
    Delimiters = new string[] { "," },
    TrimWhiteSpace = true
};

var csvSplitList = new List<string>();

// Reads all fields on the current line of the CSV file and returns as a string array
// Joins each field together with new delimiter "|"
while (!parser.EndOfData)
{
    csvSplitList.Add(String.Join("|", parser.ReadFields()));
}

// Newline characters added to each line and flattens List<string> into single string
var formattedCsvToSave = String.Join(Environment.NewLine, csvSplitList.Select(x => x));

// Write single string to file
File.WriteAllText(filePathFormatted, formattedCsvToSave);
parser.Close();

CSV change delimiter

1 Answers1

Linked