Skip to main content
I
iccsi
Inspiring
July 8, 2016
Answered

ColdFusion does not recognize CSV files

  • July 8, 2016
  • 2 replies
  • 2258 views

I have a local ColdFusion 10 Developer version on my local machine.

I use ColdFusion to create a CSV file and save on the server and create a link using following code.

Browsers open the CSV file as a text file and I use right click link to save target, my Windows Explorer save a a html file.

It works on the other server using the same code.

I would like to know are there any configuration to set ColdFusion recognize CSV file format when save the target and it lets browsers know to use Excel to open CSV files.

  Your help and information is great appreciated,

   Regards,

   Sourises,

<Cfset thisPath = ExpandPath("*.*")>

     <cfset f_dir = GetDirectoryFromPath(thisPath)>

   <cfset f_name = "#dateformat(now(), 'mmddyy')##timeformat(now(), 'hhmm')#.csv">

   <cffile action="WRITE" file="#f_dir##f_name#"

   output="MtField1, MyField2" addnewline="Yes">

   <cfloop query="#myQuery#">

   <cffile action="APPEND" file="#f_dir##f_name#"

   output="#Field1#, #Field2#"  addnewline="Yes">

   </cfloop>

<cfset theLink = "MyServer/Report/" & f_name>

<cfoutput><a href="#theLink#">Here is the file</a></cfoutput>

   <br>

This topic has been closed for replies.
Correct answer BKBK

There are several different themes playing there. I would separate them, making the code scalable and more manageable.

Using your code, I have set up the following quick test. It uses the in-built datasource, cfartgallery, which you also have.

I placed the 3 files in the same directory. But this is in general unnecessary because you can pass the directory's path as a variable.

fileWrter.cfm

<!--- This page writes the file to be downloaded --->

<cfquery name="myQuery" datasource="cfartgallery" >

select city, email

from artists

</cfquery>

<!--- Details of the file to be written to disk --->

<cfset f_dir = getDirectoryFromPath(expandPath('*.*'))>

<cfset f_name = dateformat(now(), 'mmddyy') & timeformat(now(), 'hhmm')>

<cfset f_extension = "csv">

<cfset f_path = createobject("component", "File").getPath(f_dir,f_name,f_extension)>

<cffile action="WRITE" file="#f_path#" output="field1,field2" addnewline="Yes">

  

<cfloop query="#myQuery#">

    <cffile action="append" file="#f_path#" output="#city#, #email#"  addnewline="Yes">

</cfloop>

<cfset queryString = "?f_name=" & f_name & "&f_extension=" & f_extension>

<!--- I use a relative path so that the download link with be translated into a relative URL --->

<cfset downloadRelativePath = "fileDownload.cfm" & queryString>

<cfoutput><a href="#downloadRelativePath#">Here is the file</a></cfoutput>

fileDownload.cfm

<!--- The URL identifies the file to be downloaded --->

<cfif isDefined("URL.f_name") and isDefined("URL.f_extension")>

   

    <cfset dir = getDirectoryFromPath(expandPath('*.*'))>

    <cfset name = URL.f_name>

    <cfset fileExtension = URL.f_extension>

   

    <cfset downloadFileName = name & "." & fileExtension>

    <cfset downloadFilepath = createobject("component", "File").getPath(dir,name,fileExtension)>

   

    <cfheader name="Content-Disposition" value="attachment; filename=#downloadFileName#">

    <cfcontent type="application/vnd.msexcel" file="#downloadFilepath#">

   

<cfelse>

You first have to create the download file.

</cfif>

File.cfc

<!--- File component, which may be shared by multiple CFM pages --->

<cfcomponent>

    <cffunction name="getPath" output="false" returntype="string">

        <cfargument name="dir" type="string" required="yes">

        <cfargument name="fileName" type="string" required="yes">

        <cfargument name="extension" type="string" required="yes">

           <cfset var filePath = arguments.dir & arguments.fileName & "." & arguments.extension>

          

           <cfreturn filePath>

    </cffunction>

</cfcomponent>

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
July 9, 2016

I should also mention cfspreadsheet as an alternative solution. The code to go from query to Excel sheet will be something like this:

fileWriter.cfm

<!--- This page writes the file to be downloaded --->

<cfquery name="myQuery" datasource="cfartgallery" >

select city, email  from ARTISTS

</cfquery><!---<cfdump var="#myquery#">--->

<!--- Details of the file to be written to disk --->

<cfset f_dir = getDirectoryFromPath(expandPath('*.*'))>

<cfset f_name = dateformat(now(), 'mmddyy') & timeformat(now(), 'hhmm')>

<cfset f_extension = "xls">

<cfset f_path = createobject("component", "File").getPath(f_dir,f_name,f_extension)>

<cfset queryString = "?f_name=" & f_name & "&f_extension=" & f_extension>

<!--- I use a relative path so that the download link with be translated into a relative URL --->

<cfset downloadRelativePath = "fileDownload.cfm" & queryString>

<cfoutput><a href="#downloadRelativePath#">Here is the file</a></cfoutput>

<!--- Create Excel sheet and store it as the given path --->

<cfspreadsheet 

    action="write"

    autosize="yes"

    filename = "#f_path#" 

    overwrite = "true"

    query = "myQuery"

    sheetname = "city_email">

EddieLotter
EddieLotter
Inspiring
July 13, 2016

Sourises, did BKBK​ answer your question?

Cheers

Eddie

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
July 9, 2016

There are several different themes playing there. I would separate them, making the code scalable and more manageable.

Using your code, I have set up the following quick test. It uses the in-built datasource, cfartgallery, which you also have.

I placed the 3 files in the same directory. But this is in general unnecessary because you can pass the directory's path as a variable.

fileWrter.cfm

<!--- This page writes the file to be downloaded --->

<cfquery name="myQuery" datasource="cfartgallery" >

select city, email

from artists

</cfquery>

<!--- Details of the file to be written to disk --->

<cfset f_dir = getDirectoryFromPath(expandPath('*.*'))>

<cfset f_name = dateformat(now(), 'mmddyy') & timeformat(now(), 'hhmm')>

<cfset f_extension = "csv">

<cfset f_path = createobject("component", "File").getPath(f_dir,f_name,f_extension)>

<cffile action="WRITE" file="#f_path#" output="field1,field2" addnewline="Yes">

  

<cfloop query="#myQuery#">

    <cffile action="append" file="#f_path#" output="#city#, #email#"  addnewline="Yes">

</cfloop>

<cfset queryString = "?f_name=" & f_name & "&f_extension=" & f_extension>

<!--- I use a relative path so that the download link with be translated into a relative URL --->

<cfset downloadRelativePath = "fileDownload.cfm" & queryString>

<cfoutput><a href="#downloadRelativePath#">Here is the file</a></cfoutput>

fileDownload.cfm

<!--- The URL identifies the file to be downloaded --->

<cfif isDefined("URL.f_name") and isDefined("URL.f_extension")>

   

    <cfset dir = getDirectoryFromPath(expandPath('*.*'))>

    <cfset name = URL.f_name>

    <cfset fileExtension = URL.f_extension>

   

    <cfset downloadFileName = name & "." & fileExtension>

    <cfset downloadFilepath = createobject("component", "File").getPath(dir,name,fileExtension)>

   

    <cfheader name="Content-Disposition" value="attachment; filename=#downloadFileName#">

    <cfcontent type="application/vnd.msexcel" file="#downloadFilepath#">

   

<cfelse>

You first have to create the download file.

</cfif>

File.cfc

<!--- File component, which may be shared by multiple CFM pages --->

<cfcomponent>

    <cffunction name="getPath" output="false" returntype="string">

        <cfargument name="dir" type="string" required="yes">

        <cfargument name="fileName" type="string" required="yes">

        <cfargument name="extension" type="string" required="yes">

           <cfset var filePath = arguments.dir & arguments.fileName & "." & arguments.extension>

          

           <cfreturn filePath>

    </cffunction>

</cfcomponent>

I
iccsiAuthor
Inspiring
July 17, 2016

Thanks for the information and help to suggest alternative, actually, my code works on remote server, but not my local developer server. I was wondering if there is any configuration for the server to let browser to recognize it is csv files, it looks like browser does not recognize csv file when run from from local developer server, but works on the remote server for some reason.

Thanks a million again for the suggestions and information to provide alternative options,

regards,

Sourises,

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
July 17, 2016

I answered your original question, and more besides.

ICCSI wrote:

Browsers open the CSV file as a text file and I use right click link to save target, my Windows Explorer save a a html file.

It works on the other server using the same code.

I would like to know are there any configuration to set ColdFusion recognize CSV file format when save the target and it lets browsers know to use Excel to open CSV files.

It is not for Coldfusion to recognize the file format. It is for Coldfusion to pass the MIME type, and for the client computer (usually the browser) to recognize the type passed to it.

The usual 2 lines of code used for this are:

<!--- For excel --->

<cfheader name="Content-Disposition" value="attachment; filename=#downloadFileName#">

<cfcontent type="application/vnd.msexcel" file="#downloadFilepath#">

<!--- For CSV--->

<cfheader name="Content-Disposition" value="attachment; filename=#downloadFileName#">

<cfcontent type="text/csv" file="#downloadFilepath#">

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices