One of our users wanted to know how they can import products using Excel from various suppliers for a market. It is small local suppliers that do not have proper product codes (PLU, SKU, barcodes, etc). They also want to allocate all the products of a supplier to a specific department in ROBOTILL. We have decided to give an example of how this can be done on our blog to also help other ROBOTILL users with a similar requirement.
Deciding on how to use unique product codesWhen you import products using the Bulk Product Import feature of ROBOTILL it is important to use unique product codes or barcodes. When importing products from various suppliers that don't have international unique product codes, you can do one of the following:
- Allocate product codes yourself - this can work but will be time consuming.
- Ask the suppliers to allocate their own unique codes - the problem is that there might be suppliers that choose the same product code (by coincidence).
- You can use the option above but then add a supplier unique prefix or suffix to the supplier codes. You might be able to add the prefix/suffix using Excel - but we will use the option below.
- The option we will use in this example is to email each supplier an Excel sheet where they must enter their products with their own unique product code and then also add an allocated supplier prefix to each code.
Step 1: Creating a department for each supplier
For each supplier you can create a department. We will just use the supplier name for the department name. In this example we will import products from a supplier named 'Riley'.
We create a department named 'Riley'. To add a department go to ROBOTILL Manager > Setup > Departments.
Step 2: The Supplier Product List
You also need to decide on a product prefix for each supplier. For our 'Riley' supplier we will ask the supplier to add the prefix 'R-' to all his product codes. You could email the supplier something like the following Excel Sheet:
The supplier then emails you back the completed form:
(we kept the list short for this example but ROBOTILL can import any number of products)
You now have product codes that will be unique across all suppliers.
Creating the import fileYou can now create your import file. You can combine all the supplier sheets into one sheet or import each sheet separately.
The sheet you received from the supplier is not in the correct format, so you will need to copy the list of products from the original sheet into a new one and make the following changes:
- You can only have the rows and columns with the data you want to import - so all other information and headings we had in the original form needs to be removed.
- We also need to use the headings as they are defined in the Bulk Import Online Help. So 'Product Code' needs to be changed to STOCKCODE and the other two headings needs to be changed to upper case.
- We also need to add a DEPARTMENT column and add the name of the department as it was created in ROBOTILL. For this example it will be 'Riley'. (remember in Excel and OpenOffice you can copy a single value into the entire column).
We can now save the file to CSV format. To see how to do that, see the Bulk Import Online Help.
The new CSV file will look like this (if you open it using notepad):
Importing the file into ROBOTILL
You can now just import the file into ROBOTILL (just have a look at the online help if you don't know how/where to do this).
That is it. The end result will be that the products would have been created in ROBOTILL and allocated to the department we wanted.
You also could have added the stock on hand to the original file you emailed the suppliers so that you can also allocate product quantities during the import process or you can simply use stock receiving when you receive the products from the supplier.
Have a look at the bulk import online help if there is more information you want to import for the products - like a re-order point (ROP), second and third tier prices, TAX information, etc.