Export Datatable to CSV Using Extension Method

Introduction

I would like to share how to export a DataTable to Comma Separated File (CSV) format using an Extension method.

In this document we will also learn how to use Extension methods to make code more manageable.

About 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 snapshot of a sample CSV file:



Procedure

a. Create DataTable

We added a class containing a method returning a DataTable

Code

public static class OperationsUtlity
     {
        public static DataTable createDataTable()
        {
            DataTable table = new DataTable();
                //columns
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("NAME", typeof(string));
            table.Columns.Add("CITY", typeof(string));

                //data
            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:



b. Create UI to display DataTable

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



Code



c. Create 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)
          {
           }

  This method takes the DataTable as its parameter, that's why it is an extension method.
  
  
• 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();
          }

d. Export to CSV on button click

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


        }

 

e.

 

dt.ToCSV() will call the ToCSV method defined in the CSVutlity class.



f. Running the code

The following will be the output:



Open this file in notepad.



By default this file opens into Excel.

Double-click to open this file in Excel.



Refrences
Basic of Extension method
http://www.c-sharpcorner.com/UploadFile/deveshomar/extension-method-in-C-Sharp/

Conclusion

We have learned how to use an Extension method and learned how to export a DataTable to a CSV file.