Convert DataTable to CSV or List or JSON String


This article describes about how to convert DataTable to CSV (Comma Separated Values) or List object or JSON string using Newtonsoft.

Create a simple console application and add code to create ADO.NET DataTable.

Add DataRows to DataTable. You can use below code for creation of DataTable and addition of DataRows

    
    DataTable dtProducts = new DataTable();
    dtProducts.Columns.Add("ProductID", typeof(int));
    dtProducts.Columns.Add("ProductName", typeof(string));
    dtProducts.Columns.Add("SupplierID", typeof(int));
    dtProducts.Columns.Add("CategoryID", typeof(int));
    dtProducts.Columns.Add("QuantityPerUnit", typeof(string));
    dtProducts.Columns.Add("UnitPrice", typeof(decimal));
    dtProducts.Columns.Add("UnitsInStock", typeof(int));
    dtProducts.Columns.Add("UnitsOnOrder", typeof(int));
    dtProducts.Columns.Add("ReorderLevel", typeof(Int16));
    dtProducts.Columns.Add("Discontinued", typeof(bool));

    dtProducts.Rows.Add(1, "Chai", 1, 1, 
            "10 boxes x 20 bags", 18.00, 39, 0, 10, 0);
    dtProducts.Rows.Add(2, "Chang", 1, 1, 
            "24 - 12 oz bottles", 19.00, 17, 40, 25, 0);
    dtProducts.Rows.Add(3, "Aniseed Syrup", 1, 2, 
            "12 - 550 ml bottles", 10.00, 13, 70, 25, 0);
    dtProducts.Rows.Add(4, "Chef Anton's Cajun Seasoning", 2, 2, 
            "48 - 6 oz jars", 22.00, 53, 0, 0, 0);
    dtProducts.Rows.Add(5, "Chef Anton's Gumbo Mix", 2, 2, 
            "36 boxes", 21.35, 0, 0, 0, 1);
    dtProducts.Rows.Add(6, "Grandma's Boysenberry Spread", 3, 2, 
            "12 - 8 oz jars", 25.00, 120, 0, 25, 0);
    dtProducts.Rows.Add(7,"Uncle Bob's Organic Dried Pears",3,7, 
            "12 - 1 lb pkgs.", 30.00, 15, 0, 10, 0);
    dtProducts.Rows.Add(8, "Northwoods Cranberry Sauce", 3, 2, 
            "12 - 12 oz jars", 40.00, 6, 0, 0, 0);
    dtProducts.Rows.Add(9, "Mishi Kobe Niku", 4, 6, 
            "18 - 500 g pkgs.", 97.00, 29, 0, 0, 1);
    dtProducts.Rows.Add(10, "Ikura", 4, 8, 
            "12 - 200 ml jars", 31.00, 31, 0, 0, 0);
        
        

I have taken this data from Northwind database.

DataTable to CSV

To write DataTable contents to CSV, we can use System.IO.StreamWriter. StreamWriter allows you to write any plain text to files which are stored on physical file or network stream.

You will have to write the header and DataRows, row by row to csv file. So first write the header and then DataRows.

Below code reads header and data from DataTable and write it to CSV file using StreamWriter.


public static void ToCSV(DataTable dtDataTable, string strFilePath)
{
    StreamWriter sw = new StreamWriter(strFilePath, false);
    //headers  
    for (int i = 0; i < dtDataTable.Columns.Count; i++)
    {
        sw.Write(dtDataTable.Columns[i]);
        if (i < dtDataTable.Columns.Count - 1)
        {
            sw.Write(",");
        }
    }
    sw.Write(sw.NewLine);
    foreach (DataRow dr in dtDataTable.Rows)
    {
        for (int i = 0; i < dtDataTable.Columns.Count; i++)
        {
            if (!Convert.IsDBNull(dr[i]))
            {
                string value = dr[i].ToString();
                if (value.Contains(','))
                {
                    value = String.Format("\"{0}\"", value);
                    sw.Write(value);
                }
                else
                {
                    sw.Write(dr[i].ToString());
                }
            }
            if (i < dtDataTable.Columns.Count - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);
    }
    sw.Close();
}        

        

This writes each row of DataTable to CSV. You can also write entire DataTable's content to CSV in one Write statement.

Below code shows how to write all content in single execution.


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("test.csv", sb.ToString());

        

It require very less code however it will keep your content in memory for more time. So this method is not recommended for larger files having more than 1000 rows.

If you need more control on formating like excel cells font, backcolor, calculation of cells you can use Microsoft.Interop.Office.Excel.


DataTable to List object

In this step you will convert your DataTable to List of Product objects. Add a new class name it as Product. This class represent as Product entity.

Add below code and properties to Product class.


public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public Int16 ReorderLevel { get; set; }
    public bool Discontinued { get; set; }

}
    
            

Below method takes DataTable as input parameter and add its DataRow details to List of Product objects.


private static void GetProductList(DataTable table)
{
    var productList = new 
                List<Product>(table.Rows.Count);
    foreach (DataRow row in table.Rows)
    {
        var values = row.ItemArray;
        var product = new Product()
        {
            ProductID = Convert.ToInt32(row[0]),
            ProductName = row[1].ToString(),
            SupplierID = Convert.ToInt32(row[2]),
            CategoryID = Convert.ToInt32(row[3]),
            QuantityPerUnit = row[4].ToString(),
            UnitPrice = Convert.ToDecimal(row[5]),
            UnitsInStock = Convert.ToInt32(row[6]),
            UnitsOnOrder = Convert.ToInt32(row[7]),
            ReorderLevel = Convert.ToInt16(row[8]),
            Discontinued = Convert.ToBoolean(row[9]),
        };
        productList.Add(product);
    }
}
    
            

DataTable to JSON string

In this step you will convert your DataTable contents into JSON string using Newtonsoft dll. For more info on JSON framework visit Newtonsoft JSON.

To add JSON.NET, Right click on project and click on Manage NuGet Packages -> Search and Install JSON.NET.

Add reference to program.cs file.

    
    using Newtonsoft.Json;
        
            

You will use JsonConvert.SerializeObject to serialize DataTable and produce JSON string.


private static void GetProductJson(DataTable table)
{
    string JSONresult;
    JSONresult = JsonConvert.SerializeObject(table);
    Console.Write(JSONresult);
}
    
            

Convert DataTable to JSON

You can convert DataTable in many different formats like XML, Array or IEnumerable<T> also you can perform various operations on DataTable like Compare multiple DataTables to Get Matched or Unmatched datarows.

Download source code.

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by drona at 7/2/2015 3:35:00 AM
Very helpful to understand use of DataTable. Thanks for sharing it
post comment on dotnetmentors.com