Participant

Question

exporting db records as .CSV file

Forum|Forum|15 years ago
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# 
</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-coldfusion) 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>

Getting started

This topic has been closed for replies.

BKBK

Community Expert

What's your Coldfusion version?

H

HeadwatersAuthor

Participant

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?

ilssac

Inspiring

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.

A

Adam Cameron.

Inspiring

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

Inspiring

<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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded