Need Help in report

[deep_this]

Hi All,

There is a current funcationality for exporting the value of one entity being selected from a drop down and exporting to excel. This has limited number of records, so excel is able to handle this.

But now the need is to select an option that says "select all entitites", When I try to export this to excel, it just hangs and does nothing. The total number of records is definitely more than 65000 which is the maximum that excel spreadsheet can handle.

Can you please give some options or suggestions on how I can achieve to export this huge number of records to any other format other than excel.

Will exporting to PDF work?

Please reply asap.

Thanks

[deep_this]

need reply urgently please comment

[Adam Cameron.]

If you need help •urgently•, engage a CF consultant. Or raise a support ticket with Adobe.

--

Adam

[deep_this]

thanks a lot for your reply.

[Adam Cameron.]

thanks a lot for your reply.

Cheers.  Although I suspect there was an element of sarcasm in yours.

My advice was sound: if you need help urgently, and you're not getting it here, then it might be an idea to pursue other channels.  And also an urgent issue might need a bit of urgency on your part as well as ours, eh?  An easy way to get something solved urgently is to throw money at it.

Anyway...

What's the purpose of this export?  Your post is a bit light on detail, so it's difficult to give a sensible answer.

Can you not export the recordset to more than on Excel file?

You could spool it out to a CSV file or something, maybe... CSVs don't have a size limit, obviously.

Or maybe some sort of XML?

It's difficult to be sure, but I really don't see a PDF as a sensible format for exported data..?

--

Adam

[-__cfSearching__-]

What's the purpose of this export?

Your post is a bit light on detail,

so it's difficult to give a sensible answer.

Looking at the original post, I had similar questions myself. In addition

1) What tool are you currently using to generate this export?

2) How much data are you talking about?

3) How frequently does this export need to occur?

4) What database are using? ie What tools do you have at your disposal.

There are probably multiple options available. But you need to provide more information before we could suggest a reasonable alternative.

-Leigh

[deep_this]

We are not using any tool or framework. we are using Coldfusion 9 and SQL Server Database  and it is a cfm page running cfqueries, querying SQL database to pull the records. And I am using the following code to export to excel. This works for lesser number of records (between 20000 to 30000) but I need to export some where around 1 million records.

<cfheader name="Pragma" value="">

<cfheader name="Cache-control" value="">

<cfcontent type="application/vnd.ms-excel">

<cfheader name="Content-Disposition" value="filename=AllTeachers.xls">.

Thanks

[Joshua_Cyr]

You can just dump it to a .txt file.  The real thing you should be doing is to use a database tool to export this, not ColdFusion.  Any DBA at your company should be able to dump that data out for you in a couple minutes tops.

You an also use db tools like ems sql manager which has a good many export options (and import, etc)

http://www.sqlmanager.net/en/products/studio/mssql

I think they have an evaluation version which should serve fine for your testing needs.

[Adam Cameron.]

And what are you planning to do with these million records you export from the DB?

--

Adam

[deep_this]

Thanks for your replies, This is client requirement where the users need to be able to download the report selecting all teachers option and all classes option from the dropdown from the website.

Is there an option of being able to export them to multiple spread sheets. Can you please provide some example to which I can refer if any.

Thanks

[Guest]

If you want to create actual spreadsheets you'll want to use POI utliity I believe... Which is a pain in the rear if you ask me for simply exporting Excel.  I think CF 8+ has the ability built in but not entirely sure.

I bet I can offer a more interesting solution if you can express what the end result of the client is looking for... do they just want to browse their data?

Dave @ Oyova Software

http://www.oyova.com - Web Design and Development

[Owainnorth]

Adding to what's already been said here (and possibly repeating an earlier unanswered question) - what exactly do you intend on doing with this data? There's too much to scan through manually, Excel can't open it (except maybe new versions, but you can't rely on people having that); hell I doubt even Notepad would be able to fire it up in plain text.

Generally if you find yourself hitting these kind of limitations then your underlying idea is fundamentally flawed. As has been pointed out there are tools for bulk-export of data, and getting CF to read from a database and dump into Excel is almost certainly not the right medium.

[Guest]

Export it in a CSV format, you can have as many records as you want, but the file will be huge and again if you try to open with excel your limitted in the older versions of Excel, I believe the newer version have no issue with that limit.

But you can export your data as a report.csv and you can have as many rows as you want.

Dave @ Oyova Software

http://www.oyova.com - Web Design and Development