One common thing we hear from customers is that some special characters get treated strangely when opening Amplifi exports in Excel. There is a nerdy reason for this and that is that Amplifi utilizes the international web standard UTF-8 encoding for our text output and Excel utilizes a Microsoft standard for encoding when it reads the CSV files, thus converting special characters in to strange characters. If Google Sheets or Mac Numbers is an option for you, then you are in good shape as they both also adhere to the UTF-8 standard and will read your CSV files seamlessly.
What if Excel is your only available tool? Unfortunately, while Excel support opening CSV files directly into the application, it also makes a lot of assumptions about your data and your encoding and you end up with a lot more changes than you wanted. for example, you might have UPC number with a leading 0 in the code and Excel will drop any leading zeros when it pulls integers in and it converts them to number format. Also it reads your files with its own encoding and that will cause the issues with special characters.
There is good news for you if you use Excel, but successful reading of CSV files requires a few more steps. Here is a step by step overview of how to import a CSV into Excel (NOTE: these steps are for Excel on Mac, but should be similar for Windows). Once you have your CSV output file follow these steps:
Open Excel, this will create a new spreadsheet
Go to File > Import
In the next step choose CSV file (should be default) and click Import
Chose the file you wish to open
Choose Delimited and IMPORTANT choose File Origin. By default on a Mac, Excel assumes Mac encoding so that will be the selected option. You need to choose Unicode (UTF-8) and click Next>
Choose your delimiter which will be Comma. Click Next> OR if you are fine with Excel reading your data and assigning a format, you click Finish and your import will be opened
If you didn't click Finish in step 6 then you now have the option to set your cell format by column. I like to set all columns to Text, but painfully you need to do this column by column which is a little tedious. Once you have finished selecting your output, click Finish and now you have a UTF-8 formatted spreadsheet.
Following these steps will ensure your special characters are preserved. Click this link for steps in Google Sheets. Also learn more about other CSV tools here.