Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Writing a .txt file for import in Excel with UTF-8 BOM

by bradcathey (Prior)
on Aug 24, 2017 at 17:04 UTC ( #1197945=perlquestion: print w/replies, xml ) Need Help??

bradcathey has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monasterians,

I'm writing a text file for import into Excel. As it is now, when the file is imported, any special characters, such as trademark symbol reads as jibberish. In my editor, I have saved the file with UTF-8 BOM which Excel reads fine.

I've looked at File::BOM but before installing and trying that, are there any other considerations? Thanks!

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot
  • Comment on Writing a .txt file for import in Excel with UTF-8 BOM

Replies are listed 'Best First'.
Re: Writing a .txt file for import in Excel with UTF-8 BOM
by perldigious (Priest) on Aug 24, 2017 at 18:44 UTC

    Maybe this is too obvious to even suggest, but have you tried messing around with the import options in Excel?

    Not sure what version of Excel you have, but something like this?

    I only suggest it since it seems like there should be a way to get Excel to recognize a UTF-8 text file, at least I'd hope that's a problem Microsoft has licked at this point. But I'm told I often give Microsoft too much credit. :-)

    UPDATE: Or instead of importing the text file you create in to Excel you could try having Perl create the Excel file directly?

    Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

      Yes, I did learn that you can import the file into Excel with UTF-8 as an option and all is fine but trying to make the experience for the client a little smoother. I guess MS doesn't take UTF-8 straight out of the box. Strange.

      I have used Excel::Template in the past, but for my purposes it would be complicated to set up and looking for something quick. But nothing is quick when programming, right.

      Thanks!

      —Brad
      "The important work of moving the world forward does not wait to be done by perfect men." George Eliot

        If you want to open the file in Excel with a double mouse click rather than using the text import wizard, File::BOM will work or just add another print line to your script.

        #!perl use strict; use utf8; #use File::BOM(); #open my $fh, '>:utf8 :via(File::BOM)','output_with_bom.csv' # or die "$!"; open my $fh, '>:utf8','output_with_bom.csv' or die "$!"; print $fh chr(0xFEFF); # U+FEFF is EF BB BF as utf-8 print $fh $_ for <DATA>; close $fh; __DATA__ a,1 b,2 ,3 ,4
        poj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1197945]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2020-10-31 02:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (286 votes). Check out past polls.

    Notices?