Export DataTable To CSV In C#

C#

Export DataTable To CSV In C#

In this article and code sample, I would like to share how to export a DataTable to a Comma Separated File (CSV) format using a C# extension method. We will also learn how to use an extension method to make code more manageable.

What is a .csv file

A Comma Separated Value (CSV) file contains data with all the columns in the file separated by a comma. Another use of a CSV file is to directly open the file in Excel and then the data will be auto-filled into Excel cells.

The following is a snapshot of a sample CSV file.

Here is the process of creating a DataTable and exporting its data to a .csv file.

Step 1. Create a DataTable

We added a class containing a method that returns a DataTable. The DataTable is created dynamically. In your case, your DataTable may be created via DataSet that fetches data from a database. If you're new to ADO.NET and DataTable, first read this: DataTable in C# Code.

csharp
public static class OperationsUtility
{
    public static DataTable CreateDataTable()
    {
        DataTable table = new DataTable();

        // Define columns
        table.Columns.Add("ID", typeof(int));
        table.Columns.Add("NAME", typeof(string));
        table.Columns.Add("CITY", typeof(string));

        // Add data rows
        table.Rows.Add(111, "Devesh", "Ghaziabad");
        table.Rows.Add(222, "ROLI", "KANPUR");
        table.Rows.Add(102, "ROLI", "MAINPURI");
        table.Rows.Add(212, "DEVESH", "KANPUR");
        table.Rows.Add(102, "NIKHIL", "GZB");
        table.Rows.Add(212, "HIMANSHU", "NOIDa");
        table.Rows.Add(102, "AVINASH", "NOIDa");
        table.Rows.Add(212, "BHUPPI", "GZB");

        return table;
    }
}

Step 2. Create UI to display DataTable

Here we created a simple DataGridView to bind to the DataTable.

Code

I took Window Form.

Step 3. Create an Extension Method that converts the DataTable to CSV

csharp
public static void ToCSV(this 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();
}

Step 4. Export to CSV on button click

csharp
private void btnCSV_Click(object sender, EventArgs e) {
    DataTable dt = OperationsUtlity.createDataTable();
    string filename = OpenSavefileDialog();
    dt.ToCSV(filename);
}