Geek Answers Handbook

Write to csv in MVC with data containing comma

One of my actions is outputting a csv file. The problem is that the field contains a comma and then splits datarow into two fields. I tried using "\" "to enclose each line in double quotes, but this does not work. Can someone lead 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: the object reference is not installed in the object instance.

+4
source share
2 answers

There is no “official” CSV format, although the most common one is described in RFC 4180 (http://tools.ietf.org/html/rfc4180). According to this RFC (Section 6), fields containing commas must be enclosed in double quotation marks.

In @Axam's observation below, RFC 4180 does not allow extra spaces around commas. Change the format string to:

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

CSV files use different escaping than C #. If you want to include a comma in the CSV file, you need to remove the entire cell in double quotes. It should be noted that this creates a secondary problem of placing double quotes in the text! This problem results in doubling your double quotes.

IE this csv file will be displayed as a single line with three 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("\"", "\"\""));

Of particular note is that my double quotation mark denoting a cell IMMEDIATELY follows a comma, whereas in your example you have extra space. You will need to remove this extra space in order to properly avoid CSV data.

+2
source

More articles:


All Articles