Exporting a DataSet to a CSV File in ASP.Net

 

 

 

 

 

 

 

If you have the need to export data from a SQL Server Dataset to a CSV file through your website application, you can write to the response object in the Page_Init function of your page to produce the file. The user will not see the page at all but will be prompted to open or save the file on their desktop.

Here is the Page_Init function with the Response Write that you will need:

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        Dim objData As System.Data.DataSet = New System.Data.DataSet

        Dim objCSV As String = BuildCSV(objData)
        Dim objFilename As String = "Excel" & Today.ToString("yyyy-mm-dd") & ".csv"

        With Response
            .AddHeader("Content-disposition", "attachment;filename=" & objFilename)
            .ContentType = "text/plain"
            .Write(objCSV)
            .End()
        End With
        
    End Sub

And here is the supporting function to return the DataSet as a String:

Public Shared Function BuildCSV(ByRef objData As System.Data.DataSet) As String

        Dim objList As System.Data.DataSet = objData
        Dim objCSVOverall As New System.Text.StringBuilder
        Dim objCSVRow As New System.Text.StringBuilder

        '*** Build the header row
        For intCount As Integer = 0 To objList.Tables(0).Columns.Count - 1
            Dim CurrentColumn As System.Data.DataColumn = objList.Tables(0).Columns(intCount)
            objCSVRow.Append(Chr(34) & CurrentColumn.ColumnName & Chr(34) & ",")
        Next

        objCSVOverall.AppendLine(objCSVRow.ToString.Substring(0, objCSVRow.ToString.Length - 1))

        '*** iterate through the rows
        For Each CurrentRow As System.Data.DataRow In objList.Tables(0).Rows
            objCSVRow = New System.Text.StringBuilder

            For intCount As Integer = 0 To objList.Tables(0).Columns.Count - 1
                Dim objValue As String = CurrentRow.Item(intCount).ToString
                Dim CurrentColumn As System.Data.DataColumn = objList.Tables(0).Columns(intCount)

                objCSVRow.Append(Chr(34) & objValue.Replace(Chr(34), "'") & Chr(34) & ",")
            Next

            objCSVOverall.AppendLine(objCSVRow.ToString.Substring(0, objCSVRow.ToString.Length - 1))
        Next

        Return objCSVOverall.ToString
    End Function