FrontAccounting Wiki - Devel - Manual Import of Inventory Items

Introduction

There is a very nicely written FA module for Importing Items. However, it requires several passes using several CSV files. Also, it is overkill for the simple items import envisaged here besides not supporting specially formatted descriptions holding non field db data.

This method is not official and all support requests may be directed to the author (apmuthu).

For this article, we assume Company # 1 as the target company to import inventory items into.

Caveat

Make sure that your db schema (Chart of Accounts) follows the sql/en_US-new.sql - many official COAs (including those for UK and Canada) are woefully out of date and upgrade scripts during FA's evolution cared less about field order and type resulting in a multitude of "working" and well used FA installs.

Process Sequence

Create the holding table
Import Inventory data into the holding table
Prepare sql statements to import data in holding table into the various FrontAccounting tables
Use Non Field DB Data to accommodate missing FA fields

Holding Tables

The schema of a typical inventory holding table used here is:

CREATE TABLE `items_holder` (
  `stock_id` varchar(20) NOT NULL COMMENT 'Part#',
  `ItemDesc` varchar(254) NOT NULL COMMENT 'Item Description',
  `ReOrder` int(11) DEFAULT NULL COMMENT 'ReOrder Level',
  `QtyStock` int(11) DEFAULT NULL COMMENT 'Qty in Stock',
  `MoQ` int(11) DEFAULT NULL COMMENT 'Min Order Qty',
  `OnRMA` int(11) NOT NULL DEFAULT '0' COMMENT 'On RMA',
  `category_id` int(11) DEFAULT NULL COMMENT 'Category ID',
  `CostPrice` float(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Unit Cost',
  `WSPrice` float(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Wholesale Price',
  `BinLoc` varchar(20) DEFAULT NULL COMMENT 'Bin Location',
  `VendorID` int(11) DEFAULT NULL COMMENT 'Vendor#',
  `Weight` float(8,2) DEFAULT NULL COMMENT 'Weight',
  `UPC` varchar(30) DEFAULT NULL COMMENT 'UPC Code',
  `WarehouseID` int(11) NOT NULL DEFAULT '1' COMMENT 'Warehouse ID',
  `Product_Code` varchar(30) DEFAULT NULL COMMENT 'Product#',
  `RetailPrice` float(12,2) DEFAULT NULL COMMENT 'Retail Price',
  `DiscCode` float(8,2) DEFAULT '1.00' COMMENT 'Disc Code',
  `IsFreighted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is Freighted',
  `IsCatalog` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Catalog',
  `SaleType` enum('Normal','AskForPrice','Discontinued') NOT NULL DEFAULT 'Normal' COMMENT 'SaleType',
  `PriceDate` date DEFAULT NULL COMMENT 'Price Date',
  `ReplStockID` varchar(20) NOT NULL COMMENT 'Replacement Part#',
  `ListPrice` float(12,2) DEFAULT NULL COMMENT 'List Price',
  PRIMARY KEY (`stock_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Items Holder';

The COMMENTs in the table above will double up for field labels in admin tools like Adminer.
The Product_Code is the Suppliers Product Code whilst the stock_id is the Customer Invoice Item Code used by FA.
The category_id is to be taken from values in the stock_category table.
For the purposes of this article we will assume that all items are marked up from their CostPrice
- by a factor of DiscCode to obtain the Wholesale Price
- by 20% more than the DiscCode factor to get the RetailPrice
The stock_id is the only field we need to manually organize as unique values with no spaces or special characters before data import as it will be the one used by FA as the primary key in the stock_master table and as foreign keys elsewhere.

Source Data

Inventory data to be imported into FA can come from a variety of sources including spreadsheets (Excel, etc), and CSV / TSV files, MS Access, xBASE tables, other databases like Oracle, MSSQL, etc. Suitably craft SQL statements to insert data into the holding table and / or import directly into it using LOAD DATA INFILE constructs of MySQL.

If spreadsheets like Excel are used as the source, then the necessary sqls can be constructed dynamically using the CONCATENATE() function to string the syntax together besides being able to generate CSV (comma separated values) / TSV (tab separated values) files for direct import.

Beware of special characters like some stray unicode or application hexadecimal values in the data apart from common delimiters like space, comma, single and double quotes, backquotes, ampersand (&), semicolons, colons, hyphen looking characters, etc. DOS style CRLF issues and line feeds in text fields too cause issues in sql execution / data import.

SQLs used for direct Import

stock_master table import

INSERT INTO 1_stock_master
SELECT stock_id
    , category_id
    , 1 AS tax_type_id
    , ItemDesc AS `description`
    , TRIM(CONCAT(
              IF(LENGTH(TRIM(BinLoc)) > 0, CONCAT('BinLoc#',TRIM(BinLoc),' '), ''),
              IF(IsFreighted,'Frt#1 ',''),
              IF(Weight,CONCAT('Wt#', Weight, ' '),'')
          )) AS long_description
    , 'each' AS units
    , 'B' AS mb_flag
    , 3010 AS sales_account
    , 3610 AS cogs_account
    , 1310 AS inventory_account
    , 3710 AS adjustment_account
    , 1330 AS assembly_account
    , 0 AS dimension_id
    , 0 AS dimension2_id
    , 0 AS actual_cost
    , 0 AS last_cost
    , 0 AS material_cost
    , 0 AS labour_cost
    , 0 AS overhead_cost
    , 0 AS inactive
    , 0 AS no_sale
    , 0 AS editable
FROM `items_holder`
ORDER BY stock_id;

The sales_account, cogs_account, inventory_account, adjustment_account and assembly_account can be taken from their default values in the sys_prefs table as well or hardcoded to suit your CoA as above.

item_codes table import

INSERT INTO `1_item_codes`
SELECT 
      NULL AS id
    , stock_id AS item_code
    , stock_id
    , ItemDesc AS `description`
    , category_id
    , 1 AS quantity
    , 0 AS is_foreign
    , 0 AS inactive
FROM items_holder
ORDER BY stock_id;

loc_stock table import

INSERT INTO `1_loc_stock`
SELECT 
      'DEF' AS loc_code
    , stock_id
    , ReOrder AS reorder_level
FROM items_holder
ORDER BY stock_id;

Hardcoded the location as DEF here - change as required to possibly depend on the WarehouseID field in the items_holder table.

prices table import (selling prices)

INSERT INTO `1_prices`
SELECT NULL AS id, stock_id, sales_type_id, curr_abrev, price FROM (
SELECT 
       stock_id
    , 2 AS sales_type_id
    , 'USD' AS curr_abrev
    , ROUND(CostPrice*DiscCode,2) AS price
FROM items_holder
UNION
SELECT 
      stock_id
    , 1 AS sales_type_id
    , 'USD' AS curr_abrev
    , ROUND(CostPrice*DiscCode*1.2,2) AS price
FROM items_holder
) a ORDER BY stock_id, sales_type_id;

sales_type_id is taken from sales_types table and here 1 is assumed to be Retail Price List and 2 is used to denote WholeSale Price List.
The curr_abrev field is hard coded here but can be taken from the sys_prefs table as well.

purch_data table import (purchase prices from vendors)

INSERT INTO `1_purch_data`
    SELECT 
        VendorID AS supplier_id
      , stock_id
      , ROUND(CostPrice,2) AS price
      , '' AS `suppliers_uom`
      , 1 AS `conversion_factor`
      , item_code AS `supplier_description`
FROM `lst_items`
-- WHERE CostPrice <> 0
;

purch_data.price field is of double type and hence the rounding off

Limitations of this manual import procedure

No Kits and Assemblies
No Foreign Items

Big View Home Login History Recent Changes
Project home page Wiki Guidelines About FrontAccounting General Features License Support Installation Installation Update config.php Working with FA Sales Purchases Items and Inventory Manufacturing Fixed Assets Dimensions Banking And General Ledger Setup The SuperAdmin Create/Update Companies, Install/Update Languages, Install/Activate Extensions, Software Upgrade Development TechInfo Resources Wishlist and future development directions for next major releases Page title Sourceforge project page CVS updates mailing lists CVS www interface Instructions on CVS usage Community Mantis bugtracking system Support Forum FA mailing list edit SideBar TriadSkin powered by PmWiki	Devel Manual Import of Inventory Items Introduction There is a very nicely written FA module for Importing Items. However, it requires several passes using several CSV files. Also, it is overkill for the simple items import envisaged here besides not supporting specially formatted descriptions holding non field db data. This method is not official and all support requests may be directed to the author (apmuthu). For this article, we assume Company # 1 as the target company to import inventory items into. Caveat Make sure that your db schema (Chart of Accounts) follows the sql/en_US-new.sql - many official COAs (including those for UK and Canada) are woefully out of date and upgrade scripts during FA's evolution cared less about field order and type resulting in a multitude of "working" and well used FA installs. Process Sequence Create the holding table Import Inventory data into the holding table Prepare sql statements to import data in holding table into the various FrontAccounting tables Use Non Field DB Data to accommodate missing FA fields Holding Tables The schema of a typical inventory holding table used here is: CREATE TABLE `items_holder` ( `stock_id` varchar(20) NOT NULL COMMENT 'Part#', `ItemDesc` varchar(254) NOT NULL COMMENT 'Item Description', `ReOrder` int(11) DEFAULT NULL COMMENT 'ReOrder Level', `QtyStock` int(11) DEFAULT NULL COMMENT 'Qty in Stock', `MoQ` int(11) DEFAULT NULL COMMENT 'Min Order Qty', `OnRMA` int(11) NOT NULL DEFAULT '0' COMMENT 'On RMA', `category_id` int(11) DEFAULT NULL COMMENT 'Category ID', `CostPrice` float(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Unit Cost', `WSPrice` float(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Wholesale Price', `BinLoc` varchar(20) DEFAULT NULL COMMENT 'Bin Location', `VendorID` int(11) DEFAULT NULL COMMENT 'Vendor#', `Weight` float(8,2) DEFAULT NULL COMMENT 'Weight', `UPC` varchar(30) DEFAULT NULL COMMENT 'UPC Code', `WarehouseID` int(11) NOT NULL DEFAULT '1' COMMENT 'Warehouse ID', `Product_Code` varchar(30) DEFAULT NULL COMMENT 'Product#', `RetailPrice` float(12,2) DEFAULT NULL COMMENT 'Retail Price', `DiscCode` float(8,2) DEFAULT '1.00' COMMENT 'Disc Code', `IsFreighted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is Freighted', `IsCatalog` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Catalog', `SaleType` enum('Normal','AskForPrice','Discontinued') NOT NULL DEFAULT 'Normal' COMMENT 'SaleType', `PriceDate` date DEFAULT NULL COMMENT 'Price Date', `ReplStockID` varchar(20) NOT NULL COMMENT 'Replacement Part#', `ListPrice` float(12,2) DEFAULT NULL COMMENT 'List Price', PRIMARY KEY (`stock_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Items Holder'; The COMMENTs in the table above will double up for field labels in admin tools like Adminer. The Product_Code is the Suppliers Product Code whilst the stock_id is the Customer Invoice Item Code used by FA. The category_id is to be taken from values in the stock_category table. For the purposes of this article we will assume that all items are marked up from their CostPrice by a factor of DiscCode to obtain the Wholesale Price by 20% more than the DiscCode factor to get the RetailPrice The stock_id is the only field we need to manually organize as unique values with no spaces or special characters before data import as it will be the one used by FA as the primary key in the stock_master table and as foreign keys elsewhere. Source Data Inventory data to be imported into FA can come from a variety of sources including spreadsheets (Excel, etc), and CSV / TSV files, MS Access, xBASE tables, other databases like Oracle, MSSQL, etc. Suitably craft SQL statements to insert data into the holding table and / or import directly into it using LOAD DATA INFILE constructs of MySQL. If spreadsheets like Excel are used as the source, then the necessary sqls can be constructed dynamically using the CONCATENATE() function to string the syntax together besides being able to generate CSV (comma separated values) / TSV (tab separated values) files for direct import. Beware of special characters like some stray unicode or application hexadecimal values in the data apart from common delimiters like space, comma, single and double quotes, backquotes, ampersand (&), semicolons, colons, hyphen looking characters, etc. DOS style CRLF issues and line feeds in text fields too cause issues in sql execution / data import. SQLs used for direct Import stock_master table import INSERT INTO 1_stock_master SELECT stock_id , category_id , 1 AS tax_type_id , ItemDesc AS `description` , TRIM(CONCAT( IF(LENGTH(TRIM(BinLoc)) > 0, CONCAT('BinLoc#',TRIM(BinLoc),' '), ''), IF(IsFreighted,'Frt#1 ',''), IF(Weight,CONCAT('Wt#', Weight, ' '),'') )) AS long_description , 'each' AS units , 'B' AS mb_flag , 3010 AS sales_account , 3610 AS cogs_account , 1310 AS inventory_account , 3710 AS adjustment_account , 1330 AS assembly_account , 0 AS dimension_id , 0 AS dimension2_id , 0 AS actual_cost , 0 AS last_cost , 0 AS material_cost , 0 AS labour_cost , 0 AS overhead_cost , 0 AS inactive , 0 AS no_sale , 0 AS editable FROM `items_holder` ORDER BY stock_id; The sales_account, cogs_account, inventory_account, adjustment_account and assembly_account can be taken from their default values in the sys_prefs table as well or hardcoded to suit your CoA as above. item_codes table import INSERT INTO `1_item_codes` SELECT NULL AS id , stock_id AS item_code , stock_id , ItemDesc AS `description` , category_id , 1 AS quantity , 0 AS is_foreign , 0 AS inactive FROM items_holder ORDER BY stock_id; loc_stock table import INSERT INTO `1_loc_stock` SELECT 'DEF' AS loc_code , stock_id , ReOrder AS reorder_level FROM items_holder ORDER BY stock_id; Hardcoded the location as DEF here - change as required to possibly depend on the WarehouseID field in the items_holder table. prices table import (selling prices) INSERT INTO `1_prices` SELECT NULL AS id, stock_id, sales_type_id, curr_abrev, price FROM ( SELECT stock_id , 2 AS sales_type_id , 'USD' AS curr_abrev , ROUND(CostPriceDiscCode,2) AS price FROM items_holder UNION SELECT stock_id , 1 AS sales_type_id , 'USD' AS curr_abrev , ROUND(CostPriceDiscCode1.2,2) AS price FROM items_holder ) a ORDER BY stock_id, sales_type_id; sales_type_id* is taken from sales_types table and here 1 is assumed to be Retail Price List and 2 is used to denote WholeSale Price List. The curr_abrev field is hard coded here but can be taken from the sys_prefs table as well. purch_data table import (purchase prices from vendors) INSERT INTO `1_purch_data` SELECT VendorID AS supplier_id , stock_id , ROUND(CostPrice,2) AS price , '' AS `suppliers_uom` , 1 AS `conversion_factor` , item_code AS `supplier_description` FROM `lst_items` -- WHERE CostPrice <> 0 ; purch_data.price field is of double type and hence the rounding off Limitations of this manual import procedure No Kits and Assemblies No Foreign Items Edit Page History Source Attach File Backlinks List Group Page last modified on March 25, 2015, at 07:00 PM
skin config pmwiki-2.2.107