locked
How to Escape Comma while exporting data to CSV file RRS feed

  • Question

  • User-1824994464 posted

    Hi,

     I am exporting the sql Result set to a CSV file.  If i have comma between the words, it is pushing the word after comma to next column. how to escape comma in CSV file so that the whole word stays in the same column. For Example if i have  "604, Beverlyblvd",  Beverlyblvd is going to next column.  I want "604,Beverlyblvd" to be in the same column. Please see my code below. Can you guys modify my code to escape the comma. Appreciate your help.

    Thanks.

                                using (var dr = cmd.ExecuteReader())
                                {
                                    
                                    var columns = new List<string>();
                                    for (int i = 0; i < dr.FieldCount; i++)
                                    {
                                        columns.Add(dr.GetName(i));
                                    }
                                    sbData.Append(string.Join(",", columns.ToArray()));
    
                                    while (dr.Read())
                                    {
                                        columns = new List<string>();
                                        for (int i = 0; i < dr.FieldCount; i++)
                                        {
                                            object val = dr.GetValue(i);
                                            columns.Add(val == null ? "" : val.ToString());
                                        }
                                        sbData.Append(Environment.NewLine);
                                        sbData.Append(string.Join(",", columns.ToArray()));
    
                                    }
    
                                    var firstResultSetCSV = sbData.ToString();
                                    var fileNameToExport = spRow.FileName1;
    
                                    outputs.Add(new KeyValuePair<string, string>(fileNameToExport, firstResultSetCSV));
    
                                }

    Tuesday, January 13, 2015 11:52 PM

Answers

  • User-320102142 posted

    Change the code as shown below

    columns.Add(val ==null ? "" : AddEscapeSequenceInCsvField(val.ToString()));

    Add a new method:

    private string AddEscapeSequenceInCsvField(string ValueToEscape)        {

    if (ValueToEscape.Contains(","))

    {

    //*you might need to handle if it contains a quote*

    //Wrap with quotes.

    return "\"" + ValueToEscape + "\"";

    }

    else

    {

    //no commas

    return ValueToEscape;

    }

    }

    Hope this will help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 14, 2015 12:48 AM

All replies

  • User-320102142 posted

    Hi vkasp,

    As far as I understand, you need to add an escape sequence in the word where you have ,.

    Eg: 

    604,Beverlyblvd to be "604,Beverlyblvd"

    In case the word already has double quote, then you may need to add escape sequence for double quote as well.

    Eg: 

    "604,Beverlyblvd" to be """604,Beverlyblvd"""

    Note: Double quote act as an escape sequence.

    Wednesday, January 14, 2015 12:29 AM
  • User-1824994464 posted

    Hi,

    I just showed example. this data is coming from result set. please see my code above.

    Wednesday, January 14, 2015 12:33 AM
  • User-320102142 posted

    Change the code as shown below

    columns.Add(val ==null ? "" : AddEscapeSequenceInCsvField(val.ToString()));

    Add a new method:

    private string AddEscapeSequenceInCsvField(string ValueToEscape)        {

    if (ValueToEscape.Contains(","))

    {

    //*you might need to handle if it contains a quote*

    //Wrap with quotes.

    return "\"" + ValueToEscape + "\"";

    }

    else

    {

    //no commas

    return ValueToEscape;

    }

    }

    Hope this will help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 14, 2015 12:48 AM
  • User-1824994464 posted

    Thanks for your help.!

    Thursday, January 15, 2015 9:28 PM