vb.net - How do I best generate a CSV (comma-delimited text file) for download with ASP.NET?

Question

This is what I've got. It works. But, is there a simpler or better way?

One an ASPX page, I've got the download link...

<asp:HyperLink ID="HyperLinkDownload" runat="server" NavigateUrl="~/Download.aspx">Download as CSV file</asp:HyperLink>

And then I've got the Download.aspx.vb Code Behind...

Public Partial Class Download
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'set header
        Response.Clear()
        Response.ContentType = "text/csv"
        Dim FileName As String = "books.csv"
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName)

        'generate file content
        Dim db As New bookDevelopmentDataContext
        Dim Allbooks = From b In db.books _
                       Order By b.Added _
                       Select b
        Dim CsvFile As New StringBuilder
        CsvFile.AppendLine(CsvHeader())
        For Each b As Book In Allbooks
            CsvFile.AppendLine(bookString(b))
        Next

        'write the file
        Response.Write(CsvFile.ToString)
        Response.End()
    End Sub

    Function CsvHeader() As String
        Dim CsvLine As New StringBuilder
        CsvLine.Append("Published,")
        CsvLine.Append("Title,")
        CsvLine.Append("Author,")
        CsvLine.Append("Price")
        Return CsvLine.ToString
    End Function

    Function bookString(ByVal b As Book) As String
        Dim CsvLine As New StringBuilder
        CsvLine.Append(b.Published.ToShortDateString + ",")
        CsvLine.Append(b.Title.Replace(",", "") + ",")
        CsvLine.Append(b.Author.Replace(",", "") + ",")
        CsvLine.Append(Format(b.Price, "c").Replace(",", ""))
        Return CsvLine.ToString
    End Function

End Class

Answer 1

CSV formatting has some gotchas. Have you asked yourself these questions:

• Does any of my data have embedded commas?
• Does any of my data have embedded double-quotes?
• Does any of my data have have newlines?
• Do I need to support Unicode strings?

I see several problems in your code above. The comma thing first of all... you are stripping commas:

CsvLine.Append(Format(b.Price, "c").Replace(",", ""))

Why? In CSV, you should be surrounding anything which has commas with quotes:

CsvLine.Append(String.Format(""{0:c}"", b.Price))

(or something like that... my VB is not very good). If you're not sure if there are commas, but put quotes around it. If there are quotes in the string, you need to escape them by doubling them. " becomes "".

b.Title.Replace(""", """")

Then surround this by quotes if you want. If there are newlines in your string, you need to surround the string with quotes... yes, literal newlines are allowed in CSV files. It looks weird to humans, but it's all good.

A good CSV writer requires some thought. A good CSV reader (parser) is just plain hard (and no, regex not good enough for parsing CSV... it will only get you about 95% of the way there).

And then there is Unicode... or more generally I18N (Internationalization) issues. For example, you are stripping commas out of a formatted price. But that's assuming the price is formatted as you expect it in the US. In France, the number formatting is reversed (periods used instead of commas, and vice versa). Bottom line, use culture-agnostic formatting wherever possible.

While the issue here is generating CSV, inevitably you will need to parse CSV. In .NET, the best parser I have found (for free) is Fast CSV Reader on CodeProject. I've actually used it in production code and it is really really fast, and very easy to use!