escaping tricky string to CSV format

Question

I have to create a CSV file from webservice output and the CSV file uses quoted strings with comma separator. I cannot change the format...

So if I have a string it becomes a "string"... If the value has quotes already they are replaced with double quotes. For example a str"ing becomes "str""ing"...

However, lately my import has been failing because of the following

• original input string is: "","word1,word2,..."
• every single quote is replaced by double resulting in: """",""word1,word2,...""
• then its prefixed and suffixed with quote before written to CVS file: """"",""word1,word2,..."""

As you can see the final result is this:

""""",""word1,word2,..."""

which breaks my import (is sees it as another field)... I think the issue is appereance of "," in the original input string.

Is there a CVS escape sequence for this scenario?

Update

The reason why above breaks is due to BCP mapping file (BCP utility is used to load CSV file into SQL db) which has terminator defined as "," . So instead of seeing 1 field it sees 2...But I cannot change the mapping file...

Answer 1

I use this code and it has always worked:

/// <summary>
/// Turn a string into a CSV cell output
/// </summary>
/// <param name="str">String to output</param>
/// <returns>The CSV cell formatted string</returns>
public static string StringToCSVCell(string str)
{
    bool mustQuote = (str.Contains(",") || str.Contains("\"") || str.Contains("\r") || str.Contains("\n"));
    if (mustQuote)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("\"");
        foreach (char nextChar in str)
        {
            sb.Append(nextChar);
            if (nextChar == '"')
                sb.Append("\"");
        }
        sb.Append("\"");
        return sb.ToString();
    }

    return str;
}