English Boards > V3 Development

Bulk Import Of Products

(1/2) > >>

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!

Navigation

[0] Message Index

[#] Next page