Delimiter Workaround CSV C#

Question

I have written a method that writes a DataTable to a CSV file that looks like this;

    public void writeToCSV(DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                          Select(column => column.ColumnName);
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in dt.Rows)
        {
            IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
            sb.AppendLine(string.Join(",", fields));
        }
        File.WriteAllText("C:\\xampp\\mysql\\data\\sdcdbftest\\test.csv", sb.ToString());
        MessageBox.Show("Success");
    }

This writes to the CSV file fine, however the issue is when one of our users has inputted a comma in one of the fields that I am building the DataTable from. Excel takes this as a new column, and all of the data ends up in the wrong places when written to Excel.

I have tried using a semicolon instead of a comma but that hasn't worked so far. One way I did get it working is writing to a .txt file with a semicolon as the appended chraracter and then opening it in Excel, of course selecting the semicolon as the delimiter.

Whilst this is a workaround I would rather not make the user do this everytime they open an excel file. Is there a workaround?

The string join will not work for some cases. CSV files have standards, and these RFC standards must be followed. The most simple way to accomplish a simple library would be having Flags during the scanning of data, and the flag for the data part should be on, when a delimiter type is encountered. Basically, you will need to read character by character in order to accomplish a good CSV reader. — Hozikimaru, Oct 05 '15 at 15:36
@CBreeze library advice is generally off-topic for SO, but the most recommended standard library for doing anything like this is the "FileHelpers" library. — CodeCaster, Oct 05 '15 at 15:38
Quick fix...change `field => field.ToString()` to `field => field.ToString().Contains(",") ? "\"" + field.ToString() + "\"" : field.ToString()` — Idle_Mind, Oct 05 '15 at 17:30
@Idle_Mind yeah and then the input contains `"`... Don't do quick fixes, don't reinvent the wheel. — CodeCaster, Oct 06 '15 at 06:29
@CodeCaster after installing FileHelpers and using `FileHelpers.CsvEngine.DataTableToCsv` there is still the issue of the commas splitting up columns. Could you recommend a way through FileHelpers to stop this? I have no experience with it and haven't been able to find anything online. — CBreeze, Oct 06 '15 at 07:23

score 2 · Answer 1 · answered Oct 05 '15 at 15:35

2

You can use double quotes " to escape columns that contain commas and new lines.

Source: wikipedia

answered Oct 05 '15 at 15:35

Joanvo

5,677
2
25
35

score 0 · Answer 2 · answered Oct 05 '15 at 15:32

You need to delimit your CSV fields. If a field contains reserved characters then you need to wrap it with double quotes e.g. "My, new field". Additionally, if the field value itself contains a double quote then this needs to be delimited with an additional double quote.

Delimiter Workaround CSV C#

2 Answers2