We need you!

We're working hard on the next version of Developer Fusion. Let us know what you think we should be up to!

Members

Technology Zones

Articles

Hosted By

MaximumASP

Info
Rated
Read 31,706 times
1 comments

Related Categories

Generate CSV reports

xmantim

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>

Comments

Read Comments | Post Comment

  • write CSV file w/o headings?

    Posted by ztc on 31 Dec 2002

    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.