Share via

cannot open UTF-8 CSV file that contains Chinese with excel correctly

Anonymous
2022-12-04T10:16:33+00:00

I have a CSV file encoded with UTF-8, generated by myself on Mac, contains Chinese, English, and numbers. I can open it correctly with Mac text editor, VScode, Mac Numbers, and google forms. However, excel seems to have some issue with it, Chinese become some random characters.

I have tried and failed:

  1. right click file -> open with excel
  2. In excel, File -> Open...
  3. In excel, File -> Import
  4. In excel, Data -> acquire external data

I have read this article How to open UTF-8 CSV file in Excel without mis-conversion of characters in Japanese and Chinese language for both Mac and Windows and many other articles. Tried all the steps correctly, still failed.

The file I have is here https://drive.google.com/file/d/1gm3l6kKPWbNlmu-POvh8NKl0fHWFGA4A/view?usp=share_link. if you want to try it yourself.

I believe the standard procedure is helpless, and probably need to ask engineers to fix the bug. Or I missed something important.

Microsoft 365 and Office | Excel | For education | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-12-05T10:43:00+00:00

    You are welcome.

    as to bash, sed also works.

    sed "$(printf '1s/^/\357\273\277/')" utf8.csv > utf8-BOM.csv

    0 comments
  2. Anonymous
    2022-12-05T09:21:16+00:00

    Thank you!!!

    I was using python to generate csv files. Changing to with open(filename, "w", encoding="utf-8-sig") is helpful.

    And I can't download Notepad++ on Mac, so I tried sublime and vscode to convert utf8 to utf8-bom, also works pretty well.

    I also tried adding magic number to the file. It also worked.

    
echo -ne '\xEF\xBB\xBF' > utf8-BOM.csv

cat utf8.csv >> utf8-BOM.csv

    But I guess the Microsoft engineer team still need to fix this problem lol.

    0 comments
  3. Anonymous
    2022-12-04T12:46:00+00:00

    I'm on Windows 10 and didn't have a problem (I should have added a 'promote first row to headers' step):

    The settings which came automatically were:

    0 comments