I often need to import into Excel text files which are codified using ISO (Excel calls it 28591 Western European ISO), but Excel keeps trying to read them as being Japanese, which reads English-alphabet characters normally but anything with an accent mark, umlaut… as a Japanese character. I can change it manually when I import each file, but is there a way to tell it “please use your option 28591 as the default”? The question refers mainly to the 2016 version.
Thanks in advance!
I don’t have a direct answer, I know that Excel relies on the regional settings in the Control Panel when importing text (interpreting dates, deciding if 3.14159 is text or a real number, etc.).
This thread on StackOverflow says that the best recipe to make Excel behave predictably is to encode the input file in UTF-8 and include a byte order mark at the beginning (even though a BOM doesn’t make much sense in UTF-8).
Others choose to import their files into Google Sheets or OpenOffice/LibreOffice. Of course, that’s even more cumbersome than having to select code page 28591 manually in Excel’s wizard…
Actually it isn’t (unlike Excel, both OO and LO get it right by themselves), but I need to use what’s available…
Those settings apparently aren’t used in order to help decide what is the proper character set because I’ve had Excel insist in thinking they were Japanese with computers set to half a dozen different locations, none of which was Japan. The current problem files are in French, computer is set to French (with Spanish keyboard), location is France, it still wants to turn accented characters into Japanese ones.
Oh well. At least I know that I can just go on blaming Excel instead of feeling like a moron because I hadn’t found some totally-evident setting…
Thanks.
Just to clarify, these are Unicode text files, right?
… I… guess? I don’t control the programs which generate them. But they are files which Open Office/ Libre Office and plain-text editors open correctly; Excel doesn’t. Neither does Word but I don’t normally begin by opening them with Word.
It would help a lot to know exactly what format your text files are. The most likely would be UTF-8, but since it fails to import into Excel, it’s probably some older and/or more obscure format like UCS-16, or even just 8-bit Latin-1. A hex dump of just a few bytes at the beginning of the file would likely reveal this, but I don’t know of any easy way to get a hex dump in Windows (which I assume you’re running) without installing additional software like Powershell.
Apparently you have to do it via the registry: Stuff: Changing default text import origin type in Excel