share
Super UserExcel: Change default encoding (file origin) of Text Import Wizard to UTF-8 (65001 : Unicode)
[+44] [8] Dizzley
[2015-05-06 10:12:17]
[ microsoft-excel character-encoding utf-8 ]
[ https://superuser.com/questions/911369/excel-change-default-encoding-file-origin-of-text-import-wizard-to-utf-8-650 ]

I am using a variety of tools to regularly prepare data for the web. One stage requires me to open a CSV in Excel, make changes and save the file.

Is there a way to force Excel to accept UTF-8 encoding, and to save its files with that encoding?

(2) To clarify, I would like to change a setting so that Excel assumes an input file is UTF-8 by default. As mentioned below, LibreOffice/OpenOffice does this. - Dizzley
If you don't actually have Unicode characters to deal with, I'd consider using a middleware to convert all CSVs to ASCII and then open in Excel. - Vassile
(1) Related question here but not exactly the same. - hhh
[+24] [2017-02-15 21:37:25] Robert H. [ACCEPTED]

I answered a similar question at Default character encoding for Excel Text Wizard? [1].

I found my answer at Changing default text import origin type in Excel [2].

  1. Close Excel, if it is open.
  2. Open the Registry Editor.
  3. Navigate to HKEY_CURRENT_USER → Software → Microsoft → Office → ▒▒ → Excel → Options, where ▒▒ is your version of Office, mostly likely the largest number you see there.
  4. Right-click an empty space on the right side and select New → DWORD.
  5. Name the item DefaultCPG, and press Enter to save.
  6. Right-click on DefaultCPG and select Modify.
  7. Set the Base to Decimal.
  8. For Value data, enter 65001 to set your default to UTF-8. For some other encoding, use the code page identifier, which you can find in the Text Import Wizard in Excel or in this list [3].
  9. Click OK.

Like Vasille says in the comment to this question, if your file is not actually in UTF-8 format, you may technically want to convert the characters within the file to the encoding you want before opening in Excel. For my purposes, though, UTF-8 does a good enough job of displaying non-corrupted characters.

Not working? Make sure you set Base to Decimal (Step 7).

Screenshot of Edit DWORD 32-bit value showing setting base to decimal

[1] https://superuser.com/questions/1076240/default-character-encoding-for-excel-text-wizard/1179240
[2] http://www.lukemiller.org/journal/2005/03/changing-default-text-import-origin.html
[3] https://docs.microsoft.com/en-us/windows/win32/intl/code-page-identifiers

(9) Are there any reasons not to do this? Why is this not the default behavior of Excel? --- My team often deals with datasets containing foreign characters, and I'm wondering if there is any reason to not perform this action on each of our machines. - user1318135
(7) This unfortunately did not work on my machine. Files still open with default encoding (Win 7, Office 2016). - user1318135
(10) Doesn't work on mine also, Win 10 Office 2016 - Phuah Yee Keat
(5) No dice. Excel behaves as stupidly as ever. One would expect an obvious feature like this to be accessible via a user preference in software as mature as Excel. - Aaron Bramson
IT WORKS!!! At first, I forgot to set the Base to Decimal. After fixing that—and making sure the Value data is set to 65001 in decimal—the encoding in the Text Import Wizard now defaults to UTF-8. Hooray!!! - - - - I added an edit (pending approval) with a screenshot and a few minor clarifications. FYI, I am using Excel 16.0 (Office 365) on Windows 10. - Alex Quinn
(1) @Robert H. Is there a way to achieve this on a Mac? - alex
(1) Can we clarify what this answer is supposed to achieve? After following the steps I thought Excel would open any file automatically with UTF-8 encoding i.e. double-clicking in Windows Explorer would open the CSV using UTF-8. That hasn't happened - it defaults back to ANSI like always. If I select "Import CSV" from the Data menu, UTF-8 is the default encoding - but I think it always was. EIther way, it's still a multi-step process to do something as simple as open a CSV in Excel. (Office 365, Win 10) - Tom Wagstaff
@Robert , do I need to restart PC after the change in the Registry ? - Igor Savinkin
1
[+10] [2019-05-16 20:15:39] Cristian

It seems that Byte Order Mark [1] is required by Microsoft Office software.

  1. Using Notepad++, convert the CSV using menu: Encoding -> Convert to UTF8-BOM.

  2. Using the sed Unix utility, available in cmder [2] or Git for Windows [3]. See Adding BOM to UTF-8 files [4].

    sed -i '1s/^/\xef\xbb\xbf/' file.csv

  • Tested with Excel 2016
  • This is what avwtp meant in their answer [5].
[1] https://en.wikipedia.org/wiki/Byte_order_mark
[2] https://cmder.net/
[3] https://gitforwindows.org/
[4] https://stackoverflow.com/a/35807131/3735961
[5] https://superuser.com/a/1431190/376144

Setting the BOM in Notepad++ works perfectly fine. Is there a command-line solution for Windows, like at CMD-Prompt or Powershell? - snahl
An answer for Python: f = open(out_filename, "w") #set the BOM to UTF-8. Required for importing csv to Excel f.write('\ufeff') # Close the file f.close() - snahl
Setting the BOM was the fastest and easiest. Thanks - Orsu
2
[+6] [2015-05-06 10:15:33] Moudiz

One easy way to change excel ANSI encoding to UTF-8 is the open the .csv file in notepad then select File > Save As. Now at the bottom you will see encoding is set to ANSI change it to UTF-8 and save the file as a new file and then you're done.


I will experiment with this. One problem I have is opening files consistently in the correct encoding so I do not get data in two encodings in a single results file. Then it becomes hard to fix. I really wish Excel allowed me a better way to choose input file encoding. - Dizzley
3
[+3] [2015-05-06 10:44:36] Lluser

I solved similar problem before. Unsuccessfully, but you can use LibreOffice [1], which is UTF-8 in default.

[1] https://www.libreoffice.org/

LibreOffice is very handy for this. However, sometimes I have VBA macros to run. Thanks Lluser - Dizzley
4
[+3] [2017-01-27 07:57:57] hhh

You need to use the File > Import option and start with blank document and specify UTF-8

enter image description here

but this is far from optimal to make this a default setting for all files, anyway it is unnecessary to rotate the files around Google Drive or LibreOffice. The defaults are just badly chosen and the disability to change that is irritating.

Related

  1. How to set character encoding when opening Excel [1] but does not find the option to change the defaults such that all files are automatically opened with UTF-8 instead of Macintosh format in OSX

  2. How to change default file option to UTF-8 away from Macintosh file option [2] in Apple.SE.

[1] https://superuser.com/questions/280603/how-to-set-character-encoding-when-opening-excel
[2] https://apple.stackexchange.com/questions/270534/excel-how-to-change-default-file-option-to-utf-8-away-from-macintosh-file-optio

5
[0] [2015-05-06 21:26:20] Karan

There's an Excel addin available here [1] to work with Unicode CSV files that should help you.

Here's the developer Jaimon Mathew's note:

Excel treats .csv files as text files and will replace all Unicode characters with “?” when saved normally. If you want to preserve the Unicode characters, you would need to save the file as “Unicode text (*.txt)”, which is a Tab delimited file. Since I couldn’t find any existing solution to preserve Unicode characters in CSV format, I thought I’ll give it a go in creating an Excel Addin to do just that.

1

[1] https://jaimonmathew.wordpress.com/2011/08/23/excel_addin_to_work_with_unicode_csv/

6
[0] [2015-10-02 11:04:10] Viktor

It's not the best solution, but it's an option: upload your Excel file to Google drive, open it with Google Tabs and download as a csv file. It worked for me.


(5) This does not answer the question how to change the encoding of Excel document. - Ramhound
Of course, when you open the CSV in Excel you lose the cell formatting so some numeric ID strings get converted to integers. - Dizzley
7
[0] [2019-04-29 23:59:02] avwtp

Assuming you have a unix shell available (Cygwin), I append to the front of the csv file a little 3 char unicode ID (UTF-8) and then it reads fine into Excel and more importantly it is properly processed as input using VB Excel automation.

UTF-8.txt

EFBBBF << hex edit into a 3 char file.

cat UTF-8.txt file-to-use.csv > new_file.csv


(1) (1) Can you describe how/why this works? (2) Can you describe exactly how to create this UTF-8.txt file? - Scott - Слава Україні
I suggest using a hex editor and typing in the 3 character sequence. Why it works, no idea. I figured out this solution by taking an existing UTF-8 csv file, renaming it to txt, importing it into Excel (like suggested in other threads), saving it as CSV with the Unicode plugin (other thread) and then doing a binary diff of the two files. The only difference was the 3 character starting sequence. - avwtp
See this thread why it works [link] (stackoverflow.com/questions/6002256/…) - avwtp
In bash (OSX) printf '\xEF\xBB\xBF' > new_file.csv then cat the rest in - HT @Yaron U. - Mr R
8