ColdFusion - How to convert table into CSV?

[ProjectDeadline365]

Hi -

Trying to query a database table and turn it into a CSV file... Is there a special ColdFusion tag or protocall for this??

I have a job posting for this at Guru.com - it should only take a couple hours.

http://www.guru.com/pro/ProjectDetail.aspx?ProjectID=762752

Sincere thanks,

--
Peter Ruppe

Account Director

Codex Creative

Tel 312.623.7349

1520 N. Damen | Suite A-1 | Chicago, IL 60622

Email | Peter.Ruppe@codexcreative.com

codexcreative.com

[Ron_Stewart]

Search cflib.org for csvformat; it is a custom tag that we use in several of our apps to turn queries into CSV... should be very straightforward to incorporate.

--

/ron

[Dan_Bracuk]

I'd simply use cffile.

Does your data contain any comma's?

[ProjectDeadline365]

Oooo.. good point! We would have to format te output correctly for the description column then. What would be the command for that?

Would I have to do a cfloop to get all the lines?

I'm trying, but am getting errors. ... This seems like it should be easier to do than what i'm experiencing.

[Dan_Bracuk]

Don't ignore the first reply.  That function might be just what you are looking for.

[Dan_Bracuk]

Here is a quick and dirty way to do it with cffile.  Pay attention to carraige returns in your source code.  cfsavecontent picks them up.

<cfquery name="x" datasource="something">
select seq f1
, 'text, with comma' f2
, current_date f3

from u_100
where seq < 5
</cfquery>

<cfdump var="#x#" metainfo="no">

<cfprocessingdirective suppresswhitespace="yes">
<cfsavecontent variable="content">
<cfoutput query="x">"#f1#","#f2#","#DateFormat(f3, 'yyyy-mm-dd')#"#chr(10)#</cfoutput>
</cfsavecontent>
</cfprocessingdirective>

<cffile file="D:\DW\dwweb\work\aa.csv" action="write" nameconflict="overwrite" output="#content#" addnewline="no">

[Ron_Stewart]

Here's another snippet, based on CSVFormat() from cflib.org:

<cfinclude template="/path/to/csvformat.cfm" />

<cfquery name="qryExport" dataSource="#application.dsn#">

          SELECT

                    ...

          FROM

                    ...

</cfquery>

<cfset variables.csvPath = ExpandPath("./relative/path/to/file.csv") />

<cfset variables.csvContent = CSVFormat(qryExport, """") />

<cffile action="write" file="#variables.csvPath#" output="#variables.csvContent#" />

I like it because it hides the messiness of turning the query results into the resulting comma-separated content, it includes column names, and you can provide an additional optional argument to the function specifying which columns from the query are to be included in the result. In addition, if you have several places where you need to do the same kind of thing, you also have some assurance they are being handled consistently.

--

/ron

[Dan_Bracuk]

Regarding,

"it includes column names"

Interesting point.  To include column names or not to include column names depends on, once you produce the csv file, for what will it be used?