My report shows invalid characters in the export, such as São and ’. What causes this and how can I correct it?

It's possible that users may have copied data into a rich text field directly from another source such as Microsoft Word, which can carry over formatting that doesn't render correctly in programs such as Microsoft Excel - even if it displays correctly within CM.

There are two ways to correct this: 

Correcting the data in CM

This option is the best way to ensure your reports continue to work as expected, and your data always displays correctly.

When users are entering data into a rich text field, it's common for them to copy from another source. That source could be an email or a document that has been shared with them. Some very common issues with copying from another source involve the smart quotes or apostrophes (often seen as a curly quote).

When a 'smart quote'  is used in a program like Word, Powerpoint etc, those programs can handle them fine, but most editors require the straight quote to make sure that it can render correctly online.  It's the difference between "this" (the quotes here are straight up and down) versus “this” (these are the smart quotes, which are slightly angled).
 
To make sure content displays correctly, the smart quote ” should be converted into the straight quotes ". - that will apply with apostrophes as well. If you're pasting into the editor from another source (like a word document, as that is usually where it is copied from) then you can use the Paste as Text option, or you can use the short-cut of SHIFT+CTRL+V (or OPTION+Shift+⌘+V on Mac) to make sure the special formatting is removed. 

Force Excel to display the characters

When you download a CSV file, you can open it in a spreadsheet program such as Microsoft Excel. However, if the CSV file contains special characters that are not included in basic Latin script, these special characters may not display in Microsoft Excel.

 You can force Excel to display them with the following steps:

• Open a blank workbook in Excel
• Select the 'Data' tab, and then select 'From Text/CSV'

• In the next window, select the CSV file
• From the next window, make sure that 'File origin' is set to Unicode (UTF-8)

• Click 'Load' at the bottom
• That should load the data and display the character, like this:

Opening this file in the steps above should render them correctly for display, but if you want to correct them in the data (recommended) you'd want to replace the ones that appear as ’ with the standard '.