Exporting to .CSV 2

[Norman55]

I am trying to export data from an asp page into a csv file.
I can manage to export smaller files but as soon as anything over 20,000 records the first records are ok then I get this at the bottom off the page


<p>Response object</font> <font face="Arial" size=2>error 'ASP 0100 : 8007000e'</font>
<p>
<font face="Arial" size=2>Out of memory</font>
<p>
<font face="Arial" size=2>/dev/Test/Export.asp</font><font face="Arial" size=2> line 0</font>
<p>
<font face="Arial" size=2>Unable to allocate required memory.
</font>


Thanks in advance

 

[theniteowl]

Look here:

http://www.aspfaq.com/show.asp?id=2131

It's hard to think outside the box when I'm trapped in a cubicle.

 

[Norman55]

any other ideas?
that all seems ok ?

 

[theniteowl]

How are you pulling down the records?
Perhaps you can page the recordset so you pull it down in smaller chunks. Pulling 20,000 records all at once directly is not a good idea for performance if nothing else, if that is what you are doing. Without seeing code I am just guessing.


It's hard to think outside the box when I'm trapped in a cubicle.

 

[theniteowl]

Oh, and what happens if you just output to the screen rather than to a CSV file?

Is the CSV file being created on the server and then delivered to the client or is the file being output from the browser as CSV?


It's hard to think outside the box when I'm trapped in a cubicle.

 

[Norman55]

thats is what i am doing. how can we get around it ? i am pretty new to all this so any suggestions are welcomed.

 

[Norman55]

if i output to the screen it works fine it is just when i try and export to .csv

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%Response.AddHeader "Content-Disposition", "filename=EAData.csv;"%>
<%
Response.Clear
Response.Buffer = false
Response.ContentType = "application/csv"
%>
<%
Set mySearch = Server.CreateObject("ADODB.Recordset")

mySearch.Open "SELECT * FROM dbo.tblTest" , MyConn

%>

 

[Norman55]

Sorry I do apologise it does show the same error when outputting it to page

 

[Sheco]

What if you write the data to a text file using FSO and then redirect the browser to the file?

 

[Norman55]

im sorry to sound sstupid but how would i do that ?

 

[Sheco]

Something like this:
[tt]
dim fso, txtNew, txtNewName
txtNewName = 'name of new file
set fso=Server.CreateObject("Scripting.FileSystemObject")
txtNewName = fso.GetTempName
set txtNew=fso.CreateTextFile(txtNewName, True, False)

Dim fld, row
Do While Not MySearch.EOF
row = ""
For Each fld in MySearch.Fields
row = row & fld.value & ","
Next

'replace last comma with vbCrLF
row = left(row, len(row) -1) & vbCrLf

txtNew.Write row
mySearch.MoveNext
Loop
txtNew.close
set txtNew=nothing
set fso=nothing

mySearch.Close
Set mySearch = Nothing

Response.Redirect txtNewName
[/tt]

Of course then you'll need to create some sort of clean-up process to get rid of the old files.

 

[Norman55]

I think i am doing something wrong as i get the temp name in the address bar and page cannot be displayed?

 

[Sheco]

Hmmm, try inserting this line:[tt]
txtNewName = Left(txtNewName, len(txtNewName) -3) & "csv"
[/tt]
Immediately after this line:[tt]
txtNewName = fso.GetTempName [/tt]


And also change this line:[tt]
set txtNew=fso.CreateTextFile(txtNewName, True, False)[/tt]
To this:[tt]
set txtNew=fso.CreateTextFile(Server.MapPath(".") & "\" & txtNewName, True, False)[/tt]

 

[Norman55]

thanks
i will try that and let you know how i get on

 

[Norman55]

you are a real star that works beautifully thanks a lot

 

[Sheco]

ok, just be aware that this will leave a bunch of temp files on your server so you'll need a process to clean them up... perhaps a small script that is triggered by the windows scheduler or even by the ASP application_onStart event.

 

[Norman55]

dont want to push my luck as you have already helped me but dont suppose you have a script for that ?

 

[Sheco]

I dont but you could use the FileSystemObject for that and also I bet someone in the VBScript forum does have an example at least: forum329

The guys in that forum seem to concentrate more on system administration tasks and deleteing all files older than a certain date would seem to be more "on point" over there.

If you decide to run it as a scheduled job then just leave it as a .vbs file and if you want to trigger it from the application_onStart then it is pretty easy to convert from .vbs to ASP.

 

[Norman55]

how do i put a header row on the export?

 

[Sheco]

Stick a line in before the Do While Loop that writes the header to the file.

Something like:
[tt]txtNew.Write "blah,blah,blah" & vbCrLf[/tt]

Or if you want it to automatically adjust to whatever fields happen to be in your recordset then you can take part of the code above and modify it so that it writes the field name insteaed of the field value:[tt]
row = ""
For Each fld in MySearch.Fields
row = row & fld.[red]Name[/red] & ","
Next

'replace last comma with vbCrLF
row = left(row, len(row) -1) & vbCrLf

txtNew.Write row
[/tt]

... of course you'd also want to do this before the loop so you dont end up writing one line of header for each line in your resultset.