Skip to main content
B
biene22
Inspiring
October 18, 2011
Question

Export millions of records to textfile

  • October 18, 2011
  • 3 replies
  • 1353 views

Hi everybody,

I'm using CF 9 Standard Edition and an Oracle 11g database .

In my CF-Application I have queries with millions of records which I want to export to a textfile (csv).

Is this possible in ColdFusion? If yes, what is the fastest way to do that?

I try to use UDF QueryToCSV2 of CFLIB.org (http://www.cflib.org/udf/QueryToCSV2)

and then export the data via <cffile action="write"> to a file,

but it takes too much time , so that  I get a Server error.

Any ideas?

Thanks in advance.

Claudia

    This topic has been closed for replies.

    3 replies

    D
    Dan_Bracuk
    Inspiring
    October 18, 2011

    No matter what you try, if you are working with great gobs of data, it's going to be slow.  It brings up a couple of questions.

    Is this a regular job or something that only has to be done once?

    What does the .csv file get used for?

    B
    biene22Author
    Inspiring
    October 19, 2011

    It is a regular job, so sqlplus isn't a solution.

    I now find a better way inside oracle database. I created  a procedure using UTL_FILE

    and it works fast enough.

    Thanks for your replies.

    Claudia

    Owainnorth
    Owainnorth
    Inspiring
    October 19, 2011

    Sounds spot on. If there's a way of doing something without getting another technology involved, it's almost always the way to go.

    Owainnorth
    Owainnorth
    Inspiring
    October 18, 2011

    Nope, ColdFusion is definitely not the best tool for the job and almost certainly will never work. Why not just use SQLPlus and spool to textfile?

    Sean Coyne
    Sean Coyne
    Participating Frequently
    October 18, 2011

    You can increase the timeout for the request using the cfsetting tag:

    <cfsetting requesttimeout="600" /> would be a 10 minute timeout.  You can set the timeout to any value you like.

    If you do not want to do that, you could try building the CSV string yourself using a Java StringBuffer which tends to create strings slightly faster than using native ColdFusion strings.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices