That's a really nice technique. - Matt Small
-----Original Message----- From: Marian Dumitrascu [mailto:[EMAIL PROTECTED]] Sent: Friday, March 29, 2002 11:09 AM To: CF-Talk Subject: RE: Creating a CSV File 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> > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
