Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Exporting data into CSV format in asp.net

Anybody ever done like exporting your dataset or datareader data into a csv file(perferably "in memory" - not writting into hard disck of web server) and then response.redirect to the generated file so that user can download or save this file into their hard disk?

Thanks

RAM
Friday, April 18, 2003

1) Put a button on your page that calls the "Export to CSV" function.

2) In the ExportToCSV function, you do something like.

Response.Clear(); // just in case there's already output
Response.ContentType = "text/plain"
Response.AddHeader("Content-Disposition:attachment;filename=download.csv");

// multiple Response.Write statements outputting the CSV data

Response.End();


There ya go.  100% in-memory.

Richard Ponton
Friday, April 18, 2003

Now, if your intent is to get the data into Excel, then you can just set the ContentType to "application/vnd.ms-excel", add the Content-Disposition header, and then Render your datagrid directly to a StringWriter and output that.

Excel understands HTML tables.

Richard Ponton
Friday, April 18, 2003

Thanks for the info

looks like addheader method is going away and we need to use appendheader instead. here is my code block

        Public Sub dumpCSV(ByVal AddressReader As System.Data.OleDb.OleDbDataReader)
            Response.Clear()
            Response.ContentType = "text/plain"
            Response.AppendHeader("Content-Disposition","attachment")
            Response.AppendHeader("filename","download.csv")
                While AddressReader.Read()
                    Response.write(AddressReader.getvalue(0))
                Response.write(AddressReader.getvalue(1))
                Response.write(AddressReader.getvalue(2))               
                End While
                Response.End()
                AddressReader.Close()
                AddressReader = Nothing
       
        End Sub

When I run this, it just emits all the output into browser with clear text. I was expecting that browser will provide usual "save dialog box". any idea what I am missing?

Thanks in advance
Ram

RAM
Monday, April 21, 2003

You're not going to be able to force a 'save as' box, except possibly by creating a temp file and displaying a link to it. But if you want IE to open it in Excel instead of showing plain text, use Excel's MIME type for the ContentType.

Dave Rothgery
Monday, April 21, 2003

The HTTP header for Content-Disposition includes the filename.  It's one header, not two seperate ones.

Response.AppendHeader("Content-Disposition", "attachment;filename=" + theFileName)

Certain combinations of IE, Windows, and Office ignore this header and embed office into IE anyhow.  Sorry.  Also, with certain version of IE,Windows,and Office, HTTPS can be problematic as IE will pass the URL directly to Office instead of downloading a temp file.  Office will then try and access the URL and fail because it's behind an HTTPS authenticated site.

Richard Ponton
Monday, April 21, 2003

Richard,

Thanks for your input. Header worked when made as one. But I am planning to  add "," and store it in temp folder in the web server. Either I will give a link or force the "save dialog" box by response.redirecting the temp file. Then a nighly batch job will clear all the temp files.

My next concern is: though csv is open format, when user try to open, it will be opened in Excel(if they have MS office installed in their PC). The amount of data what I am talking about could be more than 65K and Excel will ignore any data which is more than 65K which means a problem!! anybody encountered this eariler??

Ram

RAM
Tuesday, April 22, 2003

Temp files present other problems.

The moment you let your webserver have write access anywhere on your served pages, you have a security risk.

How can you gaurantee that you'll have enough disk space?  What if a badly or maliciosly written program hits your script over and over again?

What if someone moves your application to another webserver (say, after you're no longer working in the area, for good or ill)?  Will they remember to set up the nightly job to clear out temp folders?

I say, avoid temp files like the plague.  If there's a way to avoid them, avoid them.  Sometimes you can't (like using System.Web.Mail to send attachments).

If your output is producing output that excel can't handle, just what do you think can handle it?  Maybe you should use a different format, like XML (not that excel will be able to handle a humongous XML file either).

If you are outputting CSV data and you don't want excel to handle it, then don't use .CSV as the extension.  Pick a new extension, like ".BIGCSV".

Richard Ponton
Tuesday, April 22, 2003

You are right about temp files and I thought about this. But here is another situation. Assuming I am writting the big file "in memory" and forcing the user to save the file. If for some reason(may be due to network connectivity at their end), they are not able to save the file. In this case, I need to recreate the file again all over again. If I store them in a file, they can always go back and try saving again.

I guess I can save these files in another box. But having access to write into another box is equally dangerous..

I can create more than one file if count goes beyond the 65K limit as csv is the only option left to us

RAM
Wednesday, April 23, 2003

*  Recent Topics

*  Fog Creek Home