Quickly exporting a table into a CSV
I've just written a few lines of code which easily export the contents of a table into a CSV. This is rather handy if you clients need to download the content locally.
Here is the code:
<cfquery name="qGetApplicants" datasource="#application.defaultProperties.datasourceName#">
select *
from applicant
</cfquery>
<cfset csvstr = createObject("java","java.lang.StringBuffer")>
<cfset csvstr.append(qGetApplicants.columnList & "," & Chr(13) & Chr(10) )>
<cfloop query="qGetApplicants">
<cfloop list="#qGetApplicants.columnList#" index="thisColumn">
<!-- remove carrage commas -->
<cfset removedCarageReturns = REReplace(evaluate(thisColumn),'#chr(13)#|\n|\r','','ALL') />
<!-- remove line returns -->
<cfset csvstr.append(replace(removedCarageReturns,",","","all") & ",") />
</cfloop>
<cfset csvstr.append(Chr(13) & Chr(10))>
</cfloop>
<cfheader name="Content-Disposition" value="attachment; filename=ReportApplicantsExport.csv">
<cfcontent type="application/msexcel" variable="#ToBinary( ToBase64(csvstr.toString()) )#" reset="Yes">
select *
from applicant
</cfquery>
<cfset csvstr = createObject("java","java.lang.StringBuffer")>
<cfset csvstr.append(qGetApplicants.columnList & "," & Chr(13) & Chr(10) )>
<cfloop query="qGetApplicants">
<cfloop list="#qGetApplicants.columnList#" index="thisColumn">
<!-- remove carrage commas -->
<cfset removedCarageReturns = REReplace(evaluate(thisColumn),'#chr(13)#|\n|\r','','ALL') />
<!-- remove line returns -->
<cfset csvstr.append(replace(removedCarageReturns,",","","all") & ",") />
</cfloop>
<cfset csvstr.append(Chr(13) & Chr(10))>
</cfloop>
<cfheader name="Content-Disposition" value="attachment; filename=ReportApplicantsExport.csv">
<cfcontent type="application/msexcel" variable="#ToBinary( ToBase64(csvstr.toString()) )#" reset="Yes">
Doing it via java make this sooo much quicker!
I have to confess that i picked up some of the java code form a blog - but i cant remember which one so i'm sorry to that person.
Comments
[Add Comment]
Quick update - a thanks to ryan @Stillnet Studios http://www.stillnetstudios.com/2007/03/07/java-str...
# Posted By nick tong
| 30/06/08 21:00
| Report abusive comment
Nice one! That will come in handy :-)
# Posted By Will Wilson
| 30/06/08 21:13
| Report abusive comment
It would come even more handy if you use the DataTypeConvert component, its a good idea not to reinvent the wheel, that component will pretty much cover everything you need csv2query query2cvs 2 xml 2 whatever
# Posted By Raul Riera
| 30/06/08 21:25
| Report abusive comment
@Raul: where can we find the DataTypeConvert component?
# Posted By William from Lagos
| 01/07/08 14:17
| Report abusive comment
This works nicely :) great work Nick!
# Posted By Will Wilson
| 27/07/08 13:57
| Report abusive comment
[Add Comment]
