SambaPOS4 Bulk Product Importer

*** Setup And Usage Instructions ***

Requirements

  • SambaPOS V4.1.37
  • SQL Express Server 2008+
  • WampServer 2.5 (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.5 using default settings
  • Unzip ‘SambaPOS4 Product Import V2.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 Menu List you specify under CreateMenu in the import.csv file.
  • Edit load_products.php file in c:\wamp\www and set your specific details.

// SQL Express Server Details - Set to your details from your connection string
// example: data source = localhost\SAMBAPOS; user id=sa; password=sambapos; database=import
$myServer = “localhost\SAMBAPOS”;
$myUser = “sa”;
$myPass = “sambapos”;
$myDB = “import”;

// 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.phpThis 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 SambaPOS4.
  • Enjoy

Additional Tools
In ‘SambaPOS4 Product Import V2.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.

SambaPOS4 Product Import V2.zip (5.5 KB)


New version supporting SambaPOS 4.1.82

EDIT (2015-04-15 : QMcKay):

5 Likes

Wonderful tool…

Cheers

Thanks for your great effort in creating this script is wonderful. Thank you because is really hard to config the inventory in sambapos.

I went fool because I didn’t work.
Problem was $myDB user setting must be

$myDB = “SambaPOS4”;

but you suggest

database=import

But now, It works fine.
Thank You

Beautiful and great idea!

BUT! There is a BUT!

I have this error:

Warning: odbc_connect(): SQL error: [Microsoft][Driver Manager ODBC] Nome origine dati non trovato e driver predefinito non specificato., SQL state IM002 in SQLConnect in C:\wamp\www\load_products.php on line 62

What is?

I’m on Win 8.1, SQL Express 2012, WampServer 2.5 64bit. What is? Samba is working with the same config.

UPDATE:
I fixed it with “Driver={SQL Server Native Client 11.0}”.

It would be great if there was also the possibility to query the file “V3_del_Prod_Inv_Recipe_MenuItems” with another php file.

Is it possible?

Dear,

I try to use bulk product importer. I have change SQL server native to 11.0 and then I run again. But in the browser said :

Start : 21:11:10
Connected successfully - MSSQL

Start A WorkPeriod First.

I have tried to Start Work period but when I run it said same again.

How to solve that problem?
TIA

Hi, but for multiple portions there are news? Is it possible to add multiple portions for each products?

Hello there,

I am using windows 8.1 with sambaPOS4…an i couldnt find the location of the SQL server.Thanks In advance.

$myServer = “localhost\SAMBAPOS”; ?

Thanks.

I encounter this Issue,
Sambapos Version: 4.1.82
SQL Express 2012
Native Client 11.0

Start : 13:40:16
Connected successfully - MSSQL

( ! ) Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column ‘SortAlphabetically’, table ‘Sambapos4.dbo.ScreenMenuCategories’; column does not allow nulls. INSERT fails., SQL state 23000 in SQLExecDirect in C:\wamp\www\load_products.php on line 179
Call Stack

Time Memory Function Location

1 0.0010 296520 {main}( ) …\load_products.php:0
2 0.3870 318560 odbc_exec ( ) …\load_products.php:179
Menu Category - HELMETS
RJAYS APEX HELMET S BLKBLU (RJH45BUBK3)

( ! ) Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint “FK_dbo.ScreenMenuItems_dbo.ScreenMenuCategories_ScreenMenuCategoryId”. The conflict occurred in database “Sambapos4”, table “dbo.ScreenMenuCategories”, column ‘Id’., SQL state 23000 in SQLExecDirect in C:\wamp\www\load_products.php on line 209
Call Stack

Time Memory Function Location

1 0.0010 296520 {main}( ) …\load_products.php:0
2 0.3970 319088 odbc_exec ( ) …\load_products.php:209

( ! ) Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column ‘DefaultBaseUnitCost’, table ‘Sambapos4.dbo.InventoryItems’; column does not allow nulls. INSERT fails., SQL state 23000 in SQLExecDirect in C:\wamp\www\load_products.php on line 244
Call Stack

Time Memory Function Location

1 0.0010 296520 {main}( ) …\load_products.php:0
2 0.4170 319656 odbc_exec ( ) …\load_products.php:244

( ! ) Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint “FK_dbo.RecipeItems_dbo.InventoryItems_InventoryItem_Id”. The conflict occurred in database “Sambapos4”, table “dbo.InventoryItems”, column ‘Id’., SQL state 23000 in SQLExecDirect in C:\wamp\www\load_products.php on line 266
Call Stack

Time Memory Function Location

1 0.0010 296520 {main}( ) …\load_products.php:0
2 0.4330 319856 odbc_exec ( ) …\load_products.php:266

( ! ) Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ‘Price’., SQL state S0022 in SQLExecDirect in C:\wamp\www\load_products.php on line 292
Call Stack

Time Memory Function Location

1 0.0010 296520 {main}( ) …\load_products.php:0
2 0.4760 320456 odbc_exec ( ) …\load_products.php:292

Finish : 13:40:17
Execution Time : 00:01

Anyone have the same issue?

That was made a while ago. I would need to look at its SQL to be sure but I think a few structure changes were made since then.

That script needs to be updated for latest version but unfortunately @JohnS is away for a while. If it is a new install you can try importing products to 4.1.37 version and install 4.1.82 on it to migrate database to latest version.

OH! Thanks for reply, Hope it won’t take so long…

That script may never get updated. Try what @emre suggested:

@JohnS has not been active since Jun 24 2014

Noted, I am trying on it, currently looking for the older version download path, thanks!

Its the same link for 4.1.82 just scroll down to that version.

erm, how could i downgrade my database or create a new one with sql express server 2012?

I just ran the script against v4.1.82 using SQLExpress 2014 and experienced no such issues when using the sample CSV file.

The script could be made more robust for providing more default values to certain fields though… I will take a look at it.

Give this a try - I modified the PHP code quite a bit:

(attachment removed - see post later in thread for newer version)

Post any error messages that you encounter.

This is the expected output with the sample CSV file:

Start : 08:01:40
Connected successfully - MSSQL

Reading file: import.csv

Read: M1 - LABOUR ::: Brayden - Labour
Read: M2 - LABOUR ::: Bruce - Labour
Read: WORKSHOP CONSUMABLES ::: WORKSHOP CONSUMABLES
Read: $25 GIFT CERTIFICATE ::: $25 GIFT CERTIFICATE
Read: $50 GIFT CERTIFICATE ::: $50 GIFT CERTIFICATE
Read: $75 GIFT CERTIFICATE ::: $75 GIFT CERTIFICATE
Read: $100 GIFT CERTIFICATE ::: $100 GIFT CERTIFICATE
Read: ALL BALLS STEERING STEM BEARING KIT-30-140-21 (22-1013) ::: ALL BALLS STEERING STEM BEARING\rKIT-30-140-21 (22-1013)
Read: ALL BALLS BEARING KIT (22-1009) ::: ALL BALLS BEARING KIT (22-1009)
Read: RJAYS APEX HELMET S BLKBLU (RJH45BUBK3) ::: RJAYS APEX HELMET S BLKBLU\r(RJH45BUBK3)

Done reading file… Rows:10

Inserting items to DB…

Menu Category - WORKSHOP
M1 - LABOUR
M2 - LABOUR
WORKSHOP CONSUMABLES

Menu Category - GIFT CERTIFICATES
$25 GIFT CERTIFICATE
$50 GIFT CERTIFICATE
$75 GIFT CERTIFICATE
$100 GIFT CERTIFICATE

Menu Category - BEARINGS
ALL BALLS STEERING STEM BEARING KIT-30-140-21 (22-1013)
ALL BALLS BEARING KIT (22-1009)

Menu Category - HELMETS
RJAYS APEX HELMET S BLKBLU (RJH45BUBK3)

Finish : 08:01:40
Execution Time : 00:00

2 Likes

everything is just fine only this error…
seems something wrong with the warehouse

( ! ) Notice: Array to string conversion in C:\wamp\www\load_products.php on line 527
Call Stack

Time Memory Function Location

1 0.0020 342016 {main}( ) …\load_products.php:0
Exo Reptile Calcium 90G