Solved

Coldfusion reading Excel file translates ñ into n

Posted on 2016-07-28
4
75 Views
Last Modified: 2016-07-29
I am reading an excel file using <cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">. However,letter ñ in data is converted to n in query="data".

How can I prevent translation from ñ to n
0
Comment
Question by:Errol Farro
  • 2
  • 2
4 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 41733499
Worked okay for me.

1. Can you upload a sample .xls that causes a problem on your end?
2. What is your CF version? <cfdump var="#server#">
0
 

Author Comment

by:Errol Farro
ID: 41733652
You are right !!! CFSPREADSHEET reads the ñ character

Further analyses in the code revealed the following

When I create a CSV containg the ñ character, the ñ is shown when the CSV file is opened with Notepad
However, when opening the CSV file with Excel, the ñ is converted to ñ


EXAMPLE

Opened with NOTEPAD
===============
Jeandru Castañeda

When opened with Excel
======================
Jeandru Castañeda


Code used to create CSV file is
            <cfsetting showDebugOutput="No">
            <cfheader name="Content-disposition" value="attachment;filename=#fileNameCsv#">
            <cfcontent type="UTF-8">
                <cfoutput>#word#</cfoutput>
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41733824
Hm... seems like Excel may be having a problem with the charset.  Makes sense in a way since the download isn't actually returning a real Excel file, but simple text.  Try returning binary instead. Just convert the csv string into bytes and use the "variable" attribute:

<!--- For DEMO ONLY --->
<!--- IMPORTANT: The charset for your JVM may differ --->
<cfprocessingdirective pageEncoding="utf-8">
<cfset word = "Jeandru Castañeda">
<cfset binaryText = charsetDecode(word, "windows-1252") >

<cfheader name="Content-Disposition" value="attachment;filename=test.csv"> 
<cfcontent type="text/csv;" reset="true" variable="#binaryText#">

Open in new window


It is important to use the correct charset when decoding the string or the results will be wrong.  If you not sure what that is, check the default charset using:

<cfset defaultCharset = createObject("java", "java.nio.charset.Charset").defaultCharset()>
<cfdump var="#defaultCharset.toString()#">

Open in new window

0
 

Author Closing Comment

by:Errol Farro
ID: 41735144
Many thanks !!!!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question