The problem
One of the most common issues we face when importing data into our Maropost store is that the leading zeros for SKUs can sometimes be stripped out if they are entirely numerical. The ability to have your csv retain leading zeros is crucial to success when uploading information to your webstore.
For example:
0001234 becomes 1234
Often, people misattribute this to Maropost and some limitation, but the issue actually lies in the way Microsoft Excel interprets numerical data in a cell.
When Excel opens a CSV file (this is not an issue for excel formatted files with the xlsx extension), it checks each cell to determine what kind of data is in each cell. By default, Excel will interpret entirely numerical fields as being numbers, in which case the 000 in the above example is redundant and removed.
Then, when we save it, we end up with SKUs we never intended.
The solution
To enable us to have our csv retain leading zeros, we need to use Excel a bit differently. Instead of our usual method of double clicking on the CSV to open it, we need to first open Excel and follow the below steps:
- Open Excel and navigate to the
Data
2. Click on From Text/CSV
3. Select the CSV you want to work on.
4. Select UTF-8 from the File Origin dropdown. This encoding is generally sufficient, but if you have special characters that don’t show up properly, you may need to experiment here.
5. You should get a screen like this at this stage with your annoyingly zeroless SKUs. Here, we right click on the query in the right hand side of the window and select Edit
6. On the right hand side of this intimidating screen we can see a little X next to Changed Type. This is part of a list that tells us what it did when importing the data. Select the SKU field from the table by clicking the header (and any other fields you want to preserve the zeros on) and click that little X.
7. Exit out of the query editor and be sure to select keep!
8. Now, as we can see, the zeros are back where they belong and will stay that way when you save!
I know this is a somewhat convoluted way of doing this, which is why I don’t use Excel for these tasks. I use free software called Libre Office which I will cover in an upcoming post. If I missed anything important, please feel free to contact us for more clarification.
In the meantime, I hope this has been helpful and I will talk to you next time!