Bulk Stock Import and Update

The Bulk Import and Update functionality is mainly used to import and update products from a CSV (Excel) file. You can also export products to an Excel file, update the file and then import it again.

If you want to export product information in a specific format for specific programs like scale software, then a better option would be to use the Export Manager.

Importing products from Excel or another program

The first step is to get your spreadsheet in the correct format for the import.

The file should only have the columns specified below and the header row should have the names for the columns as specified below. (All in upper case).

• STOCKID
• STOCKCODE
• BARCODE
• DESCRIPTION
• PRICE
• PRICEINC
• PRICE2
• PRICEINC2
• PRICE3
• PRICEINC3
• TAXDESCRIPTION
• UNITCOST
• DEPARTMENT
• CATEGORY
• ROP
• REORDERQTY
• STOCKONHAND
• AISLEBIN
• SUPPLIER

You do not need to use all the columns but these are the only columns that should be in the file. For example, if you are updating the price of existing products, you only need two columns - the first column that identifies the product (STOCKID or STOCKCODE or BARCODE) and STOCKONHAND.

The columns in the file can be in any order but the first column must be one of the following:

• STOCKCODE
• BARCODE
• STOCKID

The first column will be used to determine if a new item must be added or if an update must be done. If the product with the item in the first column is found, that product will be updated. If no item is found, a new product will be created.

Note about STOCKCODE:
The StockCode in ROBOTILL is not required to be unique (like the BARCODE and StockID). If you choose to use the STOCKCODE as your first field (record identifier), you need to use unique stock codes within your organization. If STOCKCODE is used in the first column, it will update the first record it finds with the same Stock Code if there is more than one.

More information about the columns:

• STOCKID - The StockID is unique per system. Only use the StockID if you are updating products (if the file was exported from the same ROBOTILL installation). If you are importing files from another ROBOTILL installation (seperate database), then you need to delete the StockID column and use the STOCKCODE or BARCODE as the first unique column.
• STOCKCODE - Product Stock Code. See note above if used in first column.
• BARCODE - It is important that you format the barcode column in your spreadsheet as a text column before you save the file as a CSV file. Otherwise it is possible that your spreadsheet application can change the bardode numbers using scientific notation. For example a number like 8694740101237 can be changed to 8.69E+012 if the column is not a text column.
• PRICE - Price Excluding Tax (see note about price below)
• PRICEINC - Price including tax (see note about price below).
• PRICE2 - Second Tier Price Excluding Tax (see note about price below)
• PRICEINC2 - Second Tier Price including tax (see note about price below).
• PRICE3 - Third Tier Price Excluding Tax (see note about price below)
• PRICEINC3 - Third Tier Price including tax (see note about price below).
• TAXDESCRIPTION - The Description of the Tax item as specified in ROBOTILL.
• DEPARTMENT - The department name as specified in ROBOTILL.
• CATEGORY - The category name as specified in ROBOTILL.
• ROP - Re-order Point
• REORDERQTY - Re-order Quantity
• AISLEBIN - Ailse-Bin (product location within store)
• SUPPLIER - Supplier name as specified in ROBOTILL.

Note about product price
If you want to update price, you only need to use either PRICE or PRICEINC and not both. The other will be calculated.

Importing the file

Before you do the import, remember to do a backup as the import action can affect a lot of records in the database.

To do the import, in ROBOTILL Manager select 'Products and Stock' from the menu and then 'Bulk Stock Import and Update'.

Select your CSV file and click on Import. The number of records that was imported as well as any problems will be displayed.

Common Problems and Fixes

An easy way to find problems is to look at the raw text file. To do this, right click on the file, select 'Open With' and then choose Notepad.

Depending on the columns you chose to import, the file should look something like the example above. It should have a header row and each field should be separated with a comma.

First column must be STOCKCODE, BARCODE or STOCKID, but it is correct

Check the spelling of your column header. If it is correct and one of the 3 above, then the problem is probably your field delimiter.

Have a look at your file in notepad, the columns must be separated by commas. If it is not change the delimiter.

Barcode - Scientific Notation

One problem that often occurs is that the number of records that is being updated is much less than the number of records in the file. This can be because you used the BARCODE field as the first column and your spreadsheet program treaded the barcode column as a number. If that happens it might use scientific notation instead of the actual numbers.

This will cause a lot of the barcodes to be the same and not be valid barcodes. To fix this all you need to do is format the barcode column as a text column before you save it to CSV.

Invalid Column: (no name is given)

Another problem is that you might get an error 'Invalid Column:' but no column name is given. This means you have an empty column in your CSV file.

Open the file in notepad as explained above to find the empty column. In the header record there will be two commas next to each other. All you need to do is remove the empty column. Not from Notepad as you need to delete the entire column and not just the two commas in the first line. You can either do it the CSV file or the original file (then you will need to save the CSV file again).

In your spreadsheet the empty column will not be visible if it is at the end as the spreadsheet will have many empty cells in the end. Just delete a couple of columns at the end as shown above.

Invalid number of columns due to decimal separator

If your country is using a comma for the decimal symbol, then it can cause the 'Invalid number of columns' error. To fix this, change your decimal symbol to a dot.

Updating products using Excel

Although you can update prices in a grid format in ROBOTILL (see Changing Prices and Specials), some people prefer to use Excel. You can also do other product updates (stock codes, descriptions, barcodes, etc) in Excel (or another spreadsheet program).

To use this functionality to update products, you first need to create a CSV file. In ROBOTILL Manager, from the Menu select 'Products and Stock' > 'Bulk Stock Import / Export and Update'. Select the 'Export stock for update' tab.

Select a file that must be created. Select the fields you want to export and update. You only need to export the fields that you want to update. Once the file has been created, you can open it in Excel or another spreadsheet program and do the updates you want.

The first column in the file is the STOCKID (Stock ID). This column will always be added when you export a file from ROBOTILL (unless you use the Export Manager). The StockID is a unique field and is a system allocated number. You should not edit this number. You can also not create your own StockID's in a file. This column is added so that you can update other unique fields like the barcode and stock code.

If you want to create a file where you want to update existing items and add new ones, you will need to delete the StockID column and use either the barcode or stock code as the first column. This column will then be used to decide if a record must be updated or a new one created.

Once you made the changes to the products in the file, you can just import it again. Remember to first to a backup.