csvhelper everything get written in one column in Excel

Question

I'm trying to use csvhelper to create my csv file by following some tutorials but all my data get written in only one column. This is my code:

EDITED As I understood from the comments, the problem is with excel reading the csv files. I found some solution that I can fix this problem by making some changes in Excel setting, in that case my question is: is there anyway to address this issue from my code, that it won't require any changes in Excel setting to be able to read csv files properly?

    public void CreateCSVFile()
    {
        using (var sw = new StreamWriter(@"countrylistoutput.csv"))
        {
            var writer = new CsvWriter(sw);
            using (var dt = ExportToCSV())
            {
                foreach (DataColumn column in dt.Columns)
                {
                    writer.WriteField(column.ColumnName);
                }
                writer.NextRecord();

                foreach (DataRow row in dt.Rows)
                {
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        writer.WriteField(row[i]);
                    }
                    writer.NextRecord();
                }
            }
        }


    }

I don't get what I'm doing wrong, I would appreciate it if anyone could help me with this issue.

And here is how I tried to feed the data:

    public System.Data.DataTable ExportToCSV()
    {
        System.Data.DataTable table = new System.Data.DataTable();
        table.Columns.Add("ID", typeof(int));
        table.Columns.Add("Name", typeof(string));
        table.Columns.Add("Sex", typeof(string));
        table.Columns.Add("Subject1", typeof(int));
        table.Columns.Add("Subject2", typeof(int));
        table.Columns.Add("Subject3", typeof(int));
        table.Columns.Add("Subject4", typeof(int));
        table.Columns.Add("Subject5", typeof(int));
        table.Columns.Add("Subject6", typeof(int));
        table.Rows.Add(1, "Amar", "M", 78, 59, 72, 95, 83, 77);
        table.Rows.Add(2, "Mohit", "M", 76, 65, 85, 87, 72, 90);
        table.Rows.Add(3, "Garima", "F", 77, 73, 83, 64, 86, 63);
        table.Rows.Add(4, "jyoti", "F", 55, 77, 85, 69, 70, 86);
        table.Rows.Add(5, "Avinash", "M", 87, 73, 69, 75, 67, 81);
        table.Rows.Add(6, "Devesh", "M", 92, 87, 78, 73, 75, 72);
        return table;
    }
}

screenshot of the result

Thanks

There's nothing wrong. You *imported* the file in Excel using `;` as the list separator instead of `,`. — Panagiotis Kanavos, Feb 06 '18 at 16:15
When you double click on a CSV file, Excel *imports* it using your locale's list separator. In locales where `,` is the decimal separator, the list separator is `;`. Either set a different list separator when exporting, or use `File > Open` or `Data > Insert` to import the file with your own settings — Panagiotis Kanavos, Feb 06 '18 at 16:17
On the other hand, if you want to read the file in Excel, use EPPlus to create a real `xlsx` file with eg `sheet.LoadFromDataTable(dt)` — Panagiotis Kanavos, Feb 06 '18 at 16:18
Hi Panagiotis, I added the method that shows how I feed the data as well, so maybe the correct way to ask the question is, how can I feed my data that the result can be open by excel as well (not only excel) — Nima, Feb 07 '18 at 07:22
when You modify question add at least mark update: or similar. Hard to discus with changing questions — Jacek Cz, Feb 07 '18 at 15:56

score 5 · Accepted Answer · answered Mar 13 '18 at 16:50

That happens when Excel doesn't use commas as the field separator (it depends on Excel's locale). The solution for Excel specifically is to add a special sep=, line at the top of the file, above any headers. For example:

using (var streamWriter = new StreamWriter(outputFilePath))
{
    streamWriter.WriteLine("sep=,"); // make Excel use comma as field separator
    using (var csvWriter = new CsvWriter(streamWriter))
    {
        csvWriter.WriteField("field A");
        csvWriter.WriteField("field B");
        csvWriter.NextRecord();
    }
}

That will fix the problem in Excel, but cause problems in other spreadsheet apps. You can support both by making that sep=, line conditional on the intended format. Here's how that might look in a web UI:

score 0 · Answer 2 · answered Jun 25 '20 at 15:51

0

Most probably your CSV looks like

a,b,c
d,e,f

you need to make it

"a","b","c"
"d","e","f"

https://stackoverflow.com/a/54027009/9306125

answered Jun 25 '20 at 15:51

serjou

17
1
9

csvhelper everything get written in one column in Excel

2 Answers2