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
