Search code examples
excelms-accesscoldfusion

How do I create very large excel files from access database?

I have an issue right now where I believe my cold fusion code is trying to create a very large excel file after pulling records from an access database. I beleive it is timing out and giving a 500, null error. The cold fusion will need to process around 180,000+ records. Is there an efficient way of doing this?

Here is pretty much how it goes down:

    <cfquery datasource="datasource" >
Drop Table Person
</cfquery> 

<cfquery datasource="datasource" >
Update1rowtble
</cfquery>


<cfset Clist=  "FNAME,LNAME,etc..">

<cfquery name="q" datasource="datasource" >
R2A</cfquery>



<cffile action="WRITE" file="filepath" output='#toString(queryToXL(Q,Clist))#' addnewline="No" fixnewline="No">

 <cfquery name="q" datasource="source" >
R3
</cfquery>

<cffile action="WRITE" file="filepath2" output='#toString(queryToXL(Q,Clist))#' addnewline="No" fixnewline="No">

Thanks

Solution

  • Efficient is in the eye of the beholder.

    If you are simply building a csv or html file that you are streaming to the browser as an xls file, then you need to make sure you write a row of data and flush the response buffer in order to keep the connection with the browser alive.

    However, if you are building a giant excel file in memory and trying to send it down then you probably need to make some UI changes.

    Namely, have the UI queue a request for the report/file. Then let the browser go away and have the user come back later to check on it. You could even have a little ajax thing check on the queue every so often. For the server side of things, have a non-web process create the file and notify the queue when the job is complete.