Link to home
Start Free TrialLog in
Avatar of MikeXna
MikeXna

asked on

What is the best way to output a Cold Fusion Query to a CSV or XML file

CFFILE, CFDUMP?
What is the best way to query a database for all records and cleanly output it to a file?
I recently attempted to use CFFile to export to CSV using the following code below, however the csv was not clean.  The Rows (records), and Columns (fields) were not consistnatly perserved.  Some of my fields contain HTML which I need to keep.

Any advice is greatly appreciated.
Thanks!
<!--- 

<cfif exporting.recordcount GTE 66000>
    <!--- Excel can only handle 66000 rows --->
    Too many records

<cfelse>

    <!--- 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="filepath\filename.csv"
             output="id,title,body"
             addnewline="yes">

    <!--- Output the contents of the export_contacts query --->
    <cfoutput>
        <cfloop query="exporting">
    
        <!--- 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="append"
                 file="filepath\filename.csv"
                  output="#TRIM(ID)# , #TRIM(Title)# , #TRIM(Body)#"
                 addnewline="yes">

        </cfloop>
    </cfoutput>

</cfif>
--->

Open in new window

SOLUTION
Avatar of dgrafx
dgrafx
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial