Bulk Import Of Products

[JohnS]

Now I normally don't post without having a completed solution ready to go, but I am very happy with my results so far and decided to share with everyone.

Firstly, this is a Work In Progress, and is not yet completed - but it is very close.

I have spent the last 3 nights writing a script to bulk import Products into SambaPOS.
Why? Well I need to import 2,500 product lines into SambaPOS very soon, and I was not going to type each in manually

The result is as follows
- Import Products, Product Groups, Barcodes and Pricing
- Create Inventory Items (Optional)
- Create Recipes (Optional)
- Create Inventory Document for opening stock levels
- Create Menu Categories with Products and Sub Categories (Optional)
- Custom Menu Category & Item Button Colours

Limitations
- Only 1 Portion Price is added
- Designed for packaged products only. Recipes are 1:1
- Preset Menu Button Colours and Size used - 45
- Based on PHP script using MYSQL loaded with the products to import
- If script is run a second time it will add all products again
- Only supports SQL Express Databases
- CSV must by sorted by GroupCode before importing
- Adds products to last Menu created

To Do List/Wish List
- Import via CSV file directly (no MYSQL needed) - Done
- Multiple Portions
- Custom Button Colours - Done
- Alternate Product Names for Menu Buttons - Done
- Check if Product/Inventory/Recipe/Menu exists before importing
- Rewrite using VBS scripting Works fine using PHP
- Make it dead simple to use

So, hopefully over the next week I will have this ready in an easy to use solution.

[JohnS]

Setup And Usage Instructions

Requirements
- SQL Express Server 2008+
- WampServer 2.4 (Only used to run PHP script)
- Download attached file below

Installation Notes
** Always backup your Database files before using this tool **
- This is designed for SQL Express databases only and for people who are familiar with using SQL and know how to back up their database files.
** If you do not understand any of these instructions then DO NOT USE THIS TOOL **
- I will take no responsibility for lost or damaged databases.
- Best performed on the same computer as the SQL Express Database

Setup
- Download and install WampServer 2.4 using default settings
- Unzip 'SambaPOS3 Product Import V1.zip' and place files from 'www' folder into c:\wamp\www

Using Importer Tool
- If starting with a blank database, you will need to Start a Work Period and have atleast one Menu defined
- Products will be added to the last Menu created. So if you created Restaurant & Bistro, then the Products will be added to the Bistro Menu.

- Edit load_products.php file in c:\wamp\www and set your specific details. Items in Red are critical to this working correctly.

// SQL Express Server Details - Set to your details from your connection string
// example: data source = localhost\SAMBAPOS3; user id=sa; password=sambapos
$myServer = "localhost";
$myUser = "sa";
$myPass = "sambapos";
$myDB = "SambaPOS3";

// Timezone - http://php.net/manual/en/timezones.php
date_default_timezone_set('Australia/Sydney');

// Menu Defaults
// Colours can be by Name or by RGB Code (#AAFF00) - http://colorschemedesigner.com/
$DefaultCategoryButtonColour = "Orange";
$DefaultMenuItemButtonColour = "Green";

$CategoryButtonHeight = "45";
$SubCategoryButtonHeight = "45";
$MenuItemButonHeight = "45";

$DefaultColumnCount = "2";
$DefaultNumberPad = "1"; // 2-Large, 1-Small, 0-None

$MenuButtonLength = 32; // Creates automatic Product Name wrapping on Menu Button


- Edit import.csv in c:\wamp\www and update with your products and save.
Note: You cannot change the order of the Columns.

CSV Columns
- CreateMenu - Menu Name/N - Create a Menu Item Button for this Product. If you list the Menu Name here, the product is added to that menu
- CreateInventory - Y/N - Create an Inventory Item & Recipe for this Product and import stock levels
- GroupCode - Text - Product Group Code, Inventory Group Code and Menu Category
- SubGroupCode - Text - Menu Sub Category
- Barcode - Text - Product Barcode
- ProductName - Text - Product Name
- MenuName - Text - Menu Item Name - If blank Product Name is used
- InventoryName - Text - Inventory Item Name - If blank Product Name is used
- PortionName - Text - Product Price Portion Name - if blank Normal is used
- PortuonMultiplier - Number - Product Portion Price Multiplier - if blank 1 is used
- SellPrice - Number - Product Portion Price
- BaseUnit - Text - Inventory Base Unit - if blank Ea is used
- TransactionUnit - Text - Inventory Transaction Unit - if blank Ea is used
- TransactionMultiplier - Number - Inventory Transaction Multiplier - if blank 1 is used
- RecipeQty - Number - Inventory Item Quantity per Recipe - if blank 1 is used
- InventoryQty - Number - Starting Inventory Level
- CostPrice - Number - Inventory Item Cost Price
- Warehouse - Text - The Warehouse the Inventory Item is added to
- CategoryButtonColour - Text - Menu Category Button Colour - if blank Orange is used
- MenuItemButtonColour - Text - Menu Item Button Colour - if blank Green is used


- Open Web Browser and goto address 'http://localhost/load_products.php' ***This will import immediately*** and you should get a result like

Start : 23-07-2013 00:07:13
Connected successfully - MSSQL

Menu Category - OIL
MOTOREX TOP SPEED 4T 10W40 1 LITRE
MOTOREX TOP SPEED MC 4T 10W40 4 LITRE
MOTOREX FORMULA 4T 15W50 1 LITRE
MOTOREX CROSS POWER 2T 1 LITRE
MOTOREX CROSS POWER 4T 10W50 4 LITRE
Motorex Cross Power 4T 10w50 4Litre
MOTOREX 4 STROKE 10W/40 4 LITRE
Menu Category - AIR FILTER
MOTOREX AIR FILTER OIL 206 1 LITRE

Finish : 23-07-2013 00:07:13
Execution Time : 0


- Run SambaPOS3.
- Enjoy


Additional Tools
In 'SambaPOS3 Product Import V1.zip' there is a folder called 'Clear SambaPOS DB' which contains the following tools - Use at your own risk as they wipe data directly from your database.

DELETE V3 Prod Inv Recipe MenuItems.bat
- This clears all Products, Inventory Items, Recipes, Menu Categories & Items and all Inventory Transactions. It does NOT remove transactional data like sales
- Edit this file to make sure your SQL Express Server Name and Instance are correct

DELETE V3 All Transactions.bat
- This clears all transactional data leaving Products, Inventory, Recipes and Menus untouched.

[emre]

It looks great. Few hours later I'll be on vacation for two days. When I'm back I'll test it and try to find some integration path.

[JohnS]

Latest version to handle the Database changes as at 3.0.24 Beta.

Changes/Additions
- You can set which Menu products are added too
- Supports multiple Warehouses
- No need to sort by Group Code anymore
- Will automatically wrap long Menu Names for buttons - wrap length set in script

[pdsluk]

Great tool! Thanks for all the hard work!

[emre]

Today we imported ~3200 items for a small market.

Note1: Import your data on 3.24 database and after import install latest version to upgrade. Since we constantly change database structure it might not work on further releases.

Note2: If your decimal separator is comma "," excel uses ";" char as delimiter and that creates issues. Change decimal separator to dot "." in excel and export it to csv.

Note3: If product names contains (') character that creates another issue. I've replaced them with notepad++.

The result is epic win We scanned every product we could found in the office and had real fun with it. Inventory items, recipes all created fine...

[JohnS]

Today we imported ~3200 items for a small market.

Note1: Import your data on 3.24 database and after import install latest version to upgrade. Since we constantly change database structure it might not work on further releases.

I have tested with 3.0.26 and have found no side affects or issues as yet. But to be safe always start with the last version that has been confirmed fully tested.

Note2: If your decimal separator is comma "," excel uses ";" char as delimiter and that creates issues. Change decimal separator to dot "." in excel and export it to csv.

Unfortunately this will always be a case with translations. By default I use comma separated. I will test using a semicolon ; and make this the standard in future versions.

Note3: If product names contains (') character that creates another issue. I've replaced them with notepad++.

There are some characters that PHP will have issues with, and these are definitely ' and "
I will look at stripping these characters from the imported data.

The result is epic win We scanned every product we could found in the office and had real fun with it. Inventory items, recipes all created fine...

When faced with such a large volume of products to enter, this tool has been an amazing time saver. And it's not just for the initial import of products either, you can also use this to add extra products when new stock lines arrive.

This has been a great result. Thank you for the feedback.

[JohnS]

Emre,

Something that I have just noticed, and this was with 3.0.24 as well as 3.0.26.
After an import, if you select Edit Product Properties for a Menu Category, the Product Name is identical to the Menu Name, ie it shows the \r in the name.
I have checked the database and the Product Names are correct. I'm not sure what is causing this. If you remove a product from a Menu Category and add it again, it will show correctly.

[sukasem]

Emre,

Something that I have just noticed, and this was with 3.0.24 as well as 3.0.26.
After an import, if you select Edit Product Properties for a Menu Category, the Product Name is identical to the Menu Name, ie it shows the \r in the name.
I have checked the database and the Product Names are correct. I'm not sure what is causing this. If you remove a product from a Menu Category and add it again, it will show correctly.

It's not just import product. I just did key in product from scratches, yeah it's the same issue.

[crazypete]

Good day,

Did you manage to get this working as I also want to import all my products to inventory and recipe. I tried downloading the importer and was unsuccessful in downloading it. Is there maybe an easier way as I have thousands of products and should be imported as 1:1. I am running 3.033

Thanks in advance.

P