write to csv in MVC with data containing a comma

Question

One of my action outputs a csv file. The problem is if a field contains comma, then it splits the datarow into two fields. I tried using "\"" to include each row in double quotes but this is not working. Could someone guide me in the right direction?

UPDATE

var sw = new StringWriter();
            sw.WriteLine(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13}", "First Name","Last Name","Address1","Address2","City","State","Postal Code","Country","Dealer ID","Dealer Name","Survey Source","Amount","Email","Survey Code"));
            sw.WriteLine(String.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\",\"{11}\",\"{12}\",\"{13}\"",  model.SurveyWinnerDetails.Select(p => p.FirstName).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.LastName).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.Address1).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.Address2).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.City).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.State).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.PostalCode).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.CountryCode).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.DealerID).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.DealerName).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.SurveySource).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.Amount).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.Email).First().Replace("\"", "\"\""),
                        model.SurveyWinnerDetails.Select(p => p.SurveyCode).First().Replace("\"", "\"\"")));
            return File(new System.Text.UTF8Encoding().GetBytes(sw.ToString()), "text/csv", "SurveyWinner.csv");

When i run this now, I get an error: Object reference not set to the instance of an object.

How does the CSV processor handle this? This is something that should be answered by your requirements. — jrummell, Jun 12 '12 at 17:03
[There's another answer on SO that covers writing out CSVs](http://stackoverflow.com/questions/1684667/writing-a-csv-file-in-net) when the columns contain commas (and quotes). — 48klocs, Jun 12 '12 at 17:19
You probably shouldn't modify an existing question in an attempt to get answers for a different problem. In your specific case one of the following is true: model is null, an item inside model.SurveyWinnerDetails is null, or a one of the properties optained via p.Something is null. — Chris Kerekes, Jun 15 '12 at 18:23
If the later is the case try replacing 'p.Something' with 'p.Something ?? ""'. — Chris Kerekes, Jun 15 '12 at 18:30

score 2 · Answer 1 · answered Jun 12 '12 at 17:22

CSV files use different escaping than C#. If you want to include a comma ina CSV file you need to escape the entire cell in double quotes. It should be noted that this creates the secondary problem of putting double quotation marks in your text! This problem is circumvented by doubling up your double quotation marks.

IE. this csv file will display as a single row with 3 columns:

This is a single cell,"This,is,a,single,cell", "This,is""a,single""cell"

Try something like this:

sw.WriteLine(string.Format("\"{0}\",\"{0}\"", model.MyStringData1.Replace("\"", "\"\""));

Take special note that my double quotation mark that denotes a cell IMMEDIATELY follows the comma whereas in your example you have an extra space. You will need to remove this extra space in order to properly escape your CSV data.

Ethan Brown · Accepted Answer · 2012-06-12T17:28:50.100

1

There's no one "official" CSV format, though the most common is outlined in RFC 4180 (http://tools.ietf.org/html/rfc4180). According to that RFC (section 6), fields containing commas should be enclosed in double quotes.

Per @Axam's observation below, RFC 4180 does not allow for extra whitespace around the commas. Change your format string to:

"{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13}"

edited Jun 12 '12 at 17:28

answered Jun 12 '12 at 17:12

Ethan Brown

26,892
4
80
92

1

The problem is the white space after his cell delimiting commas and the double quotation mark. – Chris Kerekes Jun 12 '12 at 17:24
Right you are, Axam...I am updating my answer to reflect this. – Ethan Brown Jun 12 '12 at 17:27
Thanks Ethan. Your solution fixed it. I added "\"" + model.SurveyWinnerDetails.Select(p => p.DealerName).First() + "\"" to take care of the data containing comma. – bladerunner Jun 12 '12 at 18:33

write to csv in MVC with data containing a comma

2 Answers2