CSV export Function, what to do if string contains the character seperator?-CodePudding

i use a fuction to convert a Datatable to CSV and i use File.WriteAllText to save it to a file.

    private static string DataTableToCSV(DataTable dtable, char seperator)
    {
        StringBuilder sb = new StringBuilder();

        for (int i = 0; i < dtable.Columns.Count; i  )
        {
            sb.Append(dtable.Columns[i]);
            if (i < dtable.Columns.Count - 1)
                sb.Append(seperator);
        }
        sb.AppendLine();
        foreach (DataRow dr in dtable.Rows)
        {
            for (int i = 0; i < dtable.Columns.Count; i  )
            {
                sb.Append(dr[i].ToString());
                if (i < dtable.Columns.Count - 1)
                {
                    sb.Append(seperator);
                }
            }
            sb.AppendLine();
        }
        return sb.ToString();
    }

well, the Code is working. My problem is, in CSV the seperator is ';'. Now, of course, errors occur when a string in the table contains a semicolon. Is there perhaps an elegant way to solve the problem?

CodePudding user response：

I wrote a little helper for formatting every line of my CSV.

private string FormatForCsv(string value) => value != null && value.Contains(';') ? value.Replace(value, "\""   value   "\"") : value;

So then you can implement using:

sb.Append(FormatForCsv(dr[i]?.ToString()));

Of course you can use the same for the headers too.

I also added a null check when converting dr[i] to a string, just to be on the safe side.

CodePudding user response：

You should consider using a library to handle the CSV part for you. The current accepted answer handles quoting when the value contains the delimiter, but what happens when the value contains or starts with the quote character, or what if the value contains a newline? That approach will create an invalid file. The de-facto CSV standard specifies that fields should be quoted when the value contains a delimiter or a newline, and that quotes should be doubled up to "escape" them.

There are many libraries that can help with this, including one that I'm the author of: Sylvan.Data.Csv. Sylvan handles your scenarios in a very straightforward way:

using Sylvan.Data.Csv;

static string DataTableToCSV(DataTable dtable, char seperator)
{
    using var sw = new StringWriter();
    var opts = new CsvDataWriterOptions { Delimiter = seperator };
    using var csvw = CsvDataWriter.Create(sw, opts);
    csvw.Write(dtable.CreateDataReader());
    return sw.ToString();
}