Guide: Setup uniCenta with MySQL database and import Categories, Taxes and Products using CSV files

Help
Andrew
2012-11-25
2016-04-02
  • Andrew

    Andrew - 2012-11-25

    Guide: Setup uniCenta with MySQL database and import Categories, Taxes and Products using CSV files

    Lot of this is personal preference, but hope it will be useful to some users.


    UniCenta, MySQL Server and Database Setup:
    (1). Install Java, Visual C++ 2010 and .NET Framework 4 Client

    Java (JRE): http://www.oracle.com/technetwork/java/javase/downloads/index.html
    Visual C++: http://www.microsoft.com/en-au/download/details.aspx?id=5555
    .NET Framework: http://www.microsoft.com/en-us/download/details.aspx?id=24872

    (2). Install MySQL Community Server to "C:\POS\MySQL\"

    http://www.mysql.com/downloads/mysql/
    Use Custom and only change Location
    Launch the MySQL Instance Configuration Wizard
    Standard Configuration
    Install As Windows Service "MySQL" and Launch
    * Modify Security Settings: Enter own password for root and Enable root access from remote machines

    (3). Install MySQL Workbench to "C:\POS\MySQL_Workbench\"

    http://www.mysql.com/downloads/workbench/
    * Complete install

    (4). MySQL Workbench starts:

    • Double-click "Local instance MySQL" and enter password
    • Right-click existing database and "Drop Schema..."
    • Create yourdatabase by selecting the "Create a new schema..." (3rd icon along), enter your database name and apply

    (5). Install uniCenta to "C:\POS\uniCenta_oPOS\"

    (6). Extract "mysql-connector-java-5.1.22-bin.jar" into "C:\POS\uniCenta_oPOS\lib\"

    http://www.mysql.com/downloads/connector/j/

    (7). Go to "C:\POS\uniCenta_oPOS\" and run "configure.bat";

    Driver library: C:\POS\uniCenta_oPOS\mysql-connector-java-5.1.22-bin.jar
    Driver class: com.mysql.jdbc.Driver
    URL: jdbc:mysql://localhost:3306/yourdatabase
    User: root
    Password: yourpass
    + Modify other settings to your requirements, Save, close and start uniCenta.
    Select ‘Yes’ to creating (filling) the new database

    *Note: If error with JAVA (Windows cannot find ‘javaw’: Edit the system Environment Variables -> Add to the Path variable;
    64bit Windows:

    ;C:\Program Files (x86)\Java\jre7\bin

    32bit Windows:

    ;C:\Program Files\Java\jre7\bin

    (Optional):
    Install iReport (iReport version should match "jasperreports-#.#.#.jav" in "uniCenta_oPOS\lib\") to "C:\POS\iReport\"
    Install Programmer’s Notepad


    CSV IMPORT EXAMPLES


    Importing CSV files:
    What is a CSV file?
    Read http://en.wikipedia.org/wiki/Comma-separated_values

    In what order would I need to import? If you want to alter taxes, categories and products via csv;

    1. Tax Categories
    2. Taxes
    3. Categories
    4. Products
      (*) Note: that if you only want to import products, you can just check current tax and category ID's and use those.
    • The CSV may NOT contain a top line header
    • For large (1000+ lines/rows) import: In MySQL Workbench disable the 1000 row limit; On the menu bar visit Edit -> Preferences. Jump to the SQL Queries tab. In the Query Results section at the bottom untick the Limit Rows option

    • These examples use GST (10% Australian GST rate) and two categories with two levels of sub categories each. They are just examples and you will want to customise all to your own requirements.

    (1). Tax Categories (taxcategories) Import:
    1A. Prepare your CSV (notepad, excel or other):

    GST,GST

    1B. Import:

    load data local infile 'C:/temp/taxcategories.csv' into table taxcategories fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME)

    -> Query -> Execute current statement

    (2). Taxes (taxes) Import:
    2A. Prepare your CSV (notepad, excel or other):

    GST,GST,GST,NULL,NULL,0.1,,NULL

    2B. Import:

    load data local infile 'C:/temp/taxes.csv' into table taxes fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME,CATEGORY,CUSTCATEGORY,PARENTID,RATE,RATECASCADE,RATEORDER)

    -> Query -> Execute current statement

    (3). Categories (categories) Import:
    3A. Prepare your CSV (notepad, excel or other):

    Category001,Category001,NULL,NULL
    Sub001-001,Sub001-001,Category001,NULL
    SubSub001-001-001,SubSub001-001-001,Sub001-001,NULL
    SubSub002-001-001,SubSub002-001-001,Sub001-001,NULL
    Sub002-001,Sub002-001,Category001,NULL
    SubSub001-002-001,SubSub001-002-001,Sub002-001,NULL
    SubSub002-002-001,SubSub002-002-001,Sub002-001,NULL
    Category002,Category002,NULL,NULL
    Sub001-002,Sub001-002,Category002,NULL
    SubSub001-001-002,SubSub001-001-002,Sub001-002,NULL
    SubSub002-001-002,SubSub002-001-002,Sub001-002,NULL
    Sub002-002,Sub002-002,Category002,NULL
    SubSub001-002-002,SubSub001-002-002,Sub002-002,NULL
    SubSub002-002-002,SubSub002-002-002,Sub002-002,NULL

    3B. Import:

    load data local infile 'C:/temp/categories.csv' into table categories fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,NAME,PARENTID,IMAGE)

    -> Query -> Execute current statement

    (4). Products (products) Import:
    * This is just an example. Barcode-000001 is not an EAN13 barcode and reports wouldn't generate a barcode. 123456789012 is a valid option for EAN13 barcode.
    4A. Prepare your CSV (notepad, excel or other):

    ID-000001,Reference-000001,Barcode-000001,EAN13,Name-000001,1.15,2.35,Category001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000001
    ID-000002,Reference-000002,Barcode-000002,EAN13,Name-000002,2.15,4.35,Sub001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000002
    ID-000003,Reference-000003,Barcode-000003,EAN13,Name-000003,3.15,6.35,SubSub001-001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000003
    ID-000004,Reference-000004,Barcode-000004,EAN13,Name-000004,4.15,8.35,SubSub002-001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000004
    ID-000005,Reference-000005,Barcode-000005,EAN13,Name-000005,5.15,10.35,Sub002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000005
    ID-000006,Reference-000006,Barcode-000006,EAN13,Name-000006,6.15,12.35,SubSub001-002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000006
    ID-000007,Reference-000007,Barcode-000007,EAN13,Name-000007,7.15,14.35,SubSub002-002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000007
    ID-000008,Reference-000008,Barcode-000008,EAN13,Name-000008,8.15,16.35,Category002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000008
    ID-000009,Reference-000009,Barcode-000009,EAN13,Name-000009,9.15,18.35,Sub001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000009
    ID-000010,Reference-000010,Barcode-000010,EAN13,Name-000010,10.15,20.35,SubSub001-001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000010
    ID-000011,Reference-000011,Barcode-000011,EAN13,Name-000011,11.15,22.35,SubSub002-001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000011
    ID-000012,Reference-000012,Barcode-000012,EAN13,Name-000012,12.15,24.35,Sub002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000012
    ID-000013,Reference-000013,Barcode-000013,EAN13,Name-000013,13.15,26.35,SubSub001-002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000013
    ID-000014,Reference-000014,Barcode-000014,EAN13,Name-000014,14.15,28.35,SubSub002-002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000014

    -OR- for those that use Variable Price Products by john l (https://sourceforge.net/p/unicentaopos/discussion/1126901/thread/539551a5/)

    ID-000001,Reference-000001,Barcode-000001,EAN13,Name-000001,1.15,2.35,Category001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000001,0
    ID-000002,Reference-000002,Barcode-000002,EAN13,Name-000002,2.15,4.35,Sub001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000002,0
    ID-000003,Reference-000003,Barcode-000003,EAN13,Name-000003,3.15,6.35,SubSub001-001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000003,0
    ID-000004,Reference-000004,Barcode-000004,EAN13,Name-000004,4.15,8.35,SubSub002-001-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000004,0
    ID-000005,Reference-000005,Barcode-000005,EAN13,Name-000005,5.15,10.35,Sub002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000005,0
    ID-000006,Reference-000006,Barcode-000006,EAN13,Name-000006,6.15,12.35,SubSub001-002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000006,0
    ID-000007,Reference-000007,Barcode-000007,EAN13,Name-000007,7.15,14.35,SubSub002-002-001,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000007,0
    ID-000008,Reference-000008,Barcode-000008,EAN13,Name-000008,8.15,16.35,Category002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000008,0
    ID-000009,Reference-000009,Barcode-000009,EAN13,Name-000009,9.15,18.35,Sub001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000009,0
    ID-000010,Reference-000010,Barcode-000010,EAN13,Name-000010,10.15,20.35,SubSub001-001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000010,0
    ID-000011,Reference-000011,Barcode-000011,EAN13,Name-000011,11.15,22.35,SubSub002-001-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000011,0
    ID-000012,Reference-000012,Barcode-000012,EAN13,Name-000012,12.15,24.35,Sub002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000012,0
    ID-000013,Reference-000013,Barcode-000013,EAN13,Name-000013,13.15,26.35,SubSub001-002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000013,0
    ID-000014,Reference-000014,Barcode-000014,EAN13,Name-000014,14.15,28.35,SubSub002-002-002,GST,NULL,NULL,NULL,NULL,,,,,,,Properties,<html>ButtonText-000014,0

    4B. Import:

    load data local infile 'C:/temp/products.csv' into table products fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,REFERENCE,CODE,CODETYPE,NAME,PRICEBUY,PRICESELL,CATEGORY,TAXCAT,ATTRIBUTESET_ID,STOCKCOST,STOCKVOLUME,IMAGE,ISCOM,ISSCALE,ISKITCHEN,PRINTKB,SENDSTATUS,ISSERVICE,ATTRIBUTES,DISPLAY)

    -OR- for Variable Price Product tables...

    load data local infile 'C:/temp/products.csv' into table products fields terminated by ',' enclosed by '"' lines terminated by '\n' (ID,REFERENCE,CODE,CODETYPE,NAME,PRICEBUY,PRICESELL,CATEGORY,TAXCAT,ATTRIBUTESET_ID,STOCKCOST,STOCKVOLUME,IMAGE,ISCOM,ISSCALE,ISKITCHEN,PRINTKB,SENDSTATUS,ISSERVICE,ATTRIBUTES,DISPLAY,isvprice)

    -> Query -> Execute current statement

    5-12-12 (updated for v3.02)
    19-01-13 (added variable price product details)

     
    Last edit: Andrew 2013-01-19
    • PasswordSafeGuy

      PasswordSafeGuy - 2016-04-02

      Hi Andrew

      I haven't given up on Derby by any means!

      But I would like to see what MySQL is all about.

      This is whole SQL thing is like sorcery!
      I am by no means a computer novice.
      Been using the machines for more than 25 years now.
      But getting SQL set up and talking with uniCenta is like witchcraft.

      And I am not expert in witchcraft!
      Somehow I managed to get it working in uniCenta.
      Don't ask me how, or what I did.

      Now let's see what the big deal is with MySQL...

       
      Last edit: PasswordSafeGuy 2016-04-02
  • uniCenta

    uniCenta - 2012-11-26

    Andrew

    Thanks for doing that. Very useful for lots of people.

    Regards

    Jack

     
  • Andrew

    Andrew - 2013-02-23

    I've been looking at improving my CSV import capability. Following john l's 'CSV Import' work at http://sourceforge.net/p/unicentaopos/discussion/1126901/thread/e3b8777f/.
    Found <B>HeidiSQL</B> MySQL client to be helpful in importing products.

    Update here method to import only columns you wish into product table:

    CSV template.csv (you can leave the top line header when using the following MySQL script):

    ID, REFERENCE, CODE, NAME, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, DISPLAY
    ID-000001,Reference-000001,Barcode-000001,Name-000001,1.15,2.35,000,000,ButtonText-000001
    ID-000002,Reference-000002,Barcode-000002,Name-000002,2.15,4.35,000,000,ButtonText-000002
    

    mySQL script, don't forget to change CSV name, path and DatabaseName:

    LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\CSV template.csv' REPLACE INTO TABLE `DatabaseName`.`products` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `REFERENCE`, `CODE`, `NAME`, `PRICEBUY`, `PRICESELL`, `CATEGORY`, `TAXCAT`, `DISPLAY`);
    

    Obviously we can recycle and use other columns such as NAME for ID and DISPLAY. Can be achieved easily using Excel copy and paste cells.

    <B>Furthermore:</B>
    <B>A. Tax Categories</B> (Australian GST 10% example)

    ID, NAME
    GST,GST
    

    -

    LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\taxcat template.csv' REPLACE INTO TABLE `DatabaseName`.`taxcategories` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`);
    

    <B>B. Taxes</B>

    ID, NAME, CATEGORY, RATE
    GST,GST,GST,0.1
    

    -

    LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\taxes template.csv' REPLACE INTO TABLE `DatabaseName`.`taxes` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`, `CATEGORY`, `RATE`);
    

    <B>C. Categories</B>

    ID, NAME, PARENTID
    Category001,Category001,NULL
    Sub001-001,Sub001-001,Category001
    Sub002-001,Sub002-001,Category001
    Category002,Category002,NULL
    Sub001-002,Sub001-002,Category002
    Sub002-002,Sub002-002,Category002
    

    -

    LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\cat template.csv' REPLACE INTO TABLE `DatabaseName`.`categories` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `NAME`, `PARENTID`);
    

    <B>D. Products</B>

    ID, REFERENCE, CODE, NAME, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, DISPLAY
    ID-000001,Reference-000001,Barcode-000001,Name-000001,1.15,2.35,Sub001-001,GST,ButtonText-000001
    ID-000002,Reference-000002,Barcode-000002,Name-000002,2.15,4.35,Sub001-002,GST,ButtonText-000002
    

    -

    LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\temp\\CSV template.csv' REPLACE INTO TABLE `DatabaseName`.`products` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`ID`, `REFERENCE`, `CODE`, `NAME`, `PRICEBUY`, `PRICESELL`, `CATEGORY`, `TAXCAT`, `DISPLAY`);
    
     
    Last edit: Andrew 2013-02-23

Log in to post a comment.