Write Coldfusion query results to a text file not working

Question

<cfquery name="writefile" datasource="#dsn#">
    SELECT abc,def,pqr,stu,zex
    FROM mytable
</cfquery>


<cfoutput>
    <table>
        <cfloop query="writefile">
            <tr>
                <cfloop list="#ArrayToList(writefile.getColumnNames())#" index="col">
                    <cffile action="write" file="d:\test.txt"      output="#writefile[col][currentrow]#"> 

                </cfloop>
            </tr>   
        </cfloop>
    </table>

</cfoutput>

I am using the above code to write a text file to a location using cffile.

But the text file is not containing all the results of the query. Please guide me.

Answer 1

Using cffile action="write" will reset the file each time.

Use action="append" to add content to a file without first blanking the file.

You should also consider building the string first, then writing to the file in a single action.

For example:

<cfset Content = "" />
<cfloop query="writefile">
    <cfloop array=#writefile.getColumnNames()# index="col">
        <cfset Content &= ' ' & writefile[col][currentrow] />
    </cfloop>
    <cfset Content &= chr(10) />
</cfloop>

<cffile action="write" file="d:\test.txt" output="#FileContent#" />

(Note: string concatenation used for simplicity - if performance matters, consider using StringBuilder and/or cfsavecontent instead.)