Tips on working with spreadsheets for bulk import

• Always work on your bulk import spreadsheets in excel format (.xlsx not .csv). Only convert to .csv at the last minute, just before you upload your spreadsheet.
• 
  
  • Working in .csv format can cause issues like dropping the zero from the start of a code.
  • Working in xlsx format allows you to save excel formulas eg to show when a product title has exceeded the max length of 100.
• Format fields with codes.  If you have a ProductCode or barcode which is all numbers, excel will treat it as a number, this can cause issues eg dropping zeros at the start of the code or displaying it as a scientific number if there are a lot of digits in the code.  The following formats may be helpful:
  • Format as "Text" - in Home menu, under the number section of the ribbon choose Text from the dropdown
  • Format as Custom, 0.  - in Home menu, under the number section of the ribbon, more more number formats from the dropdown.  Choose Custom category and Type 0.  This will stop the display of scientific numbers
• When saving as csv - ensure that you UTF8 version of csv - these ensures that your csv file is using the same type of encoding as the website so characters like trademarks will display correctly.  

Helpful formula and excel functions

Finding duplicates

Having more than one record (line) with the same unique code (key) in your spreadsheet will return an error in the bulk imports eg a product import with the same product code used on two different lines.  You can use excels conditional formatting to highlight any duplicates as shown in the following image.

• Open an excel version of your document (.xlsx not .csv) - Formulas are not saved in the csv format.
• Select the column(s) you wish to check for duplicates by click at the top of the column 
• From the home menu click the conditional formatting dropdown from the ribbon and chose Highlight Cell rules
• Choose duplicate values.  Click ok.
• If you have a lot of lines in your spreadsheet, you can sort the data so that the duplicates appear at the top - Select all your columns, from the home menu select SORT & FILTER.  Sort by the field with duplicates and sort on Cell Colour.

You can remove duplicates using the "Remove duplicates" functionality in excels DATA tab.

Checking the length of fields

Some fields have a maximum length, for example the Product Title has a maximum of 100 characters, a product's short description fields has a 250 maximum and the fields in the descriptions in the accordion have a 2500 characters maximum .  You can use the Len function in excel to calculate the number of characters in a field.  You can use conditional formatting to highlight any fields where the text is too long or sort the data to show the longer lengths at the top of your spreadsheet.  You will need to delete this field in the csv version of the file that you upload.