Please, I'd like to see it also.
We've been able to do it, but we have to hard code the select statement as
well as the header row as we needed to present the fields in a specific order,
with headers that are different from the field names, as well as create the csv
file.
If any one is interested in our hard coded process, I'd be glad to share it.
But if this can all be done automatically, I'm there and more than ready to
be enlightened.
Carol L. Bluestein
Senior Programmer
NYS Office of Real Property
518-486-6335
[EMAIL PROTECTED]
____________________Reply Separator____________________
Subject: RE: creating excel file in the REQUESTED format
Author: [EMAIL PROTECTED]
Date: 8/29/01 9:10 AM
I've done this with a JAVA custom tag and it REALLY sped things up! I was
writing 25,000 records in a reasonable amount of time! My tag takes a query
(any query) and outputs it to an excel CSV file. I've never put it up to
the tag gallery, but I've posted the source code on this list before. If
anyone wants me to send it to them, let me know!
-----Original Message-----
From: BEN MORRIS [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 9:02 AM
To: CF-Talk
Subject: Re: creating excel file in the REQUESTED format
My experience w/ option 2: don't do it. I made a tag myself that looped
through query.fieldnames and it was extremely slow. It worked fine with a
few hundred records, but getting into 1500 records pretty much rendered it
useless, I got memory errors and it took forever to process.
Even doing a simple loop with hard coded field names takes upwards of 60
seconds to make a csv with 7000 records (about 20 fields of
contact/membership information). The execution time is acceptable for files
with 1500 or so records in my case. If anyone knows of a better way to make
CSV, I am all ears... maybe a CFX tag would execute faster. My instinct is
to make a scheduled task to create all of the csv files I might need (over
1000 files), but I am afraid that would take too long to execute too.
- Ben Morris
>>> Critter <[EMAIL PROTECTED]> 08/29/01 11:25AM >>>
Hello Deanna,
Option 1.
there is a tag in the gallery that will output to a csv file, it has some
errors in
it, so those will need to be fixed....but other than that it works ok.
Option 2.
you could just loop thru the data, output the query.fieldnames, then loop
thru the
recordset and build your file.....and push it with cfcontent if you want....
Option 1 - most of the work done for you......
Option 2 - opposite of option 1.
--
Critter, MMCP
Certified ColdFusion Developer
Crit[s2k] - <CF_ChannelOP Network="Efnet" Channel="ColdFusion">
-------------------------------------------
Wednesday, August 29, 2001, 11:26:12 AM, you wrote:
DS> Hi Folks,
DS> I have a query that pulls data on various items for about 72 counties,
DS> quarterly for the last five years. I'm creating a downloadable excel
file,
DS> and the client has requested that the data be presented like so:
DS> INDICATOR COUNTYNAME1, COUNTYNAME2, COUNTYNAME3
DS> fs1 (date) value value
DS> value
DS> fs1 (anotherdate) value value
DS> value
DS> I think I must be braindead today, cause I can't figure out how in the
heck
DS> I would get the data to layout like this without doing lots and lots of
DS> hardcoding. Here's the query that pulls all the data, if that's any
help.
DS> <cfquery name="getfs" password="#pword#" username="#uname#"
datasource="#dsn#">>
DS> SELECT f.item, c.name AS county, d.total, d.datecollected
DS> FROM flpweb.cfs_foodstampdata d, flpweb.cfs_foodstamp f,
flpweb.cfs_county c
DS> WHERE c.countyid = d.countyid
DS> AND d.foodstampid = f.foodstampid
DS> ORDER BY c.name, f.item, d.datecollected
DS> </cfquery>
DS> Deanna Schneider
DS> Interactive Media Developer
DS> [EMAIL PROTECTED]
DS>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
