Export DataTable To CSV In C#

Devesh Omar
1y
720.3k
0
17

Article

Introduction

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.

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.

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;
    }
}

Code snapshot

Step 2. Create UI to display DataTable

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

Create UI to display Datatable

Code

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

Create a static class as per the code below.
```
public static class  CSVUtlity  { }
```

Add an Extension method as in the following.

public static void ToCSV(this DataTable dtDataTable, string strFilePath){ }

After adding the Extension method the ToCSV method is now appearing in the list below.

The following is the code to convert the DataTable to CSV.

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

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