Excel: Change default encoding (file origin) of Text Import Wizard to UTF-8 (65001 : Unicode)

It seems that Byte Order Mark 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 or Git for Windows. See Adding BOM to UTF-8 files.

   sed -i '1s/^/xefxbbxbf/' file.csv
   

• Tested with Excel 2016
• This is what avwtp meant in their answer.

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

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

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 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 in Apple.SE.

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.

There's an Excel addin available here 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.

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

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 it set to ANSI change it to UTF-8 and save the file as new file and then your done.