Skip to main content
H
Headwaters
Participant
April 26, 2010
Question

exporting db records as .CSV file

  • April 26, 2010
  • 3 replies
  • 8958 views

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# <!--- <cfif order eq 'DateContact'>desc</cfif> --->
</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#', '&')>

<Cfset totalPages = Ceiling(contacts.recordCount / maxPerPage)>
<Cfset halfOfMax = Ceiling(groupNumbers/2)>
<Cfset currentPage = (startRow + (maxPerPage-1)) / maxPerPage>           
<cfset startSingleDigits = currentPage - halfOfMax>
<Cfif startSingleDigits lt halfOfMax>
    <cfset startSingleDigits = 1>
</Cfif>
<Cfset stopSingleDigits = startSingleDigits + groupNumbers>
<cfif stopSingleDigits gt totalPages>
    <cfset stopSingleDigits = totalPages>
    <cfset startSingleDigits = stopSingleDigits - groupNumbers>
    <cfif stopSingleDigits lt 1>
        <cfset stopSingleDigits = 1>
    </cfif>
</cfif>

<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">
<!-----   the panel with the listing of links ----------------------------------------------------------->
<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#<!---  on page #thisPage# of #totalPages# ---></div>
        <cfset newStartRow = 1><!--- "#totalPages#" --->
        <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#<!---  on page #thisPage# of #totalPages# --->
</div>
<cfset newStartRow = 1><!--- "#totalPages#" --->
<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-coldfusion) for exporting a .CSV file:

<cfsetting enablecfoutputonly="yes">  <!--- Required for CSV export to function properly --->
<cfset delim = 44> <!---  Use a comma for a field delimitter, Excel will open CSV files --->

<cfcontent  type="application/msexcel">
<cfheader  name="Content-Disposition" value="filename=filename.csv">

<!--- Output  Column Headers --->
<cfoutput>Column Header  1#chr(delim)#Column Header 2#chr(delim)#</cfoutput>

<cfoutput>#chr(13)#</cfoutput> <!--- line  break after column header --->

<!---  Spill out data from a query --->
<cfloop  query="mydbquery"><cfoutput>#dbfield1##chr(delim)##dbfield2#</cfoutput></cfloop>

This topic has been closed for replies.

3 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 26, 2010

What's your Coldfusion version?

H
HeadwatersAuthor
Participant
April 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?

D
Dan_Bracuk
Inspiring
April 27, 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

<!--- present Excel file for download, or else display it in browser --->

<cfheader name="Content-Disposition" value="attachment; filename=myExportFile.xls">

<!--- <cfheader  name="Content-Disposition" value="inline; filename=myExportFile.xls">--->


<cfcontent  type="application/vnd.ms-excel">

<!--- table to be converted to XLS file --->
<table>   
<tr>
<!--- use column names as headers --->
<cfloop list="#mydbquery.columnlist#" index="listElem">
<td><cfoutput>#listElem#</cfoutput></td>
</cfloop>
</tr>

<cfoutput query="mydbquery">
<!--- fetch data from the query, row by row --->
<tr>
<cfloop list="#mydbquery.columnlist#" index="listElem">
<td>#mydbquery[listElem][currentrow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>


Think that will work on excel 2007?

A
Adam Cameron.
Inspiring
April 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

ilssac
ilssac
Inspiring
April 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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices