Export DataGridView to CSV File with C#

Here is a bit of code that I use to export contents of DataGridViews to csv files. This is part of an application that pulls data from various spreadsheet forms so it can be imported into a database.

Firstly we need to have reference the System.IO (for the StreamWriter) and System.Windows.Forms (for the DataGridView and DataGridViewRow).

using System.IO;
using System.Windows.Forms;

The code below accepts a DataGridView (gridIn) and a string (outputFile) as input parameters. gridIn is the DataGridView to Export data from and outputFile is the full path and filename (including .csv) to export the data to.

The basic process is:

Check to see if the DataGridView has any rows. If yes, open a connection to the file then loop through the columns and add the column headers as the first row of the file (separated by commas).

Then repeat the process for each row and column writing the values to the file with a comma between each column and a newline at the end of each row.

In this example I replace commas or newlines in the data with spaces (as this suited in the particular case I was working on). However, this could be changed, by encasing the text in double quotes for example if needed.

public void writeCSV(DataGridView gridIn, string outputFile)
{
    //test to see if the DataGridView has any rows
    if (gridIn.RowCount > 0)
    {
       string value = "";
       DataGridViewRow dr = new DataGridViewRow();
       StreamWriter swOut = new StreamWriter(outputFile);

       //write header rows to csv
       for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
       {
          if (i > 0)
          {
             swOut.Write(",");
          }
          swOut.Write(gridIn.Columns[i].HeaderText);
       }

       swOut.WriteLine();

       //write DataGridView rows to csv
       for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
       {
          if (j > 0)
          {
          swOut.WriteLine();
          }

          dr = gridIn.Rows[j];

          for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
          {
             if (i > 0)
             {
                swOut.Write(",");
             }

             value = dr.Cells[i].Value.ToString();
             //replace comma's with spaces
             value = value.Replace(',', ' ');
             //replace embedded newlines with spaces
             value = value.Replace(Environment.NewLine, " ");

             swOut.Write(value);
          }
       }
       swOut.Close();
    }
 }

UPDATE: Due to the way that Rows are counted, you need to set AllowUserToAddRows to False in the DataGrid OR subtract 2 from the Row Count, i.e.

for (int j = 0; j <= gridIn.Rows.Count - 2; j++)

11 Comments

  1. sudiip said:

    Hi!
    Can you please show me your ouput file of csv as my csv file is saving the value cell by cell and I want the data of arow in single cell separated by comas.

    Thanks in advance

    January 22, 2013
    Reply
    • CoastalCoder said:

      Hi, if you open the created file in notepad you’ll see that the values are separated by comma’s. However, if you open in Excel it will put each value into a different cell (this is the behaviour of Excel).

      Hope this helps.

      January 22, 2013
      Reply
      • sudiip said:

        But whenever we save an excel as .csv it comes with different format as like in a single cell separated by comas. I am stuck on it please help.
        Thanks

        January 22, 2013
    • CoastalCoder said:

      Hi, if you want all the data to appear in one Cell in Excel with comma’s you need to wrap each line in double quotes:

      e.g.

      1, John, Smith, 20/01/1969
      2, Fred, Jones, 15/12/1967

      Would become

      “1, John, Smith, 20/01/1969″
      “2, Fred, Jones, 15/12/1967″

      To do this in the code add a ” using

      swOut.Write(‘”‘);

      Outside the loop that iterates through the columns;

      swOut.Write(‘”‘);
      for (int i = 0; i 0)
      {
      swOut.Write(“,”);
      }

      value = dr.Cells[i].Value.ToString();
      //replace comma’s with spaces
      value = value.Replace(‘,’, ‘ ‘);
      //replace embedded newlines with spaces
      value = value.Replace(Environment.NewLine, ” “);

      swOut.Write(value);
      }
      swOut.Write(‘”‘);

      January 22, 2013
      Reply
  2. Hajmanga said:

    Thank you a lot;
    A bug prevented code to complete , and was in this line ” value = dr.Cells[i].Value.ToString();”
    debugger said: NullRefferenceException was unhandled
    Object reference not set to an instance of an object.

    April 23, 2013
    Reply
    • CoastalCoder said:

      Hi, how are you calling the function, it’s sounds as if you’re not passing a valid grid.

      April 23, 2013
      Reply
      • James said:

        I also have the same problem, and I’m 100% passing a valid grid

        September 9, 2013
      • CoastalCoder said:

        Hi, did you checkout the Update at the bottom of the post?

        September 9, 2013
  3. Pratap Kumar Alluri said:

    The bugs occurs when they are null values in the grid. The code does not handle null values

    November 18, 2013
    Reply
  4. Tsvika said:

    Thanks CoastalCoder, it works great! For me it also happened that the whole line came as one cell in Excel. My solution was to use \t instead of comma.
    I.e., used swOut.Write(“\t”) instead of swOut.Write(“,”) in both appearances.

    April 6, 2014
    Reply
    • Me said:

      Excellent, thanks for the feedback.

      April 7, 2014
      Reply

Leave a Reply