Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re^3: What to do when converting Excel-supplied data to Unicode

by davis (Vicar)
on Feb 20, 2009 at 23:03 UTC ( #745463=note: print w/ replies, xml ) Need Help??


in reply to Re^2: What to do when converting Excel-supplied data to Unicode
in thread What to do when converting Excel-supplied data to Unicode

Curious. When do you print $decoded inside the sub? Just before the return?

davis


Comment on Re^3: What to do when converting Excel-supplied data to Unicode
Replies are listed 'Best First'.
Re^4: What to do when converting Excel-supplied data to Unicode
by ITFinanceGuy (Initiate) on Feb 20, 2009 at 23:33 UTC
    Yes. I just figured it out. I was reading the data using $cell->{Val} which apparently contains the unformatted cell value. I changed to using $cell->value() and that worked (almost..).

    I still cannot read Chinese characters from an Excel file. I am guessing the encoding I pass to the decode() function in TextFmt() is wrong, but I do not know how to find the encoding of Excel cells. I tried 'UTF16-LE', 'UTF16-BE', and 'UTF-8' but none worked. Leaving the TextFmt function as it is posted here does not work. Anyone have an idea as to what I might be doing wrong?

Re^4: What to do when converting Excel-supplied data to Unicode
by ITFinanceGuy (Initiate) on Feb 23, 2009 at 22:55 UTC
    Yes. I figured out part of the problem. Apparently $cell->{Val} contains the unformatted value of a cell, whereas $cell->value() has the formatted value.

    But I am still having trouble extracting the right data. My output looks garbled. I tried converting from UTF16-LE, UTF16-BE, UTF-8, and nothing seems to work. I even tried downloading several utilities from the web with no luck converting Unicode data. The only thing that does work is if I save the file as a different format from within Excel itself, but I am looking for a batch way to do this.

    Does anyone know of a command line utility that will convert .xls files containing Unicode data into XML? Or a library that works? Thanks in advance!

Re^4: What to do when converting Excel-supplied data to Unicode
by Anonymous Monk on Feb 24, 2009 at 16:50 UTC
    Nevermind. I determined that I should be using visual basic + the Microsoft Office Excel reference library to convert the files and then read them with Perl. Following suggestions from:
    http://stackoverflow.com/questions/174446/how-to-automate-converting-excel-xls-files-to-excel-xml-fromat

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://745463]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (10)
As of 2015-07-28 21:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (259 votes), past polls