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.
• Opening csv files: If you are opening a file in .csv format make sure that 
  • Excel is opening it in UTF-8 format (to make sure your special characters such as the euro symbol or trademark display correctly)
  • you have turned off the automatic data conversion around numbers so that your codes don't lose their leading zeros and don't display as scientific notation  
  • see https://support.abcommerce.com/hc/en-us/articles/360017571398-Viewing-csv-reports for more details
• Saving your csv file:  When saving as csv - ensure that you use a UTF8 version of csv - these ensures that your csv file is using the same type of encoding as the website so characters like the trademark sign and Euro symbol will display correctly. 

• 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.  (With automatic data conversion for numbers turned off)
  • 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 if you have difficulties turning off automatic data conversion for scientific numbers

   

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.