Nested quotes and commas don't seem to work correctly

[duanbailey]

Hi there!

I'm trying to read a csv file with nested quotes and commas, but it doesn't parse it as I expect.

So my csv looks like this:

customer_name,id
""The Literal", My Fake Company Limited",123456
My Other Fake Company, 98765

When I run this using

var config = new CsvConfiguration(CultureInfo.InvariantCulture)
{
	HasHeaderRecord = true,
	BadDataFound = null,
};

using (var streamReader = new StreamReader(_csvFile))
{
	using (var reader = new CsvReader(streamReader, config))
	{
		var records = reader.GetRecords<dynamic>().ToList()
	}
}

I get "The Literal" and "My Fake Comany Limited" as the first two columns, though I would expect to see "The Literal", My Fake Company Limited and 123456.

Any ideas?

[AltruCoder]

Your internal quotes need to be escaped with another double quote.

customer_name,id
"""The Literal"", My Fake Company Limited",123456
My Other Fake Company, 98765

[JoshClose]

Quotes wrap just the comma or the whole field?

[duanbailey]

Sorry. The whole field. So for example, it might look like:

""The Literal", My Fake Company Limited",123456

Edit: So to be clear, there are two fields here, "The Literal", My Fake Company Limited and 123456.

[AltruCoder]

Which spreadsheet program is your customer using? If I save an Excel sheet as CSV, the field "The Literal", My Fake Company Limited is converted to """The Literal"", My Fake Company Limited". It also works that way with Google Sheets. Open Office works that way if you select ISO-8859-1 for the output. If you select UTF-8, Open Office converts the inner quotes to “ and ”, which I don't like, but will still work in CsvHelper.

[AltruCoder]

I think you are going to have difficulty even preprocessing the data. I'm assuming the files you are getting will be more complicated than your example text. There would be a bit of logic involved to process the following text and I'm pretty sure there would be more than one way to split the text into the 4 columns no matter what logic you tried to give it.

customer_name,customer_branch,customer_division,id
""The Literal", My Fake,", Company Limited",Branch 1,","Division" has "A Literal",As Well",123456

[duanbailey]

@AltruCoder we went back and checked the original data and it was indeed triple quoted. So I think there was a mix up on our end somewhere. When we update to use version 25.0 of the libraries we do seem to be parsing correctly so thanks very much for your help!