Excel: Change default encoding (file origin) of Text Import Wizard to UTF-8 (65001 : Unicode)
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?
I answered a similar question at Default character encoding for Excel Text Wizard?.
I found my answer at Changing default text import origin type in Excel.
- Close Excel, if it is open.
- Open the Registry Editor.
- Navigate to HKEY_CURRENT_USER → Software → Microsoft → Office → ▒▒ → Excel → Options, where ▒▒ is your version of Office, mostly likely the largest number you see there.
- Right-click an empty space on the right side and select New → DWORD.
- Name the item DefaultCPG, and press Enter to save.
- Right-click on DefaultCPG and select Modify.
- Set the Base to Decimal.
- 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.
- 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).
It seems that Byte Order Mark is required by Microsoft Office software.
Using Notepad++, convert the CSV using menu: Encoding -> Convert to UTF8-BOM.
-
Using the
sed
Unix utility, available in cmder or Git for Windows. See Adding BOM to UTF-8 files.sed -i '1s/^/\xef\xbb\xbf/' file.csv
- Tested with Excel 2016
- This is what avwtp meant in their answer.
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.