[thelist] ColdFusion/Access Table Export

[Ron Thigpen]

Here's some code that I've used to do what I think you're describing.  The
tricks are in setting the MIME type using CF content, using the
Content-disposition header to get IE to behave, and using TABs between the
fields that are listed in the <cfoutput> section.  

Error handling via <cftry><cfcatch> for db connection was already in my snippet,
so I left it in. It's not central to this functionality at all.

This should be a lot faster than using <cffile> to write,and then read from the
filesystem.  I wouldn't use it for huge resultsets, but it is a good way to get
data to the client in a usable format.  

--Ron Thigpen



<CODE>

<cfquery name="getData"
         datasource="#datasource#"
         dbtype="ODBC">
SELECT *
FROM   Table
WHERE  Table.Field = '#constraint#'
</cfquery>

	<cfcatch type="Any">
	<cfoutput>
	Could not connect to database.<br><br>
	<p>#cfcatch.Message#
	<p>#cfcatch.Detail#
	</cfoutput>
	<cfabort>
	</cfcatch>
</cftry>
<cfheader name="Content-disposition" value="attachment; filename=prospects.xls">
<cfcontent type="application/vnd.ms-excel">Field1Title	Field2Title	Field3Title
<cfoutput query="getData">#Field1#	#Field2#	#Field3#
</cfoutput>	

</CODE>



Ben Dyer wrote:
> 
> Would using <CFFILE> to output to a comma-delimited text file suffice?
> 
> You could then open the file in Excel or something.  That's probably an
> easy thing to write.
> 
> --Ben
> 
> At 05:38 PM 5/2/2001, you wrote:
> >Hello,
> >
> >I'm using ColdFusion to display a Access database in HTML format on a
> >page. This way the user can look at the info. BUT I want to add a
> >feature so the table can be exported (as excel maybe?) onto the user's
> >hardrive. So they could get the individual table as a file on their
> >computer. It can be in Access format, but then the user would have to
> >convert it themself, which would also be fine.
> >
> >Any clues on how to do this would be appreciated.