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++)
Advertisements
This entry was posted in Programming and tagged , , , , , , . Bookmark the permalink.

11 Responses to Export DataGridView to CSV File with C#

  1. sudiip says:

    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

    • CoastalCoder says:

      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.

      • sudiip says:

        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

    • CoastalCoder says:

      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(‘”‘);

  2. Hajmanga says:

    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.

  3. Pratap Kumar Alluri says:

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

  4. Tsvika says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s