A faster technique would be to select each line directly in the SQL query.
Like here:
<CFQUERY NAME="GetDocs"...>
SELECT
'"' + RTRIM(CATEGORY) + '"' + ',' +
'"' + RTRIM(newc2) + '"' + ',' +
...
'"' + RTRIM(C_Alias) + '"'
AS line
FROM table
</CFQUERY>
<!--- create the file content ---->
<CFSET filecontent = ValueList(GetDocs.line,"#CHR(13)##CHR(10)#")>
<!--- and save it --->
<cffile action="APPEND" file="#csvfile#" output="#filecontent#">
the query will not select lines with NULL fields if you use MS SQL server.
In that case you should use the COALESCE function for each field that might
be null:
COALESCE(field,'')
Also you may need to convert numeric or date type fields to nvarchar.
HTH
Marian
> -----Original Message-----
> From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]]
> Sent: Friday, March 29, 2002 10:31 AM
> To: CF-Talk
> 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.AUTH
> OR)#&"_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)##r
> trim(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)##ch
> r(34)#,#ch
> r(34)##rtrim(GetDocs.C_Alias)##chr(34)#" addnewline="Yes">
>
> </cfoutput>
>
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
