Skip to main content
A
Anonymous
October 5, 2009
Question

Best Way To Extract Large Text Files From Query

  • October 5, 2009
  • 2 replies
  • 866 views

Using CF7/SQL Server 2000.  I need to extract about 50,000 records from another server, and save it to a .CSV file.  Not sure what the best method is for this.  I've got the query written, and it will pull all the records.  But don't want to display them on the screen, just store in a .CSV file somewhere on the server.

I've looked at CFFILE and CFDIRECTORY, and those don't seem to be the best tools for this.  What about using CFCONTENT with CFHEADER?  I use that to output to Excel files

<CFCONTENT type="application/vnd.ms-excel">
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=c:\mydir\myfile.xls">

But when trying to use it, it doesn't save the file on my drive.  Would appreciate advice on the best tools/tags for extracting large volumes of data from other servers, and saving to a local file.

Once working, ultimately, I'd like to set it up, so the .CFM job runs daily, automatically, extracts the data and FTP's it to another server.  I've seen the CFFTP tag, but until I can get a file saved on my drive, or another server's drive, there's nothing to FTP.  Thanks for any help/advice.

Gary

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
October 5, 2009

What did you not like about cffile?

ilssac
ilssac
Inspiring
October 5, 2009

<cfconent...> and <cfheader...> are for delivering specified mine types to a client.

Your first idea was the correct one, <cffile....> is what you want to use to write content to the local server.  I do not understand why you feel this was not the best tool.

A
Anonymous
October 6, 2009

Thanks, but I can't find any good examples of using CFFILE (just syntax of the tag, which doesn't make a lot of sense).  I've been writing CF code for 8 years, and feel I can make it sing and dance.  But I didn't understand how CFFILE worked, from reading the online documentation and my Ben Forta books.  There are no good examples.  Do you still write the query with CFQUERY, then "substitutute" CFFILE for CFOUTPUT?  Or enclose CFFILE inside CFOUTPUT?  I can't find any examples that explain this.

I just need to see a basic example of CFFILE "in action."  Starting with a simple query, and a simple output that writes the query results to a file.

Lastly, once you have the data written to a file, using CFFILE, is that when you can use CFFTP, to FTP that file, (or any file on the hard drive for that matter) to another server?

Thanks for help, and any simple examples, just to get me started.  Thanks.

Gary

D
Dan_Bracuk
Inspiring
October 6, 2009

Here is one way.  Surprisingly, the white space in the source code affected the file content.

<cfsavecontent variable="xx"><cfoutput query="x">#field1#|#field2#
</cfoutput></cfsavecontent>


<cffile file="#TheFile#" action="write" addnewline="no" output="#xx#" nameconflict="overwrite">

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