Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2019, 01:29 AM
Nava is online now
Member
 
Join Date: Nov 2004
Location: Hey! I'm located! WOOOOW!
Posts: 40,897

Format and language problems importing text into Excel


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!
__________________
Evidence gathered through the use of science is easily dismissed through the use of idiocy. - Czarcasm.
  #2  
Old 04-18-2019, 05:48 AM
Heracles's Avatar
Heracles is offline
Member
 
Join Date: Jul 2009
Location: Southern Québec, Canada
Posts: 1,518
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...

Last edited by Heracles; 04-18-2019 at 05:49 AM.
  #3  
Old 04-18-2019, 07:03 AM
Nava is online now
Member
 
Join Date: Nov 2004
Location: Hey! I'm located! WOOOOW!
Posts: 40,897
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.
  #4  
Old 04-18-2019, 08:22 AM
Quartz's Avatar
Quartz is online now
Charter Member
 
Join Date: Jan 2003
Location: Where the haggis roam fre
Posts: 30,831
Quote:
Originally Posted by Nava View Post
I often need to import into Excel text files which are codified using ISO (Excel calls it 28591 Western European ISO),
Just to clarify, these are Unicode text files, right?
  #5  
Old 04-21-2019, 02:26 PM
Nava is online now
Member
 
Join Date: Nov 2004
Location: Hey! I'm located! WOOOOW!
Posts: 40,897
... 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.

Last edited by Nava; 04-21-2019 at 02:28 PM.
  #6  
Old 04-21-2019, 02:40 PM
markn+ is offline
Guest
 
Join Date: Feb 2015
Location: unknown; Speed: exactly 0
Posts: 2,141
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.
  #7  
Old Yesterday, 10:43 AM
Reply's Avatar
Reply is offline
Member
 
Join Date: Jul 2003
Posts: 8,461
Apparently you have to do it via the registry: http://www.lukemiller.org/journal/20...rt-origin.html
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 09:40 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@straightdope.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright © 2018 STM Reader, LLC.

 
Copyright © 2017