How to fix special characters, accents, and emojis with Excel CSVs

Problem

Your text or symbols are garbled after being exported. This happens because of an encoding issue with Excel.

What we entered:

🎉 Awesome, you did it!

What was exported:

"🎉 Awesome, you did it!"

Solution

There different solutions depending on which version of excel you are using.

2010-2016 Excel

The first thing you need to do is open your export. In the export file, click the Data tab, New Query, From File, then From CSV.

Doing this opens file explorer. In the file explorer, select the export report you are working on and click Open. Clicking this opens a settings page for importing your CSV.

On the settings page, set your File Origin to 65001: Unicode (UTF-8), and your Delimeter to Comma. Once this is all set up click Load.

Clicking load creates a new worksheet and populates it with your data. If you did not use linebreaks, your worksheet should be fixed. However, if you dd use line breaks, you will see “\r” where they occur. For this example, we have “\r” in the description. If you have them elsewhere, your formula might look a bit different.

First, we make a new column next to the description column. We then enter the formula "=SUBSTITUTE([@description], "\r",CHAR(10))", This formula replaces all of the "\r" with char(10), which is a line break. Next, copy the new column and paste it as values. Then delete the description column. Finally, rename the new column to "description".

Newer Versions of Excel

This process is similar to the 2010-2016 excel process. The only difference is where you find the From Text/CSV button. To do this, go to the Data tab, click Get Data, then From File, and finally From Text/CSV.

Everything after this point is the same as the 2010-2016 versions of excel.