Best Way To Extract Large Text Files From Query

[Guest]

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

[ilssac]

<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.

[Guest]

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

[Dan_Bracuk]

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">

[Dan_Bracuk]

What did you not like about cffile?