How to create a csv from a datatable

How to create a csv from a datatable

In a project I’ve been working on I came across the need export a DataTable to CSV and this is how I did it.  I have split up the functionality as I also use them to concatenate several DataTables into one CSV.

I need to import for following namespaces in this example.

using System.Data.SqlClient;
using System.Text;


The first method gets the DataTable.  It could easily be changed to use stored procedures and add parameters.

private DataTable GetDataTable()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM MyTable", con);
    DataTable dt = new DataTable();

    using (con)
    {
        da.Fill(dt);
    }

    return dt;
}
The next method creates the CSV using a StringBuilder.  First it creates the headings by looping through the DataTable’s Columns collection, then creates each row by iterating through the Rows.  I’m also checking for any commas in the value which would break the CSV, and putting the value in quotes which escapes the comma.  Finally it’s checking for any \r or \n characters and replacing them with a space.  There may well be other characters that break the CSV but for now this is fine for what I’m doing, although it is very simple  just to add extra conditions to the method to check for invalid characters.

public string GetCSV(DataTable dt)
{
    StringBuilder sb = new StringBuilder();

    //Line for column names
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        sb.Append(dt.Columns[i]);

        if (i < dt.Columns.Count - 1)
        {
            sb.Append(",");
        }
    }

    sb.AppendLine();

    //Loop through table and create a line for each row
    foreach (DataRow dr in dt.Rows)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!Convert.IsDBNull(dr[i]))
            {
                string value = dr[i].ToString();

                //Check if the value contans a comma and place it in quotes if so
                if (value.Contains(","))
                {
                    value = string.Concat("\"", value, "\"");
                }

                //Replace any \r or \n special characters from a new line with a space
                if (value.Contains("\r"))
                {
                    value = value.Replace("\r", " ");
                }
                if (value.Contains("\n"))
                {
                    value = value.Replace("\n", " ");
                }

                sb.Append(value);
            }

            if (i < dt.Columns.Count - 1)
            {
                sb.Append(",");
            }
        }

        sb.AppendLine();
    }

    return sb.ToString();
}

Now we have the CSV as a string you could easily save it to a file, but I want to write it to the page’s HttpResponse so the user can download it in the browser.  To do this I have the following method.

private void ExportCSV(string csv, string filename)
{
    Response.Clear();
    Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.csv", filename));
    Response.ContentType = "text/csv";
    Response.AddHeader("Pragma", "public");
    Response.Write(csv);
    Response.End();
}

Now to put this all together I have a button click event like this:

protected void btnCreateCSV_Click(object sender, EventArgs e)
{
    try
    {
        string csv = GetCSV(GetDataTable());
        ExportCSV(csv, "MyCSV");
    }
    catch (System.Threading.ThreadAbortException)
    {
        //Thrown when calling Response.End in ExportCSV
    }
    catch(Exception ex)
    {
        lblMessage.Text = string.Concat("An error occurred: ", ex.Message);
    }
}
When using Response.End in a Try block as the ExportCSV does, a ThreadAbortException is thrown.  For this reason I handle this Exception first, then any other Exception which will be a true error. See here for more info.