how to use comma in csv columns

Question

Possible Duplicate:
Dealing with commas in a CSV file

We are exporting a bulk data into a csv file for one of our projects. So in this case we have to export values like a,b,c,d which all have to be remain in one column. But the comma will separate them to different columns.

Like if we export some values entered in textarea or editor which contains character like \r\n will be exported as separate rows in csv. How can i solve this problem??

score 73 · Answer 1 · edited Aug 18 '21 at 13:30

73

       // CSV rules: http://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules
        // From the rules:
        // 1. if the data has quote, escape the quote in the data
        // 2. if the data contains the delimiter (in our case ','), double-quote it
        // 3. if the data contains the new-line, double-quote it.

        if (data.Contains("\""))
        {
            data = data.Replace("\"", "\"\"");
            data = String.Format("\"{0}\"", data);
        }
        else if (data.Contains(",") || data.Contains(System.Environment.NewLine))
        {
            data = String.Format("\"{0}\"", data);
        }

data could be individual items from db or a property value of a type.

edited Aug 18 '21 at 13:30

The Lemon

1,211
15
26

answered Apr 21 '11 at 06:43

Vijay Sirigiri

4,653
29
31

4

I think this item should be: `if (data.Contains("\"")) { data = String.Format("\"{0}\"", data.Replace("\"", "\"\"")); }` as an escaped quote must be in a string that is itself within quotes. – DelftRed Nov 05 '14 at 06:48
With excel 2013, need open file with notepad++, add **sep=,** at the first line. – Phan Đức Bình Dec 27 '16 at 10:52
3

You may want to consider combining the check for a comma and the check for a newline so that you don't end up with two sets of quotes surrounding the string when both conditions exist. – Scott Aug 09 '17 at 18:51
this is really great answer, but it is much better to use .IndexOf() method instead of Contains - you will gain 10x performance with that. – dyatchenko Oct 10 '18 at 20:19
1

well, if a string has commas and new lines at the same time this code will not encode the string properly – dyatchenko Oct 11 '18 at 00:13

score 8 · Accepted Answer · edited Oct 07 '21 at 05:51

8

The most common way to handle this is to use "" around the field, but depending on who is consuming your files it can be handled in a number of ways. You can delimit the commas, you can change the commas to a special value or use a different delimiter, but the most command

edited Oct 07 '21 at 05:51

Community

1
1

answered Apr 21 '11 at 06:32

rerun

25,014
6
48
78

how to use comma in csv columns

2 Answers2

Linked