How to Fix the UTF-8 Encoding Issue when Creating CSV Files in Python 3.9.x for Excel

Learn how to properly handle `UTF-8` encoding in Python 3.9.x to ensure non-English characters display correctly in Excel.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Python 3.9.x created CSV with non-English (Unicode) characters (UTF-8 encoded) does not show correctly when opened in Excel (Windows)

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Fix the UTF-8 Encoding Issue when Creating CSV Files in Python 3.9.x for Excel

Creating CSV files with non-English characters in Python can be a tricky endeavor, especially when you're transitioning from an older version like Python 2.7 to the more modern Python 3.9.x. In particular, users often face issues with how Microsoft Excel interprets these CSV files, leading to display errors.

In this guide, we'll walk you through why these problems occur, and importantly, how to fix them so that your CSV files open in Excel labeled with the correct non-English characters.

The Problem

When I upgraded from Python 2.7 to 3.9.x, I encountered a string of issues while trying to create a CSV file that contained non-English (Unicode) characters. In my previous code, I implemented the non-recommended hack of changing the default encoding to UTF-8:

[[See Video to Reveal this Text or Code Snippet]]

To ensure compatibility with Excel, I also added the Byte Order Marker (BOM) at the beginning of the file:

[[See Video to Reveal this Text or Code Snippet]]

This method worked flawlessly with Python 2.7, allowing me to view non-English characters perfectly in Excel. However, once I transitioned to Python 3.9.x, I discovered that the default encoding was already set to UTF-8, rendering my old methods ineffective.

The Solution

After conducting extensive research and numerous trials, I discovered the necessary adjustments needed:

1. No Need for BOM

The first realization was that my removal of the BOM ("\xEF\xBB\xBF") was indeed correct. It is not required in the CSV file for proper display when using the right settings in Python 3.9.x.

2. Specify the Correct Encoding

The missing piece of the puzzle was adjusting the open() function to specify the correct encoding. The trick is to replace the outdated encoding with utf-8-sig instead of just utf-8. Below is the correct implementation:

[[See Video to Reveal this Text or Code Snippet]]

Why utf-8-sig?

The utf-8-sig encoding expects a BOM at the beginning of the file but does not retain it in the actual content of the file.

This means that while the BOM is necessary for Excel to correctly interpret the character set, it won't show up as part of the data itself, leading to a clean display of your non-English characters.

Summary of the Adjusted Code

Here is a brief overview of the corrected code for creating a CSV file in Python 3.9.x:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Making the switch from Python 2.7 to Python 3.9.x requires some adjustments when handling CSV files, especially those containing non-English characters. By specifying utf-8-sig in your open() function, you can ensure that your CSV files will display correctly in Excel.

I hope this guide helps anyone encountering similar issues with encoding in their Python projects. Happy coding!