how bout this...
loop over the query and create a cr/lf delimited list with each element
being a comma delimited list of field values then just dump the list to the
file.
<cfset
csvfile="//kcweb/firmdaily/isweb/hbimport/"&#rtrim(GetAuthors.AUTHOR)#&"_FIR
ST.csv">
<cfset crlf = "#Chr(13)##Chr(10)#">
<cfset fields="DOCNUM,VERSION,FULLPATH,etc.">
<cfset csvdata="">
<!--- loop over query --->
<cfloop query="GetDocs">
<cfset tmp="">
<!--- loop over fields list--->
<cfloop index="i" list="#fields#">
<cfset val=Evaluate("GetDocs.#i#")>
<cfset tmp=ListAppend(tmp,"""#val#""")>
</cfloop>
<!--- new line --->
<cfset csvdata = ListAppend(csvdata,tmp,crlf)>
</cfloop>
<cffile action="write" file="#csvfile#" output="#csvdata#"
nameconflict="OVERWRITE">
This should be faster than doing an append to the file for every record but
I don't know how it
would handle half a million records as the csvdata variable would be chewing
through some
RAM, you might add a check after the new line is added and if
ListLen(csvdata) is greater
than some number you could append to the file and clear the list.
HTH
Robert Gatton
----- Original Message -----
From: "Gieseman, Athelene" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, March 29, 2002 10:31 AM
Subject: Creating a CSV File
> I have created a CF page which uses CFFILE to read data from a table and
> create a .csv file from that data. It works. The problem is that it is
> very slow. Is there anything I can do to create this file without having
to
> open, append and close the file for each line? Or is there some other
> reason it is taking so long? I'll have about a half a million records for
> all the various csv's I need to create next week. Any suggestions would
be
> very much appreciated!
>
> The code is as follows:
>
> <cfset
>
csvfile="//kcweb/firmdaily/isweb/hbimport/"&#rtrim(GetAuthors.AUTHOR)#&"_FIR
> ST.csv">
>
> <cfoutput query="GetDocs">
> <cffile action="APPEND" file=#csvfile#
>
output="#chr(34)##rtrim(GetDocs.DOCNUM)##chr(34)#,#chr(34)##rtrim(GetDocs.VE
>
RSION)##chr(34)#,#chr(34)##rtrim(GetDocs.FULLPATH)##chr(34)#,#chr(34)##rtrim
>
(GetDocs.DEPT)##chr(34)#,#chr(34)##rtrim(GetDocs.CATEGORY)##chr(34)#,#chr(34
> )##rtrim(GetDocs.newc1)##chr(34)#,#chr(34)##rtrim(GetDocs.newc2)##chr(34)#
,#
>
chr(34)##rtrim(GetDocs.operator)##chr(34)#,#chr(34)##rtrim(GetDocs.editdate)
>
##chr(34)#,#chr(34)##rtrim(GetDocs.edittime)##chr(34)#,#chr(34)##rtrim(GetDo
>
cs.author)##chr(34)#,#chr(34)##rtrim(GetDocs.createdate)##chr(34)#,#chr(34)#
>
#rtrim(GetDocs.createtime)##chr(34)#,#rtrim(GetDocs.docname)#,#chr(34)##rtri
>
m(GetDocs.comments)##chr(34)#,#chr(34)##rtrim(GetDocs.T_Alias)##chr(34)#,#ch
> r(34)##rtrim(GetDocs.C_Alias)##chr(34)#" addnewline="Yes">
>
> </cfoutput>
>
>
>
>
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
