I have a CFM page that will do this for you. Would you like me to send it
to you?
It will email you the results of the QUERY and also make it an HTML File.
At 10:13 AM 4/24/2001 +0100, you wrote:
>Hi all,
>
>I am trying to generate a CSV file for a client from their datacapture
>database. They want to be able to open it in Excel.
>
>This is the code I have so far:
>
><CFQUERY datasource="#datasourcename#" name="getdatacapture">
> SELECT *
> FROM tbl_datacapture
></CFQUERY>
>
><cfset enviro_separator = ",">
>
><CFSET newline = chr(13)>
>
><cfquery name="getFields" datasource="#datasourcename#">
> SELECT *
> FROM tbl_fieldnames
></cfquery>
>
><!--- set file headings --->
> <CFSET fieldlist = "">
> <CFOUTPUT query="getFields">
> <CFSET fieldlist = fieldlist & "#fieldName#,">
> <CFIF getFields.currentrow is 1>
> <CFSET fileContent = "#fieldName#">
> <CFELSE>
> <CFSET fileContent = filecontent &
> "#enviro_separator##fieldName#">
> </CFIF>
> </CFOUTPUT>
> <CFSET fileContent = fileContent & newline>
>
><!--- generate csv fields --->
> <CFSET cf_fields = "">
> <CFOUTPUT QUERY="getdatacapture" group="ID">
> <CFLOOP INDEX="i" LIST="#fieldlist#">
> <CFIF i is "ID">
> <CFSET field_value = "#getdatacapture.ID#">
> <CFELSEIF i is "title">
> <CFSET field_value =
> "#getdatacapture.title#">
> <CFELSEIF i is "firstname">
> <CFSET field_value =
> "#getdatacapture.firstname#">
> <CFELSEIF i is "surname">
> <CFSET field_value =
> "#getdatacapture.surname#">
> <CFELSEIF i is "address1">
> <CFSET field_value =
> "#getdatacapture.address1#">
> <CFELSEIF i is "address2">
> <CFSET field_value =
> "#getdatacapture.address2#">
> <CFELSEIF i is "town">
> <CFSET field_value = "#getdatacapture.town#">
> <CFELSEIF i is "county">
> <CFSET field_value =
> "#getdatacapture.county#">
> <CFELSEIF i is "postcode">
> <CFSET field_value =
> "#getdatacapture.postcode#">
> <CFELSEIF i is "telephone">
> <CFSET field_value =
> "#getdatacapture.telephone#">
> <CFELSEIF i is "otherinfo">
> <CFSET field_value =
> "#getdatacapture.otherinfo#">
> </CFIF>
> <CFSET field_value_nocommas =
> "#ReplaceNoCase("#field_value#", ",", " ",
>"ALL")##enviro_separator#">
> <CFSET filecontent = filecontent &
> field_value_nocommas>
>
> </CFLOOP>
> <CFSET filecontent = filecontent & newline>
> </CFOUTPUT>
>
><CFSET filename =
>"glyndebourne_datacapture_#DateFormat("#now()#","ddmmyy")#.csv">
>
> <cffile action="WRITE" file="#csv_root#\#filename#"
> output="#fileContent#">
>
>
>Everything seems to work OK, and a file is created which I can view in
>Notepad, and it seems ok. But when I try to open it in Excel it tells me
>that:
>
>SYLK: File Format Not valid
>
>I have had a look at the csv file, and compared it to one that I know
>definetly works, and I can't see a difference.
>Anyone know what this error means??
>
>TIA
>
>Will Swain
>Hot Horse Ltd
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
