Supplier Spreadsheet Load Tips and Tricks

Some helpful hints for manipulating Price Lists and importing products in the correct format

Manipulating Spreadsheets

When working with any type of spreadsheets in Excel/LibreOffice, the first step is to put all relevant price data into a table:

Open

Once the data is in a table, remove any unnecessary lines by using the filter options at the top of the table and deleting any blanks or useless data.

Here are a couple of useful functions for formatting data correctly:

• =CONCAT(TRIM(CELL 1), “ “,(TRIM(CELL 2)) can be used to join to cells together in cases where there are other columns that should be in the description (eg. Size for items such as Paint Brushes should be included in the description):

Open

• =LEFT(CELL 1, LEN(CELL 1) - x) will copy a cell without the last x number of characters eg (Size may be in format 10MLTR but we want it as 10ML for the Paint Levy to work)

• =TEXTBEFORE(CELL, DELIMITER) and =TEXTAFTER(CELL, DELIMITER) will return all characters before or after a certain character.
  Example, if we wanted to remove the part number from the folllowing description:
  
  

We can use:

This will return all characters before the “-”:

Similarly, we can use =TEXTAFTER(CELL, DELIMITER) to return all info after the character which will give us the description we are after:

Useful Hotkeys to know:

• Ctrl+Shift+ArrowKey will highlight all cells in direction until it finds a empty cell. Useful if trying to highlight all cells in a column that have data in them

• Ctrl+ArrowKey does the same as above but does not highlight the cells.

Once the spreadsheet is just the product data, start copying the relevant columns from the Price List into the template file. New and updated template files with the latest fields can be found in I:\Testing\Spreadsheets.

If you need any clarification on the new fields added, or what fields are available in the store’s current version, click the “Help” tab at the bottom left of the Supplier Product Load window which contains all the fields that are available in that version:

'

Open

Open

Loading the Spreadsheet

Follow the instructions laid out in this document for Spreadsheet Loads:

HOW TO Import Supplier Product / Price File

Note: Some of the new Spreadsheets may include fields that do not work in the stores version. You may encounter this prompt when you try to load the spreadsheet:

Open

This is just a warning and will not stop you from using the import. The program should just ignore the fields that it does not recognize.

Remember to select the relevant fields that you want loaded in. The program will not look at the column if the relevant checkbox is not ticked.

Pulling Specific Items Across from the Catalogue into Stock

Sometimes, stores will only want to bring some of the items that they have loaded in from the spreadsheet across to the stock file. The Comparison Report could be used, however it is generally a lot more effort than it is worth when working out the filters that suit their specific list of products they want brought across.

In 2022.5, an option has been added to the Catalogue Enquiry window to Transfer Products from Range to the stock file. There is a document detailing this process found here:

Load a range of products from the Catalogue into the Stock file

Another way of doing this is to load the products in from a CSV/Striven Scan file into the Label queue. This works really well for new stores that only want to bring across what is on their shelves. This can be done by using a mobile scanner and scanning all items on the shelf. When you load in this file, the system will look in the stock file for the products. When it can’t find them, it will prompt to load them in from the catalogue. Just hit yes to all and you will only bring over the contents of the CSV/Striven Scan file:

Open

If creating a CSV file, put the Barcode/Part Number in the first column and a “1” in the second column of Excel. Save as a CSV then load in through the label window:

Open

Open