Exporting your data to CSV format

Introduction

On-line web applications providing some business functionality often need to exchange data between other applications. For example, data displayed in a DataGrid might be needed by an Excel spreadsheet or data might be needed to be put inside Outlook address book. In such cases Comma Separated Values (CSV) format is the most widely used data interchange format. In this article we will see how to write a generic routine so that your entire DataTable can be exported to CSV format.

The scenario

As an example we will consider following scenario:

You have a web application that stores customer information such as name, contact person and country. The application stores the data in SQL Server 2000 database table called "Customers". You want to provide an export facility for this data so that you can download a CSV file containing this data at a click of a button.

As you might have guessed we will be using the Customers table of Northwind database for our example.

Creating the Web Form

• Create a new web application in VS.NET.
• Add a web form to it
• Drag and drop a DataGrid on it
• Drag and drop a button and set its Text property to "Export to CSV"
• Drag and drop a checkbox and set its Text property to "Export Column Headings"

Populating the DataGrid

In order to populate the DataGrid with data we will write a subroutine called BindGrid(). Following code illustrates this subroutine:

Sub BindGrid()
   Dim da As New SqlDataAdapter
   ("select * from customers", 
   "data source=.\vsdotnet;
   initial catalog=northwind;user id=sa")
   Dim ds As New DataSet
   da.Fill(ds, "custoemrs")
   Session("myds") = ds
   DataGrid1.DataSource = ds
   DataGrid1.DataBind()
End Sub

Here, we populated a DataSet with all the records from Customers table. We store this DataSet in a session variable for later user. Finally, we bind the DataGrid with this DataSet.

Creating CSVHelper for exporting data

Next, we will create a class called CSVHelper that allows us to export the data into CSV format. The class will have a public function called Export() with following signature:

Export(ByVal ds As DataSet, 
ByVal exportcolumnheadings As Boolean) As String

The first parameter accepts the DataSet whose data is to be exported and the second parameter indicates whether we want to export column headings or no.

Following code illustrates the complete Export() function:

Public Function Export(ByVal ds As DataSet, 
ByVal exportcolumnheadings As Boolean) As String
Dim header As String
Dim body As String
Dim record As String
If exportcolumnheadings Then
For Each col As DataColumn In ds.Tables(0).Columns
header = header & Chr(34) & 
col.ColumnName & Chr(34) & ","
Next
header = header.Substring(0, header.Length - 1)
End If
For Each row As DataRow In ds.Tables(0).Rows
Dim arr() As Object = row.ItemArray()
For i As Integer = 0 To arr.Length - 1
If arr(i).ToString().IndexOf(",") > 0 Then
record = record & Chr(34) & 
arr(i).ToString() & Chr(34) & ","
Else
record = record & arr(i).ToString() & ","
End If
Next
body = body & record.Substring(0, record.Length - 1) & 
vbCrLf
record = ""
Next
If exportcolumnheadings Then
Return header & vbCrLf & body
Else
Return body
End If
End Function

Here,

• We first check if column headings are to be exported or no. If we want to export the column headings then we iterate through the columns collection of the DataTable and retrieve the column names.
• We store the column headings as a comma separated string in a variable called header
• Note that the column headings are enclosed in double quotes ("").
• We then iterate through each row of the DataTable
• We get an array of all the column values using ItemArray property of the DataRow class. This property returns an object array of all the values from a given row.
• We then iterate through this array and create a comma separated string consisting of column values
• Note that it is possible that our column values can contain comma themselves. We enclose such values in double quotes ("").
• We store this comma separated list into a variable called record.
• We keep on appending this record variable to another variable called body.
• Finally, we return header and body if column headings are to be exported else we simply return the body.

Using the CSVHelper class in the web form

Now, it's time to use the CSVHelper class in our web form. Write following code in the click event handler of the "Export to CSV" button.

Dim ds As DataSet = Session("myds")
Dim csv As New CSVHelper
Dim strData As String = 
csv.Export(ds, CheckBox1.Checked)
Dim data() As Byte = 
System.Text.ASCIIEncoding.ASCII.GetBytes(strData)
Response.Clear()
Response.AddHeader("Content-Type", "application/Excel")
Response.AddHeader("Content-Disposition", 
"inline;filename=customers.csv")
Response.BinaryWrite(data)
Response.End()

Here,

• We first retrieve the DataSet stored in the session variable
• We then create an instance of CSVHelper class
• We call the Export() method of CSVHelper by passing the DataSet and checked status of the Checkbox
• Note that the second parameter of the Export() method is controlling whether column headings are to be exported or no
• The return value of Export() method is a string. We convert this string into a byte array for later use. We did this conversion using ASCIIEncoding class from System.Text namespace
• We then clear the response buffer by calling Response.Clear() method. We need to do this because we want to prompt the user to download only the data we retrieved from Export() method
• We add two HTTP headers - Content-Type and Content-Disposition. These headers will decide the application that is supposed to deal with the data being sent and default file name for the file being downloaded. I have used Excel here because people often use it to work with CSV files. This is achieved by AddHeader() method of the Response object
• We then call BinaryWrite() method of Response object. This method will write our data to the response stream in raw format.
• Note that BinaryWrite() method expects byte array as the input and that is why we converted the exported data in byte array form previously.

Now, run the web form, check the checkbox if you want to export column headings and click on the "Export to CSV" button. You should be prompted for downloading the CSV file. The default name for the file will be customers.csv. Save the file on your local hard disk. Open the file in Excel or any text editor.

Summary

CSV is the most widely used data format for data interchange. In this article we developed a reusable subroutine to convert data from any DataTable into CSV format. Though we have used Customers table in our example with very few changes you can make the code work with any DataTable or even a DataView.

Update (23 September, 2005)

After release of the article some visitors expressed their concerns about the lengthy string concatenation being performed when the data is very large and its effect on memory and performance. A new class file called CSVHelperV2.vb has been added with the download which illustrates how to use System.Text.StringBuilder class to perform the same operation. StringBuilder class is better in terms of memory footprint and performance when you are doing very lengthy string concatenation.