Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to export DataTable to CSV file in ASP.NET

Author: Rachel Nicole
Download Source Code : 1330_ExportDataTableToCSVFile.zip

The purpose of this article is to explain how we can export DataTable to CSV file.

 

Comma Separated Values (CSV) file, which is also known as Comma Delimited File, is a specially formatted file. It can contain table or spreadsheet like data with very simple format. Every record is separated from other with a defined separator. Most commonly, comma (,) is used to separate each record. We can simply represent a DataTable in a CSV file. 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual Basic or Visual C#  > ASP.NET Empty Web Site
  4. Right click on web site > Add New Item > Web Form
  5. Add a Button in Web Form

    <asp:Button ID="Button1" runat="server" Text="Convert DataTable to CSV File"
           onclick="Button1_Click" />
     
  6. Open code behind file and include following namespaces

    C#
     
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
     
  7. Write below function in code behind file to get data from NORTHWIND database

    C#
     
    protected DataTable GetCustomersData()
    {
        string cmdText = "SELECT * FROM Customers";
        string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
        SqlConnection connection = new SqlConnection(myConnection);
        SqlCommand command = new SqlCommand(cmdText, connection);
        connection.Open();
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        da.Fill(dt);
        connection.Close();
        return dt;
    }
     
     
    VB.NET
     
    Protected Function GetCustomersData() As DataTable
        Dim cmdText As String = "SELECT * FROM Customers"
        Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
        Dim connection As New SqlConnection(myConnection)
        Dim command As New SqlCommand(cmdText, connection)
        connection.Open()
        Dim da As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        da.Fill(dt)
        connection.Close()
        Return dt
    End Function
     
    Here is the explanation of above function
     
    I am using Customers table in NORTHWIND database to get data.
    GetCustomersData() function returns Datatable.
    Write query to get data from database table.
    Establish your connection to the server.
    Create SqlCommand object by passing query and SqlConnection object.
    Get data in SqlDataAdapter object.
    Create DataTable Object and fill DataTable.
    Return DataTable object.
     
  8. Write below function in code behind file to export DataTable to CSV file

    C#
     
    protected void ExportDataTableToCSV()
    {
        DataTable dt = GetCustomersData();
     
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AddHeader("content-disposition", "attachment;filename=Customers.csv");
     
        StringBuilder sb = new StringBuilder();
     
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append(dt.Columns[i].ColumnName + ',');
        }
        sb.Append(Environment.NewLine);
     
        for (int j = 0; j < dt.Rows.Count; j++)
        {
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                sb.Append(dt.Rows[j][k].ToString() + ',');
            }
            sb.Append(Environment.NewLine);
        }
        Response.Write(sb.ToString());
        Response.End();
    }
     
    VB.NET

    Protected Sub ExportDataTableToCSV()
        Dim dt As DataTable = GetCustomersData()
     
        Response.Clear()
        Response.ContentType = "text/csv"
        Response.AddHeader("content-disposition", "attachment;filename=Customers.csv")
     
        Dim sb As New StringBuilder()
     
        For i As Integer = 0 To dt.Columns.Count - 1
            sb.Append(dt.Columns(i).ColumnName + ",")
        Next
        sb.Append(Environment.NewLine)
     
        For j As Integer = 0 To dt.Rows.Count - 1
            For k As Integer = 0 To dt.Columns.Count - 1
                sb.Append(dt.Rows(j)(k).ToString() + ",")
            Next
            sb.Append(Environment.NewLine)
        Next
        Response.Write(sb.ToString())
        Response.End()
    End Sub
     
    Here is the explanation of above function.
     
    Call GetCustomersData() function and assign data to a DataTable object.
    Set the ContentType property of Response object for CSV file.
    Call the AddHeader() method of Response object. The parameters provided in AddaHeader("content-disposition", "attachment;filename=Customers.csv") method will force the browser to download the specified file.
    Create an object of StringBuilder class.
    Write a “For” loop to loop through DataTable columns.
    Call the Append() method of StringBuilder object and get the Column Names separated with comma (,).
    Call Append() method again to for newline.
    Write a “For” loop to loop through each DataTable rows.
    Write a nested “For” loop to through every column.
    Now call Append() method again and  to get data of each record separated with comma (,) again.
    Call Append() method for new line once again.
    Now call Write() method of Response object which writes string to output stream.
    End() method will send all currently output to client and stop execution.
     
  9. Write Button click event to call function
     
    C#

    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportDataTableToCSV();
    }
     
    VB.NET

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        ExportDataTableToCSV()
    End Sub
     
  10. View web site in browser 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< LINQ to XML in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net