Converting DataSet/DataTable to CSV

Question

Please let me know, if there any way to generate CSV files from a DataTable or DataSet? To be specific, without manually iterating through rows of DataTable and concatenating.

Please help

why do u not want to iterate through the rows of DataTable? That would be quite straight forward... — Sören, Feb 03 '11 at 09:39
You can check this https://gist.github.com/riyadparvez/4467668 — user, Mar 12 '13 at 07:04

digEmAll · Accepted Answer · 2011-02-03T10:27:15.273

17

There are several ways to do that.

One of the simplest (IMO) is using FileHelpers Library

FileHelpers.CsvEngine.DataTableToCsv(dataTable, filename);

edited Feb 03 '11 at 10:27

answered Feb 03 '11 at 10:16

digEmAll

56,430
9
115
140

Alexander Stojakovic · Answer 2 · 2015-01-06T03:47:02.290

A relative simple, compact and quite flexible solution could be the following extension method:

public static string ToCsv(this DataTable table, string colSep = "", string rowSep = "\r\n")
{
    var format = string.Join(colSep, Enumerable.Range(0, table.Columns.Count)
                                            .Select(i => string.Format("{{{0}}}", i)));

    return string.Join(rowSep, table.Rows.OfType<DataRow>()
                                        .Select(i => string.Format(format, i.ItemArray)));
}

Please note that this solution could cause problems with huge amounts of data, in which case you should stream the output. Quoting and formatting would of course make the code more complex.

score 2 · Answer 3 · edited Sep 14 '16 at 22:09

//Dataset To Xls
ExportDataSetToCsvFile(DS,@"C:\\");

internal static void ExportDataSetToCsvFile(DataSet _DataSet, string DestinationCsvDirectory)
{
    try
    {
        foreach (DataTable DDT in _DataSet.Tables)
        {
            String MyFile = @DestinationCsvDirectory + "\\_" + DDT.TableName.ToString() + DateTime.Now.ToString("yyyyMMddhhMMssffff") + ".csv";//+ DateTime.Now.ToString("ddMMyyyyhhMMssffff")
            using (var outputFile = File.CreateText(MyFile))
            {
                String CsvText = string.Empty;

                foreach (DataColumn DC in DDT.Columns)
                {
                    if (CsvText != "")
                        CsvText = CsvText + "," + DC.ColumnName.ToString();
                    else
                        CsvText = DC.ColumnName.ToString();
                }
                outputFile.WriteLine(CsvText.ToString().TrimEnd(','));
                CsvText = string.Empty;

                foreach (DataRow DDR in DDT.Rows)
                {
                    foreach (DataColumn DCC in DDT.Columns)
                    {
                        if (CsvText != "")
                            CsvText = CsvText + "," + DDR[DCC.ColumnName.ToString()].ToString();
                        else
                            CsvText = DDR[DCC.ColumnName.ToString()].ToString();
                    }
                    outputFile.WriteLine(CsvText.ToString().TrimEnd(','));
                    CsvText = string.Empty;
                }
                System.Threading.Thread.Sleep(1000);
            }
        }
    }
    catch (Exception Ex)
    {
        throw Ex;
    }  
}

This answer isnt clear as to how it resolves the OP's question. You should edit this to make it clearer and more readable — Takarii, Aug 23 '16 at 13:25

score 2 · Answer 4 · answered Feb 03 '11 at 10:59

There is, I hope, also a possible way for doing that:

    static void Main(string[] args)
    {
        DataTable dt = new DataTable("MyTable");
        dt.Columns.Add("Id", typeof(int));
        dt.Columns.Add("Name", typeof(string));
        DataRow dr1 = dt.NewRow();
        dr1["Id"] = 1;
        dr1["Name"] = "John Smith";
        dt.Rows.Add(dr1);
        DataRow dr2 = dt.NewRow();
        dr2["Id"] = 2;
        dr2["Name"] = "John West";
        dt.Rows.Add(dr2);

        List<DataRow> list = dt.AsEnumerable().ToList();
        var strlist = from dr in list
                      select dr[0] + ", " + dr[1];
        var csv = string.Join(Environment.NewLine,strlist);
        Console.WriteLine(csv);
    }

The only drawback I came to notice with this was, we need to concat dt[Index], if more columns are going to be added in future, except for that this looks good. — AbrahamJP, Feb 03 '11 at 11:07

score 1 · Answer 5 · edited Oct 17 '12 at 20:29

So this is a fairly bizarre solution, but it works faster than most as it makes use of the JSON.net library's serialization. This speeds the solution up significantly.

Steps:

Create array of every column name in the data table, should be simple
Use JSON.net to convert datatable to a json string

string json = JsonConvert.SerializeObject(dt, Formatting.None);
Begin making use of the Replace function on c# strings and strip the json string of all json formatting.

json = json.Replace("\"", "").Replace("},{", "\n").Replace(":", "").Replace("[{", "").Replace("}]", "");
Then use the array from step 1 to remove all column names from the json string. You are left with a csv formatted string.
Consider using the array created in step 1 to add the column names back in as the first row in csv format.

Converting DataSet/DataTable to CSV

5 Answers5

Linked