Re: Writing query to a csv file

[Phillip Duba]

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  wrote:

>
> you will want to use cffile action="append"
>
>
> http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=0246.htm
>
> <
> http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=0246.htm
> >write
> will create the file
> append will add to it
>
> 2009/12/9 
>
> >
> > 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?
> >
> > 
> > select *
> > from Employee_main
> > 
> >
> > 
> > > 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">
> >
> >  
> >
> >
> >
> #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)#
> >
> >
> >   >
>  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">
> >
> >
> >
> >
> >
>
> 

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

RE: Writing query to a csv file

[Mark Kruger]

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


##TRIM(ed_first_name)#,#TRIM(ed_last_name)#,#TRIM(ed_fi_last_name)#,#TRIM(ed
_title)#,#TRIM(ed_email)#,  And so on




You can't use html (like ) 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?

 select *
from Employee_main 


 

 


#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
)#

  






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

[AJ Mercer]

you will want to use cffile action="append"

http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentation&file=0246.htm

write
will create the file
append will add to it

2009/12/9 

>
> 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?
>
> 
> select *
> from Employee_main
> 
>
> 
> 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">
>
>  
>
>
> #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)#
>
>
>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">
>
>
>
>
> 

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