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
They had joined[url=http://www.game4power.com/]game4power[/url] the humans, elves, dwarves, and [url=http://www.game4power.com/]Buy Wow Gold[/url]dragons to decimate the demonic warriors and ghoulish beasts and push the[url=http://www.aocsale.com]aoc gold[/url] remnants back into [url=http://www.game4power.com/buy-gold/" target="_blank">http://www.game4power.com/buy-gold/]wow gold cheap[/url]the hellish beyond. Thousands [url=http://www.vipwarhammergold.com]warhammer gold[/url]had perished, but the alternative… The dragon mage snorted. In truth, [url=http://www.itemchannel.com]wow gold[/url]there had beenno alternative. Krasus waved [url=http://www.aion4gold.com]aion gold[/url]long, tapering fingers over the orb, summoning a vision of the orcs. The view[url=http://www.itemchannel.com]world of warcraft gold[/url]blurred momentarily, then revealed a mountainous, rocky [url=http://www.ppgold.com]wow gold cheap[/url]area further inland. A harsh land, but one still full of life and capable of [url=http://www.wowgoldone.com/]cheapest wow gold[/url]supporting the new colonists. Already, several stone [url=http://www.wowgoldone.com/][i]cheap wow gold[/i][/url]structures had risen in the main settlement, where the [url=http://www.vipaiongold.com]aion gold[/url]Warchief and one of[url=http://www.aionkina.com]aion kina[/url]the heroes of the war, Thrall, ruled. The high, rounded edifice [url=http://www.game4power.com/]Wow Gold [/url],[url=http://www.game4power.com/]www.game4power.com[/url]that served as his quarters was crude by the standards of any other race, but orcs had a propensity toward basics.
# Posted By buy wow gold
| 28/06/09 15:27
| Report abusive comment
Thanks for your useful info,
# Posted By air jordan shoes
| 30/06/09 03:21
| Report abusive comment
http://www.accountmmo.com supply cheap wow account,sell wow account,WoW account,buy WoW account,more World of Warcraft account on our website!
# Posted By wow account
| 02/07/09 07:08
| Report abusive comment
http://www.accountmmo.com is the best way to buy runescape accounts or sell runescape accounts. There are also hundreds wow accounts for sale. Update wow accounts everyday.100% safe guaranteed for buy wow account or sell wow accounts.
# Posted By runescape accounts for sale
| 02/07/09 07:10
| Report abusive comment
http://www.wowgolds.org have 3 years sales experience in world of warcraft. We are committed to provide users with fast, safe and cheap wow gold,wow accounts and powerleveling services!"
# Posted By cheap wow gold
| 02/07/09 07:10
| Report abusive comment
[Add Comment]
