Solved

Coldfusion reading Excel file translates ñ into n

Posted on 2016-07-28
4
60 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now