Export Datatable into csv

 

Introduction:

While working on .net projects you may get a situation where you need to export data into csv (Comma Separated Value) file format. There are so many ways of doing this and different programmers use to do it on different ways depending on situations and availability of data and resources they have.

Here I am going to share a simple function which will help you in exporting data from Datatable to csv file.

Prerequisite:

Before I share this function, I assumes that you already have data in Datatable.

C# Code:

#region Method : ConvertToCSV
private void ConvertToCSV(DataTable dt, string fileName, string delimiter)
{
    //prepare the output stream
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AppendHeader("Content-Disposition",
        string.Format("attachment; filename={0}", fileName));
    //write the csv column headers
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        Response.Write(dt.Columns[i].ColumnName);
        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
    }

    //write the data
    foreach (DataRow row in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            string data = row[i].ToString();
            if (data.Contains("\""))
            {
                data = data.Replace("\"", "\"\"");
            }
            if (data.Contains("|"))
            {
                data = data.Replace("|", ", ");
            }
            if (data.Contains(","))
            {
                data = String.Format("\"{0}\"", data);
            }
            if (data.Contains(System.Environment.NewLine))
            {
                //data = String.Format("\"{0}\"", data);
                data = data.Replace(System.Environment.NewLine, " ");
            }
            Response.Write(data);
            Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
        }
    }
     Response.End();
}
#endregion

This function takes three parameters a dt (Datatable) - which you want to export to csv file, filename - name of file in which you wants to save the file and a delimiter - which will be used to separate the values in this case a comma (,).

You can call this function as shown in below code:

ConvertToCSV(dt, "data.csv", ",");

I have tried to handle most of the cases like occurrence of double quotes ("),  comma in between the text which you may need to keep as it is and new line character.

I hope this will be helpful for you. Keep visiting and provide us with your feedback.