CODEWITHSUNDEEP
c#excelcsvcsvhelper
Nima
Nima

Reputation: 39

csvhelper everything get written in one column in Excel

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

Upvotes: 1

Views: 2748

Answers (2)

serjou
serjou

Reputation: 25

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

Upvotes: 0

Pathoschild
Pathoschild

Reputation: 4706

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:
download button with 'csv' and 'csv for excel' options

Upvotes: 5

Related Questions