Creating a .CSV file in ASP.NET

I wanting to create a .CSV file from a query to SQL Server in ASP.NET. The link below has an excellent description of creating the .CSV file without having to create a temp file like I was originally planning to do. This post doesn't show how to wrap the query results up, but that is pretty simple to figure out:

http://wiki.asp.net/page.aspx/401/export-to-csv-file/

Exporting ListView to CSV File in VB.NET

I have posted an article on copying a ListView to the Windows Clipboard, but I have also found it useful to export a ListView directly to a .CSV file (comma separated value file). The process to go from a ListView to the clipboard or to a CSV file is similar.

There are number of rules for creating a CSV file. Here is a good place to look if you want to see them all. I have simplified the process to a few rules that will work for most cases. I put quotes around all values and column names. Technically, this is not required, but by putting them around all cases eliminates the need to determine if you need them or not. I also replace any quote in the data with a double quote.

Public Function ExportListViewToCSV(ByVal filename As String, ByVal lv As ListView) As Boolean

Try

' Open output file
Dim os As New StreamWriter(filename)
 
' Write Headers

For i As Integer = 0 To lv.Columns.Count - 1
    ' replace quotes with double quotes if necessary
    os.Write("""" & lv.Columns(i).Text.Replace("""", """""") & """,")
Next

os.WriteLine()

' Write records
For i As Integer = 0 To lv.Items.Count - 1
   For j As Integer = 0 To lv.Columns.Count - 1
       os.Write("""" & lv.Items(i).SubItems(j).Text.Replace("""", """""")+ """,")
    Next

    os.WriteLine()

Next

os.Close()

Catch ex As Exception
' catch any errors
Return False
End Try

Return True

End Function

You may want to get the file name from the user and automatically open the file in Excel too. Here is an example of how to do that:

Public Sub TestExportToCSV()

Dim dlg As New SaveFileDialog
dlg.Filter = "CSV files (*.CSV)|*.csv"
dlg.FilterIndex = 1
dlg.RestoreDirectory = True

If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then

  If ExportListViewToCSV(dlg.FileName, ListView1) Then

      Process.Start(dlg.FileName)

  End If

End If

End Sub