Here's a kludgy way to do it:
1) Make a CFM template with your CFQUERY tag below.
2) Add the following output code:
<CFOUTPUT QUERY="getfs">
#getfs.Column1#`#getfs.Column2#`#getfs.Column3#`#getfs.ColumnEtc#<BR>
</CFOUTPUT>
NOTE: Those little apostrophe-looking things are the thing to the left of
the 1 key (shares a key with the ~).
3) Open the CFM page in a browser. You should get a simple list, with
columns delimited by the ` character.
4) Click-drag with the mouse to select the page contents.
5) Copy.
6) Paste into notepad.
7) Save as TXT file.
8) In Excel, open the TXT file.
9) In the import dialog, specify that it is a delimited file, and type that
little ` character in the box marked Other. Uncheck any other delimiter
characters.
10) Press Finish and you should have what you need.
Like I said, it's kludgy, but you can probably have your results in under
five minutes.
Hope this helps,
Matthieu
-----Original Message-----
From: Deanna Schneider [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 29, 2001 11:26 AM
To: CF-Talk
Subject: creating excel file in the REQUESTED format
Hi Folks,
I have a query that pulls data on various items for about 72 counties,
quarterly for the last five years. I'm creating a downloadable excel file,
and the client has requested that the data be presented like so:
INDICATOR COUNTYNAME1, COUNTYNAME2, COUNTYNAME3
fs1 (date) value value
value
fs1 (anotherdate) value value
value
I think I must be braindead today, cause I can't figure out how in the heck
I would get the data to layout like this without doing lots and lots of
hardcoding. Here's the query that pulls all the data, if that's any help.
<cfquery name="getfs" password="#pword#" username="#uname#"
datasource="#dsn#">
SELECT f.item, c.name AS county, d.total, d.datecollected
FROM flpweb.cfs_foodstampdata d, flpweb.cfs_foodstamp f, flpweb.cfs_county c
WHERE c.countyid = d.countyid
AND d.foodstampid = f.foodstampid
ORDER BY c.name, f.item, d.datecollected
</cfquery>
Deanna Schneider
Interactive Media Developer
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
