Re: Writing query to a csv file
you will want to use cffile action=append http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=0246.htm http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=0246.htmwrite will create the file append will add to it 2009/12/9 coldfusion.develo...@att.net I have a need to write the output from aquery to a csv file without headers. I've seen many examples where you can write the output from a query to a CSV file. However most of the examples I've seen require that the file be created first with headers and then then ourput from the query is appended. I've tried to skip the file create and header insertion but the data doesn't write to the csv correctly. Any advice? cfquery name=get_all datasource=#datasource# maxrows=55 select * from Employee_main /cfquery !--- Create the file with column header's. It is crucial that you have all the outputed text on one line with no spaces between the comma and the next column header.--- cffile action=write file=D:\Inetpub\wwwroot\HR\employee\cfmscripts\contacts.csv output=ed_first_name, ed_last_name, ed_fi_last_name, ed_title, ed_email, ed_reports_to, ed_department, ed_division, ed_location, ed_phone_number, ed_extension, ed_submitted, ed_state, ed_country, ed_zip_code addnewline=yes !--- Output the contents of the export_contacts query --- cfoutput cfloop query=get_all #TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed_title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)#,#TRIM(ed_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_extension)#,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(ed_country)#,#TRIM(ed_zip_code)# BR / !--- Append (Insert) the contents of the query into the already created CSV file. Make sure that you keep all the output on one line exactly like the column header--- cffile action=WRITE file=D:\Inetpub\wwwroot\HR\employee_directory\cfmscripts\contacts555.csv output=#TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed_title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)#,#TRIM(ed_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_extension)#,#TRIM(ed_submitted)#,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(ed_country)#,#TRIM(ed_zip_code)# addnewline=yes /cfloop /cfoutput ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329005 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Writing query to a csv file
Well.. The code below would uh... Create the same file over and over with only the last row... Unless I'm reading it wrong ;) What you needs is something like Cfsavecontent variable =csvcfoutput query=get_all ##TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed _title)#,#TRIM(ed_email)#, And so on /cfsavecontent Cffile write=c:\blah.csv output=#trim(csv)#/ You can't use html (like br) in a csv.. Instead you use actual line breaks (chr(10) or chr(10)chr(13) for windows). But the code above will add those because they are created when you code and hit enter :) Good luck. You will get this - it's not too tough. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: coldfusion.develo...@att.net [mailto:coldfusion.develo...@att.net] Sent: Wednesday, December 09, 2009 8:34 AM To: cf-talk Subject: Writing query to a csv file I have a need to write the output from aquery to a csv file without headers. I've seen many examples where you can write the output from a query to a CSV file. However most of the examples I've seen require that the file be created first with headers and then then ourput from the query is appended. I've tried to skip the file create and header insertion but the data doesn't write to the csv correctly. Any advice? cfquery name=get_all datasource=#datasource# maxrows=55 select * from Employee_main /cfquery !--- Create the file with column header's. It is crucial that you have all the outputed text on one line with no spaces between the comma and the next column header.--- cffile action=write file=D:\Inetpub\wwwroot\HR\employee\cfmscripts\contacts.csv output=ed_first_name, ed_last_name, ed_fi_last_name, ed_title, ed_email, ed_reports_to, ed_department, ed_division, ed_location, ed_phone_number, ed_extension, ed_submitted, ed_state, ed_country, ed_zip_code addnewline=yes !--- Output the contents of the export_contacts query --- cfoutput cfloop query=get_all #TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed_ title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)#,#TRIM(e d_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_extension) #,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(ed_country)#,#TRIM(ed_zip_code )# BR / !--- Append (Insert) the contents of the query into the already created CSV file. Make sure that you keep all the output on one line exactly like the column header--- cffile action=WRITE file=D:\Inetpub\wwwroot\HR\employee_directory\cfmscripts\contacts555.csv output=#TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,# TRIM(ed_title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)# ,#TRIM(ed_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_ex tension)#,#TRIM(ed_submitted)#,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(e d_country)#,#TRIM(ed_zip_code)# addnewline=yes /cfloop /cfoutput ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329006 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Writing query to a csv file
I just did this and used one of the functions on cflib.org, one of the Query2CSV ones. I did modify it to remove extra line breaks, trim, etc. from the cell data, Phil On Wed, Dec 9, 2009 at 10:14 AM, AJ Mercer ajmer...@gmail.com wrote: you will want to use cffile action=append http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=0246.htm http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=0246.htm write will create the file append will add to it 2009/12/9 coldfusion.develo...@att.net I have a need to write the output from aquery to a csv file without headers. I've seen many examples where you can write the output from a query to a CSV file. However most of the examples I've seen require that the file be created first with headers and then then ourput from the query is appended. I've tried to skip the file create and header insertion but the data doesn't write to the csv correctly. Any advice? cfquery name=get_all datasource=#datasource# maxrows=55 select * from Employee_main /cfquery !--- Create the file with column header's. It is crucial that you have all the outputed text on one line with no spaces between the comma and the next column header.--- cffile action=write file=D:\Inetpub\wwwroot\HR\employee\cfmscripts\contacts.csv output=ed_first_name, ed_last_name, ed_fi_last_name, ed_title, ed_email, ed_reports_to, ed_department, ed_division, ed_location, ed_phone_number, ed_extension, ed_submitted, ed_state, ed_country, ed_zip_code addnewline=yes !--- Output the contents of the export_contacts query --- cfoutput cfloop query=get_all #TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed_title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)#,#TRIM(ed_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_extension)#,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(ed_country)#,#TRIM(ed_zip_code)# BR / !--- Append (Insert) the contents of the query into the already created CSV file. Make sure that you keep all the output on one line exactly like the column header--- cffile action=WRITE file=D:\Inetpub\wwwroot\HR\employee_directory\cfmscripts\contacts555.csv output=#TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed_title)#,#TRIM(ed_email)#,#TRIM(ed_reports_to)#,#TRIM(ed_department)#,#TRIM(ed_division)#,#TRIM(ed_location)#,#TRIM(ed_phone_number)#,#TRIM(ed_extension)#,#TRIM(ed_submitted)#,#TRIM(ed_submitted)#,#TRIM(ed_state)#,#TRIM(ed_country)#,#TRIM(ed_zip_code)# addnewline=yes /cfloop /cfoutput ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329008 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
