Default character encoding for Excel Text Wizard?

Question

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?

Answer 1

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>Run menu item. Once in the Registry Editor, navigate to: HKEY_CURRENT_USER>Software>Microsoft>Office>11.0>Excel>Options

Once here, you'll see a few registry entries for Excel 2003. Right click in the right-hand window and choose New>DWORD Call the new DWORD item DefaultCPG and hit Enter.

Then right click on DefaultCPG and choose Modify. Set the Base to Decimal, and enter the decimal value for Unicode UTF-8 (65001 in this case), then hit OK.

(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.