[thelist] ColdFusion/Access Table Export

jeff jeff at members.evolt.org
Wed May 2 21:36:46 CDT 2001


jon & bruce,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Bruce Heerssen
:
: If the database file is in the web root
: somewhere you can simply link directly
: to it.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If it *is* in the webroot, now would be a good time to move it out of the
webroot.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: If the database is outside the web root,
: which is normally recommended, then link
: to a cfm page that has two tags in it:
: cffile and cfcontent. The first gets the
: file, the second sets the mime type of the
: page to keep the browser from trying to
: display the file.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You will probably experience problems with IE because it will try to
interpret the file type based on the file extension and open up access
directly instead of giving you the option of downloading it.

There are also problems of your database no longer being inaccessible from
the web.  At this point you might as well put it in your webroot and put a
link to it on your homepage -- unless of course you're offering this
download behind a password protected section of the site.  However, there is
probably *alot* more information in the database than what the client really
needs.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Looking back at your message, I noticed that
: you said 'table'.  You can download a COM
: object that can build an excel file on the
: fly based upon a query. Try http://cfcomet.com
: for this.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For reasons of threading and server performance, using com objects are
generally not a good idea with coldfusion.  in this case it's completely
unnecessary as you can create a tab-delimited text file send that to the
user and that can be opened up very easily in excel -- probably all the
client is really needing anyway.

creating a tab-delimited file is very easy actually.  all you do is query
the table you want to create the export of.  write to a textfile using
cffile with the columns tab-delimited.  then, loop over the query results
appending a newline of data to the text file for each record.  here's some
sample code to get you started.

<cfquery name="getmembers" datasource="#datasource#">
  SELECT *
  FROM members
</cfquery>

<cffile
 action="APPEND"
 file="c:\csv_export.txt"
 output="id	first_name	last_name"
 addnewline="Yes">

<cfoutput query="getmembers">
  <cffile
   action="APPEND"
   file="c:\csv_export.txt"
   output="#id#	#first_name#	#last_name#"
   addnewline="Yes">
</cfoutput>

now, you can zip up the file using something like <cfx_zip>and send it to
them via cfcontent, make it web accessible and give them a page with a link
to it, or even email it to them as an attachment.

good luck,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org





More information about the thelist mailing list