Exporting your data to CSV forma
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.