Library code snippets
Generate CSV reports
If you are like me, my bosses all ask for reports. This is how I give them the reports. I use an advanced query to pull the appropriate data then write it all in an excel spread sheet. This uses the CFFILE tag to write the spread sheet file. Granted it comes out as a CSV file, but Excel will open it.
<!--- Name your DSN here. Change DSN to your actual DSN. --->
<cfset mydatasource = "DSN">
<!--- You will also need to modify this to fit your needs --->
<cfquery name="daily" datasource="#mydatasource#" dbtype="ODBC" >
SELECT *
FROM Customers
Where company <> ''
</cfquery>
<!--- create some variables to store the dirctory and file name. --->
<Cfset thisPath = ExpandPath("*.*")>
<cfset f_dir = GetDirectoryFromPath(thisPath)>
<!--- I like to use the date and time for a file name, but you can name it anything you like by changing the value below. --->
<cfset f_name = "#dateformat(now(), 'mmddyy')##timeformat(now(), 'hhmm')#.csv">
<!--- Lets make the file, and put the first row of Column headings in --->
<cffile action="WRITE" file="#f_dir##f_name#"
output="Company, Sales Area, Sales Person, Site Contact, Contact Phone, Contact Email, Contact Title" addnewline="Yes">
<!--- Now lets loop over the RecordSet and fill in the data --->
<!--- You will notice that that Commas will get you into trouble. --->
<!--- So you need to find and replace them before you write them in the CSV file. --->
<!--- I show you how in the Company name field below --->
<cfloop query="daily">
<cffile action="APPEND" file="#f_dir##f_name#"
output="#REPLACE(company, ",", "","AlL")#, #region#, #salesperson#, #firstname# #lastname#, #contactphone# #contactphoneext#, #contactsemail#, #contactsTitle#"
addnewline="Yes">
<!--- End the loop here --->
</cfloop>
<br>
<!--- Now give them the URL to the file. You will need to modify this as well. --->
<a href="/reports/#f_name#">Here is the file</a>
Related articles
Related podcasts
-
Crawlable Flash, Concurrent Python, Smart Robots.txt and more
Mixed topics today Adobe works with Google, Yahoo and others to make Flash apps crawlable. Reddit.com open sources their codebase "Smart" robots.txt files - do you use them? Bruce Eckel article on concurrent Python with Twisted Railo - open source ColdFusion Cognifty - new PHP framework...
Events coming up
-
Feb
17
Andrew Powell on How I Learned to Stop Worrying and Love UX
Atlanta, United States
Come on out this month to hear Atlanta's own Andrew Powell talk on "How I Learned to Stop Worrying and Love UX"Andrew Powell has been architecting and developing web applications for over 10 years using ColdFusion, Flex, AJAX, and Java. His background includes experience running IT Departments for firms in the executive search and aviation consulting fields. Currently, Andrew is a Principal Architect at Universal Mind specializing in ColdFusion, Java, & Flex.
how would you generate the same CSV file w/o column headings?
using output="" with the WRITE action give a blank line for the column
headings rather than no line at all.
This thread is for discussions of Generate CSV reports.