exporting db records as .CSV file

Report · Apr 26, 2010

Hi,

I've been asked to place an "Export .CSV File" feature in an exisiting Cold Fusion page that was developed by somebody else.

This page returns the results of a database query, so it's displaying a subset of records contained within a particular table.

I'm fairly unschooled in Cold Fusion, more familiar with PHP. However, I did some searching on Google and found some code designed to accomplish the export (I've pasted it in below).

However, I'm not exactly sure how to integrate this with the existing page. Also, I don't want to export all the records in the db, just the subset that was returned from the query.

Would anybody be kind enough to show me how this is done?

Thanks for your time.

Here is the source code from the page that displays the result of the query (I didn't create this):

<cfif not ISdefined('order')>
<cfset order="dateconact desc">
</cfif>
<cfparam name="divName" default="1">
<cfparam name="maxPerPage" default="50">
<cfparam name="groupNumbers" default="10">
<cfparam name="startRow" default="1">
<cfquery datasource=#application.datasource# name="contacts">
select u.*,r.*
from contactus u left join contactrecord r on u.contactid=r.contactid
where u.contactid > 1
<cfif isdefined('lastname')><cfif len(lastname)> and u.contactLName like '%#lastname#%'</cfif></cfif>
<cfif isdefined('firstname')><cfif len(firstname)> and u.contactFName like '%#firstname#%'</cfif></cfif>
<cfif isdefined('noemail')>and u.contactemail is null<cfelse><cfif isdefined('email')><cfif len(email)> and contactemail like '%#email#%'</cfif></cfif></cfif>
<cfif isdefined('contactcity')><cfif len(contactcity)> and u.contactcity like '%#contactcity#%'</cfif></cfif>
<cfif isdefined('contactstate')><cfif len(contactstate)> and u.contactstate like '%#contactstate#%'</cfif></cfif>
<cfif isdefined('contactzip')><cfif len(contactzip)> and u.contactzip like '%#contactzip#%'</cfif></cfif>
<cfif isdefined('contactstart')><cfif len(contactstart)>and r.DateContact <= ###dateformat(contactend, 'mm/dd/yyyy')###</cfif></cfif>
<cfif isdefined('contactend')><cfif len(contactend)>and r.DateContact >= ###dateformat(contactstart, 'mm/dd/yyyy')###</cfif></cfif>
<cfif isdefined('contactwhy')><cfif isdefined('contactwhy')>and r.contacttype in (#contactwhy#)</cfif></cfif>
<cfif reqbroch neq 2>and r.reqbroch = #reqbroch#</cfif>
<cfif contactNewsletters neq 2>and u.contactNewsletters = #contactNewsletters#</cfif>
order by #order# 
</cfquery>
<cfset searchparam = "">
<cfif isdefined('lastname')><cfif len(lastname)><cfset searchparam = listappend(searchparam, 'lastname=#lastname#', '&')></cfif></cfif>
<cfif isdefined('firstname')><cfif len(firstname)><cfset searchparam = listappend(searchparam, 'firstname=#firstname#', '&')></cfif></cfif>
<cfif isdefined('noemail')><cfset searchparam = listappend(searchparam, 'noemail=1', '&')><cfelse><cfif isdefined('email')><cfif len(email)><cfset searchparam = listappend(searchparam, 'email=#email#', '&')></cfif></cfif></cfif>
<cfif isdefined('contactcity')><cfif len(contactcity)><cfset searchparam = listappend(searchparam, 'contactcity=#contactcity#', '&')></cfif></cfif>
<cfif isdefined('contactstate')><cfif len(contactstate)><cfset searchparam = listappend(searchparam, 'contactstate=#contactstate#', '&')></cfif></cfif>
<cfif isdefined('contactzip')><cfif len(contactzip)><cfset searchparam = listappend(searchparam, 'contactzip=#contactzip#', '&')></cfif></cfif>
<cfif isdefined('contactstart')><cfif len(contactstart)><cfset searchparam = listappend(searchparam, 'contactstart=#dateformat(contactstart, 'mm/dd/yyyy')#', '&,')></cfif></cfif>
<cfif isdefined('contactend')><cfif len(contactend)><cfset searchparam = listappend(searchparam, 'contactend=#dateformat(contactend, 'mm/dd/yyyy')#', '&')></cfif></cfif>
<cfif isdefined('contactwhy')><cfif isdefined('contactwhy')><cfset searchparam = listappend(searchparam, 'contactwhy=#contactwhy#', '&')></cfif></cfif>
<cfset searchparam = listappend(searchparam, 'reqbroch=#reqbroch#', '&')>
<cfset searchparam = listappend(searchparam, 'contactnewsletters=#contactnewsletters#', '&')>

<html>
<head><title>Maine Windjammer Cruises Admin</title><META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<link rel="STYLESHEET" type="text/css" href="styles.css">
<script language="JavaScript" src="popUpWindow.js"></script>
</head>
<body onLoad="showPanel('12');">
<cfinclude template="nav.cfm">

<div id="panel12" class="panel">
<table>
    <tr>
    <td width="600" valign="top">
    <h3>Mailing Lists</h3>
    <p class="pheader">Mailing Lists (Click the table heading to sort by that column)</P>
    <P><a href="searchmaillist.cfm">Search Mailing Lists</a> | <a href="exportmaillist.cfm">Export CSV file (coming soon)</a></P>
    <table border=1 width="100%">
        <TR>
        <TD COLSPAN="8" style="font-size:10pt; ">
        <cfoutput>
        <cfset thisPage = Fix((startRow - 1 + maxPerPage) / maxPerPage)>
        <cfset endResults = startRow + maxPerPage>
        <cfif endResults gt contacts.recordCount>
            <cfset endResults = contacts.recordCount>
        </cfif>
        <div class="black" style="background-color:##ffffff; text-align:center; padding-top:0px; padding-bottom:0px; padding-left:0px; padding-bottom:5px;">Results #startRow# through #endResults# of #contacts.recordCount#</div>
        <cfset newStartRow = 1>
        <cfset previousStartRow = startRow - maxPerPage>
        <cfset nextStartRow = startRow + maxPerPage>
        View Page: <cfif previousStartRow gte 1> <a href="maillistsdetailed.cfm?startRow=#previousStartRow#&order=#order#&divName=#divName#&maxPerPage=#maxPerPage#&#searchparam#">Previous</a></cfif>
        <cfloop from="1" to=#totalPages# index="page">
        <cfif (page mod groupNumbers eq 0) or (page gt startSingleDigits and page lt stopSingleDigits) or page eq totalPages or page eq 1>
            <cfif newStartRow neq startRow><a href="maillistsdetailed.cfm?startRow=#newStartRow#&order=#order#&divName=#divName#&maxPerPage=#maxPerPage#&#searchparam#">#page#</a><cfelse>#page#</cfif>
        </cfif>
        <cfset newStartRow = newStartRow + maxPerPage>
        </cfloop>
        <cfif nextStartRow lt contacts.recordCount><a href="maillistsdetailed.cfm?startRow=#nextStartRow#&order=#order#&maxPerPage=#maxPerPage#&#searchparam#">Next</a></cfif></td></tr>
        <tr>
        <td class="norm">Edit</td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=contactLname&maxPerPage=#maxPerPage#&#searchparam#">Last Name</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=contactFname&maxPerPage=#maxPerPage#&#searchparam#">First Name</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=contactemail&maxPerPage=#maxPerPage#&#searchparam#">E-mail</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=contactwhy&maxPerPage=#maxPerPage#&#searchparam#">How Contacted</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=newsletter&maxPerPage=#maxPerPage#">Newsletter</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=datesubmitted&maxPerPage=#maxPerPage#&#searchparam#">Date Contacted</a></td>
        <td class="norm"><a href="maillistsdetailed.cfm?order=processed&maxPerPage=#maxPerPage#&#searchparam#">Processed</a></td>
        <td class="norm">Contact ID</td>
        <td class="norm">Merge</td></tr></cfoutput>
        <cfif contacts.recordCount eq 0><tr ><td colspan=4>There are no contacts in the mailing list.</td></cfif>
        <cfoutput query="contacts" startrow="#startRow#" maxrows="#maxPerPage#">
        <tr>
        <td class="norm" style="border-style: solid; border-color: Black; background-color: silver; border-right-width: thin; border-bottom-width: thin;" class="norm"><a style="color: black; font: 10pt verdana;" href="contactupd.cfm?mid=#contactid#&order=#order#&startRow=#startRow#&maxPerPage=#maxPerPage#&#searchparam#&tourl=2">Edit</a></td>
        <td class="norm">#contactlname#</td>
        <td class="norm">#contactfname#</td>
        <td class="norm">#contactemail#</td>
        <td class="norm"><cfif contacttype eq 1>Contact Form<Cfelseif contacttype eq 2>Requested a Brochure<Cfelseif contacttype eq 3>Newsletter<Cfelseif contacttype eq 4>Win a Cruise</CFIF></td>
        <td class="norm"><cfif contactNewsletters eq 1>Yes<CFELSE>No</CFIF></td>
        <td class="norm">#dateformat(DateContact, 'mm/dd/yyyy')#<BR>(#dateformat(datesubmitted, 'mm/dd/yyyy')#)</td>
        <td class="norm"><cfif processed eq 1>Yes<CFELSE>No</cfif></td>
        <td class="norm">#contactid#</td>
        <td class="norm"><form action="mergecontact.cfm"><input type="hidden" value="#contactid#" name="badmid">Keep: <input type="text" name="keepmid" size="10"><input type="submit" value="Merge" name="mergemid"></form></td>
        </tr>
        </cfoutput>
    </table></td></tr>
</table>
<cfoutput>
<cfset thisPage = Fix((startRow - 1 + maxPerPage) / maxPerPage)>
<cfset endResults = startRow + maxPerPage>
<cfif endResults gt contacts.recordCount><cfset endResults = contacts.recordCount></cfif>
<div class="black" style="background-color:##ffffff; text-align:center; padding-top:0px; padding-bottom:0px; padding-left:0px; padding-bottom:5px;">
    Results #startRow# through #endResults# of #contacts.recordCount#
</div>
<cfset newStartRow = 1>
<cfset previousStartRow = startRow - maxPerPage>
<cfset nextStartRow = startRow + maxPerPage>
View Page: <cfif previousStartRow gte 1> <a href="maillistsdetailed.cfm?startRow=#previousStartRow#&order=#order#&divName=#divName#&maxPerPage=#maxPerPage#&#searchparam#">Previous</a></cfif>
<cfloop from="1" to=#totalPages# index="page">
<cfif (page mod groupNumbers eq 0) or (page gte startSingleDigits and page lte stopSingleDigits) or page eq totalPages or page eq 1>
    <cfif newStartRow neq startRow><a href="maillistsdetailed.cfm?startRow=#newStartRow#&order=#order#&divName=#divName#&maxPerPage=#maxPerPage#&#searchparam#">#page#</a><cfelse>#page#</cfif>
</cfif>
<cfset newStartRow = newStartRow + maxPerPage>
</cfloop>
<cfif nextStartRow lt contacts.recordCount><a href="maillistsdetailed.cfm?startRow=#nextStartRow#&order=#order#&maxPerPage=#maxPerPage#&#searchparam#">Next</a></cfif></cfoutput>
</div>
</body></html>

And here's the code I found online (http://www.universalwebservices.net/web-programming-resources/coldfusion/exporting-data-to-excel-col...) for exporting a .CSV file:

<cfsetting enablecfoutputonly="yes">  <cfset delim = 44>  <cfcontent type="application/msexcel"> <cfheader name="Content-Disposition" value="filename=filename.csv">  <cfoutput>Column Header 1#chr(delim)#Column Header 2#chr(delim)#</cfoutput> <cfoutput>#chr(13)#</cfoutput>   <cfloop query="mydbquery"><cfoutput>#dbfield1##chr(delim)##dbfield2#</cfoutput></cfloop>

Report · Apr 26, 2010

<cfquery datasource=#application.datasource# name="contacts">

This line is the start of the query block that is accessing the database, getting the data, and storing it in a record set variable named "contacts"

<cfloop  
query="mydbquery"><cfoutput>#dbfield1##chr(delim)##dbfield2#</cfoutput></cfloop>

This line will loop over a query record set variable and output each row. The #dbField1# and #dbField2# are stand in names for the columns returned from your database in the above <cfquery....> block. You need to replace them with the actual column names.

contactfname

contactlname

contactemail

These seem to be some of the column returned by the query based on the previous code. But the query does not show the column unfortunatly and I'm don't have the time to parse all that code in detail for you.

Report · Apr 26, 2010

That convert-to-CSV code is a bit... err... "simplistic".

* Firstly, the MIME type for Excel is application/vnd.ms-excel.

* Secondly, CSV data needs to be savvy to data containing things like embedded whitespace (incl. CRLF), commas and quotes.

* Thirdly... what's with using chr(44), instead of, like just a comma?

The first and third issues won't really get in your way too much, but the second point renders the code not-production-ready.

--

Adam

Report · Apr 26, 2010

What's your Coldfusion version?

Report · Apr 26, 2010

Thanks for the replies.

MX7 is what's running on the server where this page lives.

As far as the client is concerned, they just want to be able to do their query, view the results, and then click on something that says "export .CSV file" to create a file they can open with Excel.

How should I set up this "trigger"? Would it be a submit button in a form or simply a hyperlink? Can I simply define a function within the page displaying the query results, have it execute when the user clicks the button or link? Or do I need to load a new .CFM file that handles the export?

Report · Apr 26, 2010

Any or all of the above options are valid choises and each about as do able as the other depending on what technologies you are comfortable with.

Report · Apr 26, 2010

MX7 is what's running on the server where this page lives.

Damn! If you were on 9 I would have given you a one-stop solution: the cfspreadsheet can read in a query and write out a CSV file.

As far as the client is concerned, they just want to be able to do their query, view the results, and then click on something that says "export .CSV file" to create a file they can open with Excel.

Even better, why don't you just export to Excel's own type, XLS? There is a trick.

Convert a query result to an HTML table. Then use cfheader and cfcontent to return the XLS file to the client.

You'll have to do something like this:

1) Link to page to do export

<a href="export.cfm" title="Export to Excel file">Export to Excel file</a>

2) export.cfm

<table>
<tr>

<cfloop list="#mydbquery.columnlist#" index="listElem">
<td><cfoutput>#listElem#</cfoutput></td>
</cfloop>
</tr>

<cfoutput query="mydbquery">

<tr>
<cfloop list="#mydbquery.columnlist#" index="listElem">
<td>#mydbquery[listElem][currentrow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>

Report · Apr 27, 2010

Think that will work on excel 2007?

Report · Apr 27, 2010

Thanks--I just found out the person using the site is actually looking to get this data into Access. Not sure if that changes the recommendations here...

When I get a minute this afternoon I'll try the solution you suggested.

Report · Apr 30, 2010

OK, I'm getting a little closer now.

But is there a way I can reference the recordset already created by the query, which is already displayed, and make that the content of the spreadsheet?

<cfoutput>reference the table resulting from the query here</cfoutput>

As I understand it, the recordset that resulted from the query is given an ID of "contacts"--so presumably I should be able to say, "OK, take this table, which is displaying the results of the query, and create a spreadsheet from it"

I know, I'm not too adept with Cold Fusion...

Report · Apr 30, 2010

Well the record set is stored in a variable. A variable named "contacts" apparently according to your post.

Most likely this variable is being created in the variables scope. The variables scope lives for the live of the single request only and then is thrown away for garbage collection. A the request life begins when the web server (IIS, Apache, ect) request the ColdFusion file until ColdFusion is finished executing the CFML code and returns the results to the web server to be forward onto the client.

But there are other scopes the live longer then a single request. Session, Application, Server, Client ect. For what I think you are trying to do, the session scope would probably be the most proper scope. This scope lives for the life time of one users time using your application one time (and 20 minutes after they stop using the application under the default configuration).

This would be simply be using "session.contacts" or something like that, where ever you want to reference this data. But session management does have to be enabled for you CF application. The documentation explains the details quite nicely.

Report · Apr 30, 2010

OK, I'm getting a little closer now.
But is there a way I can reference the recordset already created by the query, which is already displayed, and make that the content of the spreadsheet?
<cfoutput>reference the table resulting from the query here</cfoutput>
As I understand it, the recordset that resulted from the query is given an ID of "contacts"--so presumably I should be able to say, "OK, take this table, which is displaying the results of the query, and create a spreadsheet from it"
I know, I'm not too adept with Cold Fusion...

The cfspreadsheet tag was introduced in Coldfusion 9, but you're on Coldfusion MX7. Why don't you do yourself a favour and run the code I gave you?

Adobe Community

exporting db records as .CSV file