Export to csv to ignore commas and line breaks in comments

Question

I have the below code that exports to csv fine, but just realised it doesn't handle commas or line breaks in the 'comments' field textbox. Is there a way to ignore commas and line breaks in a particular field or in all of them?

private void saveFileDialogTrack_FileOk(object sender, CancelEventArgs e)
    {
        StringBuilder str = new StringBuilder();
        foreach (DataRow dr in this.trackDataSet.Track)
        {
            foreach (object field in dr.ItemArray)
            {
                str.Append(field.ToString() + ",");
            }
            str.Replace(",", Environment.NewLine, str.Length - 1, 1);
        }
        string name = saveFileDialogTrack.FileName;
        try
        {
            System.IO.File.WriteAllText(name, str.ToString());
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        str = null;
    }

Sample csv (first few fields) Barbagallo,31,39,51.01,

        private void openFileDialogTrack_FileOk(object sender, CancelEventArgs e)
    {
        try
        {
            using (StreamReader SR = new StreamReader(openFileDialogTrack.FileName))
            {
                while (!SR.EndOfStream)
                {
                    var CSValues = SR.ReadLine().Split(',');

                    SqlCeConnection myConnection = new SqlCeConnection("Data Source = Track.sdf");
                    myConnection.Open();
                    String str = (@"INSERT INTO Track (Track, StartFinishLineNSDegrees, StartFinishLineNSMinutes,

......................

VALUES (@track, @SFNSD, @SFNSM,

......................

                        SqlCeCommand cmd = new SqlCeCommand(str, myConnection);
                    cmd.Parameters.AddWithValue("@track", CSValues[0] == string.Empty ? (object)DBNull.Value : CSValues[0]);
                    cmd.Parameters.AddWithValue("@SFNSD", CSValues[1] == string.Empty ? (object)DBNull.Value : CSValues[1]);
                    cmd.Parameters.AddWithValue("@SFNSM", CSValues[2] == string.Empty ? (object)DBNull.Value : CSValues[2]);

score 0 · Answer 1 · edited Oct 07 '21 at 06:31

0

According to "RFC4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files":

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

Just to be safe, and to make the coding easier, you could surround all of your fields in quotes.

Replace this:

str.Append(field.ToString() + ",");

With this:

str.Append(string.Format("\"{0}\",", field));

edited Oct 07 '21 at 06:31

Community

1
1

answered Aug 05 '14 at 02:45

Grant Winney

65,241
13
115
165

what would the import look like then? this is what I use to have... var CSValues = SR.ReadLine().Split(','); – Kringle Aug 05 '14 at 03:02
Both, the fisrt code exports to a csv but only commas separated with no double quotes. The second code is the import function but it only handles commas no double quotes. – Kringle Aug 05 '14 at 03:17
Yeah that's correct, it's just a way for users to share data and to back up their data. – Kringle Aug 05 '14 at 03:27
With your code above str.Append(string.Format("\"{0}\",", field)); does the streamreader care if the text breaks onto a new line if it's surrounded by double quotes? – Kringle Aug 05 '14 at 09:33

score 0 · Answer 2 · edited Jul 31 '20 at 15:48

I created a method to add the text in quote if needed, before adding the string to stringbuilder

private string HandelQuote(string text)
        {
            if (string.IsNullOrEmpty(text)) return string.Empty;

            var containsQuote = text.Contains('"');
            var containsComma = text.Contains(',');
            var addQuote = containsComma || containsQuote;

            if (addQuote)
            {
                text = text.Replace("\"", "\"\"");
            }

            return addQuote ? "\"" + text + "\"" : text;
        }

Export to csv to ignore commas and line breaks in comments

2 Answers2