Creating an Excel file from table with 40,000 records

[asdren_one]

I need help with creating an Excel file for client that has a table of 40,000 records. I have code that generates the Excel and has worked well in the past but with this much data it is timing out.

I've already informed the client that Excel has limit of 66000 records. So it might be better to export to CSV as the data in this table is going to keep growing.

If you have time to work with me on this , contact me a alexagui [at] gmail [dot] com
and I can send you more details so you can put together an accurate quote.

Thanks,
Alex

[Newsgroup_User]

asdren_one wrote:
> I need help with creating an Excel file for client that has a table of 40,000
> records. I have code that generates the Excel and has worked well in the past
> but with this much data it is timing out.
>
> I've already informed the client that Excel has limit of 66000 records. So it
> might be better to export to CSV as the data in this table is going to keep
> growing.
>
> If you have time to work with me on this , contact me a alexagui [at] gmail
> [dot] com
> and I can send you more details so you can put together an accurate quote.

For so many records my guess is that the concatenation times the most
time so you'll need to use StringBuffer to build the string. Google for
coldfusion and stringBuffer:
http://www.stillnetstudios.com/2007/03/07/java-strings-in-coldfusion/

--
Mack

[RickEp]

Here is what you need from the DOCs

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_r-s_18.html

[Dan_Bracuk]

If it's just one client, you should find out what he's doing with the file. Maybe it's something that you could do for him with Cold Fusion

Also, if you send a text file with more rows than Excel can handle, and he opens it with Excel, you've not done him any favours.

[asdren_one]

Mack, thanks for the link to the article. I'm going to give that a shot.

Dan, you're absolutely correct. But I have already warned them about the 66K Excel limit.

[-__cfSearching__-]

> I need help with creating an Excel file for client that has a table of 40,000 records

What is the client ultimately doing with the information and how often do they need to retrieve it? Depending on what it is needed for and your database type, you might also investigate database options. MS SQL has features like openrowset, etcetera which are capable of interacting with Excel (ie import/export)

[fober1]

Hi,

You can attach the RequestTimeout variable to the URL, to extend the timeout.
But to avoid a long wait for the page, you could create the Excel file once a day in a batch run, and just provide a link to the file in the page.

To create the Excel file, I would always recommend to use the Apache POI API, because using the POI the date-fields are date-format, and number fields are number-format, which is very important when any Excel formulas or filters are supposed to be used later.

Cheers,
fober