Most users import data into the Salsify PIM via Excel (XLSX) and Comma Separated Values (CSV) files. While these two file formats are compatible, they are not equal.
Too often I help clients resolve poor quality data in Salsify caused by a CSV file opened in Excel. After reading this article, you’ll have a better understanding of CSV files and how to protect the integrity of your data.
What is a CSV file?
Comma Separated Values (CSV) file is a plain-text file format that has data delimited by commas separating each data value.
For an import to another system like Salsify, typically you will also have a header row with column names.
If a comma is used within a piece of data, a “text qualifier” (usually double quotes) is used to enclose the string, such as:
,”I love Salsify, PIM, and eCommerce.”,
CSV files do not contain any formatting – nothing will be in bold, italic, colors, highlighting, etc.
Anatomy of a CSV File
I’ve opened my CSV file in Windows Notepad for my example (on a Mac you can use TextEdit).
My file has a header row, and two rows of SKU data.
Notice the UPC codes with a leading zero, long integers for MOQ data, and Launch Dates in YYYY-MM-DD format.
Mischief in Excel
Double-clicking the CSV file to open it, instead of purposefully opening it in a text editor as I did above, launches Microsoft Excel. Below you can see the three most common problems with comma separated data improperly opened with, and passed through, Excel.
- Dropped leading zero on UPC codes
- MOQ long integer converts to scientific notation
- Dates converted to a whole number
If we re-save this file as CSV*, and then import it to Salsify, we’re carrying these data problems forward into our PIM data:
*If you save the file as Excel for import, the MOQ long integer will be correct, but the bad UPC codes and dates carry forward.
If UPC code is your Product ID, importing without leading zeros exposes you to creating new product records if your Salsify import settings are not adjusted to prevent it.
If the UPC data is a property in your data set, but not your Product ID (like in our blog example), you may be overwriting good data in Salsify with the bad data, which will affect syndication to your retailers/endpoints and may affect syndication of Enhanced Content from Salsify.
Mischief Managed
These problems are easily prevented! Leverage any of the following solutions:
NEVER double click CSV files to open them in Excel
1. If you need to preview the data, right-click on the filename and open the CSV file in a Text Editor.
Popular text editors:
- Windows Notepad
- Apple TextEdit
- Visual Studio Code
- Notepad++
2. If you must work with the data in Excel, use the Get Data function (Data > From Text/CSV).
This will open Power Query. Click on “Transform Data”. Then click on “Data Type:” in the command ribbon and choose Text to prevent conversion to incorrect formats:
3. Turn on and use the “Legacy Wizard From Text” import function in Excel. This is less complex to use than Power Query. To make it available in your Excel:
- Navigate to “File > Options > Data”
- Show legacy data import wizards
- Checkmark “From Text (Legacy)”
- Click OK
Next, click on Data > Get Data > Legacy Wizards > From Text (Legacy).
Define any/all of your columns as Text in Step 3 of the wizard.
File Encoding
Without getting technical, know that the “encoding” of a file matters to how the data will render on screen when you open the file. Salsify prefers the UTF-8 encoding standard.
If you’re working with data in languages other than English, or if you have special characters in your data such as the copyright or trademark symbols, you may need to play around with file origin or encoding types when you import the file to Excel so that special characters will render correctly.
In Windows Notepad, you can see the encoding of a file in the lower right corner of the window.
Wrapping Up
Don’t think of CSV files as another type of Excel file. CSV files are CSV files for a reason. You’ve seen here why you CAN open CSV files in Excel with a double-click, but it does not mean you SHOULD.
It’s a few extra clicks to import a CSV to Excel rather than opening it with Excel. However, it’s much safer. If you don’t have time to “do it right”, you likely don’t have time to “do it over”. Backing out thousands of bad records, or tracking down why hundreds of UPC codes are wrong when you’re in a rush to syndicate an important Salsify channel takes much longer!
Follow my tips to use a text editor or Excel “Get Data” functions. Your data will be sparkling clean and runway ready!
If you need more help with Salsify beyond impressing your co-workers with your knowledge of CSV files, reach out to our Salsify-certified PIM experts at Sitation! We’re here to guide you through Salsify onboarding, data modeling and remodeling, user training, special projects, managed services, and more. We look forward to working with you.
