One common issue reported by customers is that certain special characters appear incorrectly when opening PXM CSV exports in Excel. The technical reason for this is that PXM uses the international web standard UTF-8 encoding for text output, while Excel uses a Microsoft-specific encoding when reading CSV files. This incompatibility often causes special characters to be displayed incorrectly.
If you have the option to use Google Sheets or Apple Numbers, you’re in good shape—both applications adhere to the UTF-8 standard and will read your CSV files without issues.
If Excel is your only available tool, the easiest solution is to export your data as an XLSX file instead of a CSV. You can do this by selecting the XLSX format at the time of export from PXM. XLSX files are specifically designed for Excel and will preserve both special characters and data formatting, such as leading zeros in UPC codes.
If you need to work with CSV files in Excel, be aware that opening them directly can result in unwanted formatting changes, as Excel makes assumptions about data types and encoding. For example, leading zeros in numbers might be dropped, and special characters may not display correctly.
If exporting as XLSX is not an option and you must use CSV files, you can still successfully import them into Excel by following a few extra steps. Here’s how to manually import a CSV into Excel (these instructions are for Excel on Mac, but the process is similar on Windows):
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.