A large part of my job is to run an SQL query, save the results as a UFT-8 encoded csv file then import it into Excel for further manipulation and distribution.
When I import the csv file into Excel the first screen of the Wizard displays the default File origin: Windows (ANSI) and I often forget to change the Excel File Origin value to UTF-8. Is there a way to set the default to UTF-8?
I face routine tasks with a similar profile to what you described, and answers like those at How to set character encoding when opening Excel and Is it possible to force Excel recognize UTF-8 CSV files automatically? miss the point. Like yours, my goal is to set a default encoding so that I don’t have to scroll down to near the end of the drop-down list just to choose “65001 : Unicode (UTF-8)” every single time I’m opening a CSV or tab-delimited-text file.
The answer that works for me is given at Changing default text import origin type in Excel. It involves a registry edit (creating and modifying a value), so all the normal disclaimers apply (i.e., edit at your own risk and back up the registry before editing).
You can change the default choice by running regedit from the
Program>Runmenu item. Once in the Registry Editor, navigate to:HKEY_CURRENT_USER>Software>Microsoft>Office>11.0>Excel>OptionsOnce here, you'll see a few registry entries for Excel 2003. Right click in the right-hand window and choose
New>DWORDCall the newDWORDitemDefaultCPGand hit Enter.Then right click on
DefaultCPGand chooseModify. Set theBasetoDecimal, and enter the decimal value for Unicode UTF-8 (65001in this case), then hitOK.
(quoted from Changing default text import origin type in Excel)
The instructions are from 2005, but I found that they worked for Office 2016 if where the directions say “11.0” (as the Office version), I assumed “16.0” instead. I also closed Excel (and Outlook, with its Excel previewer) before making the edit, but I can’t confirm if that’s necessary.
