Make string CSV compatible

Question

Is there an easier way to turn a string into a CSV compatible value, eg. escaping, quoting as necessary.

Currently I have this:

public static object ToCsv(object obj, CultureInfo cultureInfo, string delimiter)
{
    CsvConfiguration config = new(cultureInfo) { Encoding = Encoding.UTF8, Delimiter = delimiter, HasHeaderRecord = false, ShouldQuote = (_) => true };

    using var memoryStream = new MemoryStream();
    using var streamWriter = new StreamWriter(memoryStream);
    using var csvWriter = new CsvWriter(streamWriter, config);
    csvWriter.WriteField(obj);
    csvWriter.Flush();

    memoryStream.Position = 0;
    return Encoding.UTF8.GetString(memoryStream.ToArray());
}

Not only does this code seem like an overkill, I am also concerend about the performance.

The code is used in a copy&paste event where a DataGrid fires an event for each individual cell, and I need to parse each individual cell, depending on the amount of rows/columns the user has selected this piece of code could be called thousand of times. (for each cell)

CsvWriter and CsvConfiguration are part of the CsvHelper library: https://github.com/JoshClose/CsvHelper

Answer 1

This method is NOT designed to be used to build each field in a possible CSV file cell by cell. It is a once off to CSV parse a single value. It is a good implementation of this as it uses the same logic that a full file approach would use, but it would be a very poor implementation to use to write a whole file, or to use recursively for many fields in a file.

For the readers at home, if you need to use this library to write a file please consult the documentation: Writing a CV file

using (var writer = new StreamWriter("path\\to\\file.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(records);
}

To adequately judge/assess performance, it would be worth showing your code that is calling this method. As with the code above, to build a file you would setup the streams and config once for the file, then use WriteRecords which will iterate over your objects in the list and internally call csvWriter.WriteField(value); for each property in those objects.

Side note: that method really should declare the returning type a string and not an object.

The code is used in a copy & paste event where a DataGrid fires an event for each individual cell, and I need to parse each individual cell, depending on the amount of rows/columns the user has selected this piece of code could be called thousand of times. (for each cell)

If performance is a problem, do not try to handle this on a cell by cell basis, of alternatively give your user an alternate way to paste in a larger set of data that you can parse into a CSV file and then programmaticallyy assign to the underlying data.

As you are using 3rd party libraries (Telerik and CsvHelper) it is worth consulting their forums for specific advice on how to intercept a paste event for a bulk paste without being forced to handle the cells individually.

That being said, we can improve the performance by taking some of the internals from CsvHelper, not that you have specified that all fields should be quoted with the ShouldQuote = (_) => true so we can simply to this:

public static string ToCsv(object obj, CultureInfo cultureInfo, string delimiter, string escapedDelimiter, bool alwaysQuote = true)
{
    var field = String.Format(cultureInfo, "{0}", obj);
    if (alwaysQuote || field.Contains(delimiter))
    {
        field = field. Replace(delimiter, escapedDelimiter);
        return delimiter + field + delimiter;
    }
    return field;
}

At this level, when we are only dealing with 1 individual value at a time, simple string replace is likely to be the same or more efficient than a Regular Expression solution.

---

This code was de-constructed from CsvHelper.WriteField

Answer 2

I know you mention CsvHelper, but here is a method I put together to build a csv "cell" using StringBuilder

    /// <summary>
    /// StringBuilder Extension method - Escape cells, as they may potentially contain reserved characters
    /// </summary>
    /// <param name="sb">StringBuilder that is assembling the csv string</param>
    /// <param name="val">Value string to be persisted to the cell</param>
    /// <returns>StringBuilder, with the escaped data cell appended</returns>
    internal static StringBuilder EscapeCell(this StringBuilder sb, string val)
    {
        if (string.IsNullOrWhiteSpace(val)) return sb;

        //no need to escape if does not contain , " \r or \n
        if (!val.Contains(",") && !val.Contains("\"") && !val.Contains("\r") && !val.Contains("\n"))
        {
            sb.Append(val);
            return sb;
        }

        //surround in quotes + any internal quotes need to be doubled -> ex.,"this is an ""example"" of an escaped cell",
        string escaped = val[0] == '\"'
            ? val.Substring(1, val.Length - 2).Replace("\"", "\"\"")
            : val.Replace("\"", "\"\"");
        
        sb.Append('\"').Append(escaped).Append('\"');
        return sb;
    }

The idea is that you want to escape the entire cell if it has a special char that may break CSV structure, and any internal " needs to be normalized as ""

Using StringBuilder throughout means building the CSV string is as fast as can be. Then write the CSV string to file as needed