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.


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',
  `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`)
  • 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`
              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`
      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`
      '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 (
    , 2 AS sales_type_id
    , 'USD' AS curr_abrev
    , ROUND(CostPrice*DiscCode,2) AS price
FROM items_holder
    , 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`
        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