Export to Excel for LARGE amounts of records!!!???

[dtkelly72]

Hi Y'all,

I have used cf_excel custom tag, and cffile solutions to export a large SQL query to an excel file, but when I am dealing with thousands of records, it freezes, or is WAY TO SLOW, on any machine that is not super fast.

So I need something that halls balls to excel...

Any Ideas?

Thanks,

Dom

 

[csteinhilber]

You're always going to be held at the whim of the machine that's doing the saving. Even a super fast machine can fall victim to a drive hiccup or a higher-priority process stealing cycles. And even if you get it working on a given machine, you're not allowing for any scaling (what happens when the dataset grows to twice the size it is now... and save time increases along with it).

Best practice suggests that you need to break up the resultset into several parts/categories, so you're not trying to save all those records at once. Then save the smaller chunks to either separate files, or the same file using

<CFFILE action=&quot;append&quot; ...>

rather than write.

Even if you can't break the resultset into logical categories, I would definitely recommend stepping through only a portion of the Excel data at a time and saving before moving onto the next portion.

I've never used CF_EXCEL, so I don't know exactly how it passes back data, but I'm assuming it would be a string that has some sort of line delimiter (carriage return, perhaps?). In which case, write out a couple hundred lines at a time:

<!--- initialize output file so it exists for appending --->
<CFFILE action=&quot;write&quot; file=&quot;myfile.txt&quot; output=&quot;column name 1#Chr(9)#column name 2#Chr(9)#column name 3#Chr(13)#&quot;>

<CFSET lineQty = 0>
<CFSET tempOutput = &quot;&quot;>
<CFLOOP index=&quot;whichLine&quot; list=&quot;#myExcelData#&quot; delimiters=&quot;#Chr(13)#&quot;>
  <CFSET tempOutput = ListAppend(&quot;#tempOutput#&quot;,whichLine,&quot;#Chr(13)#&quot;)>
  <CFSET lineQty = lineQty + 1>
  <CFIF lineQty GT 200>
     <CFFILE action=&quot;append&quot; file=&quot;myfile.txt&quot; output=&quot;#tempOutput#&quot;>
     <CFSET lineQty = 0>
     <CFSET tempOutput = &quot;&quot;>
  </CFIF>
</CFLOOP>

... or something like that. Adjust accordingly. Hope it helps,
-Carl

 

[dtkelly72]

Hey, thanks a lot, I'll check out this method.

Cheers,

Dom