CODEWITHSUNDEEP
c#asp.net-mvc-3
bladerunner
bladerunner

Reputation: 607

write to csv in MVC with data containing a comma

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.

Upvotes: 4

Views: 2320

Answers (2)

Ethan Brown
Ethan Brown

Reputation: 27282

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}"

Upvotes: 1

Chris Kerekes
Chris Kerekes

Reputation: 1116

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.

Upvotes: 2

Related Questions