128

Is it possible to set the default encoding for Excel (any version, e.g. 2010) when opening files like csv files (like you can in Open Office Calc)?

I When I try to open a csv file encoded in Japanese SHIFT-JIS, it opens but with mojibake (corrupted characters). In Open Office Calc, if you click on a csv file with a non-standard encoding like SHIFT-JIS, it automatically opens a dialog where you can set the encoding.

Improve this question
3
  • Which version of Excel? Commented May 9, 2011 at 9:26
  • 2003 or 2010 (I skipped 2007) Commented May 10, 2011 at 17:49
  • for web developer: if you have a CSV string, and want to create a download link in html for downloading thie CSV file, you can 1. make your CSV file UTF-8 encoded by using "data:text/csv;charset=utf-8,"; 2. append BOM for UTF-8 to CSV string to let Excel open CSV file with correct charset (UTF-8 in this case). see Commented Aug 14 at 12:38

5 Answers 5

Reset to default
134

Use the import function under "Data" tab where Excel allows us to specify the encoding. Select "from text" and choose your csv file then Select Japanese shift-jis encoding.

Improve this answer
10
  • works perfectly for xl2007 and xl2010 Commented Apr 27, 2012 at 15:05
  • 2
    Works also in Excel 2016 Commented Dec 2, 2015 at 12:53
  • 2
    On Excel 2016 for Mac: first open blank worksheet, next in main menu go to "Data" -> "Get External Data" -> "Import Text File". Commented Jun 6, 2016 at 11:42
  • 11
    Downvoted, because using the import function (or text into columns assistant) means line breaks within cells will be messed up (this is broken in Excel ever since). It will wrap the next line into a new row and therefore trash the entire data layout below. Commented Jan 18, 2017 at 21:30
  • 2
    Also not an answer to setting a character encoding when directly opening a csv file in Excel. The whole point of setting the encoding is to eliminate these repetitive and annoying required steps. Commented Sep 18, 2018 at 9:27
11

The following steps work with Office 2003.

  1. Rename .csv to .txt
  2. Open .txt file from Excel (don't do it with right click on file then open with Excel), Excel will open a Text Import Wizard dialog, ask ask for the format of .txt file, including character encoding of text file.
  3. Rename .txt back to .csv after your edit is finished.
Improve this answer
1
  • Works for office for Mac 2016 as well. Commented Mar 15, 2024 at 11:40
5

On Excel 2016 for Mac:

  1. create blank worksheet,
  2. in main menu go to Data -> Get External Data -> Import Text File,
  3. follow steps in wizard - choose the encoding until you will see the correct preview and on the next step choose columns delimiter (delimiters differ from csv to csv files).
Improve this answer
2

See if this helps (MS Excel 2007 and above).

  1. Open Excel and click the Microsoft Orb at the top and then click on Excel Options.

  2. Go to Advanced, and then look for the Web Options button. It should be under General.

  3. Go to the Encoding tab and pick Japanese Shift-JIS from the drop-down menu. Click Ok, restart Excel and try to open your file.

Improve this answer
2
  • 4
    This didn't work. My system is a Japanese system and so SHIFT-JIS is already set here as the default. But, I have my default editing language set for English (with Japanese also added). If I set the default editing language to Japanese, then the SHIFT-JIS csv file will open correctly. But this is messy. What if the file was some other encoding (e.g. Korean, Chinese)? What I really need is a place to set the encoding of the file (like I can in Open Office Calc). Commented May 10, 2011 at 18:12
  • 1
    This should work for saving, but not for opening. I am wondering if it actually makes sense to choose an encoding when opening a file. Would this fix the issue ? Commented May 12, 2015 at 14:05
1

Lozzer provided great feedback in the small print: "If I set the default editing language to Japanese, then the SHIFT-JIS csv file will open correctly...."

I have spent hours looking for a solution on how to open Japanese CSV with one simple click We have a multi-language environment (En-Jp).

Even though Excel showed Japanese menus (based on the control panels language settings), the default editing setting in File > Options > Language > Choose Editing Language (Top area) remained English. Changing that to Japanese (obviously no issue for Japanese users) solved our problem.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.