Import Inventory

Hi old_guy,

  In order to insert products into FA. You may refer to the table stock_master. For stock category, you may refer to stock_category.

Below are my sample code that I created to synchronize the item in FA. If you need to use it, you need to change the parameter values in the array Object. I have hardcoded for my testing.

-------------------------------------------------------------------------------------------------------------------------------------
$objInfo = array();       

//Array Information           
$objInfo['units'] = 'Each';
$objInfo['units_of_measure'] = '0';
$objInfo['tax_type'] = '0';
$objInfo['qty'] = '1';    
$objInfo['mb_flag'] = 'B';                    

$objInfo['sales_act'] = '';
$objInfo['cogs_act'] = '';
$objInfo['inventory_account'] = '';
$objInfo['adjustment_account'] = '';
$objInfo['assembly_account'] = '';
$objInfo['dim1'] = '';
$objInfo['dim2'] = '';
$objInfo['actual_cost'] = '0';
$objInfo['last_cost'] = '0';            
$objInfo['labour_cost'] = '0';
$objInfo['overhead_cost'] = '0';
$objInfo['no_sale'] = '0';
$objInfo['editable'] = '0';            
       
//Set information
$objInfo['stock_id'] = '123';
$objInfo['description'] = 'abc';           
$objInfo['long_description'] = 'this is a description of the item';
$objInfo['category'] = 'productcategory';            
$objInfo['material_cost'] = '100.00';
$objInfo['price'] = '150.00'
$objInfo['reorder_level'] = '10';
//FA Active Record = 0 and InActive = 1
$objInfo['inactive'] = '0'

Method name: syncItem
-------------------------------------------------------------------------------------------------------------------------------------

/*
* To Sync Item
* $arrObj            -    Item Array Object
* $return            -    Item ID
*/
function syncItem($arrObj) {
    global $log;
   
    $selected_id = '';
    $category_id = '';
    $item_unit_id = '';

    //Retrieve Item Units   
    $sql = "SELECT abbr FROM ".TB_PREF."item_units WHERE UPPER(name) = UPPER(" . db_escape($arrObj['units']) .")";
    $result = db_query($sql, "Unable to find units");
    $row = db_fetch_row($result);
   
    if (!$row) {           
        write_item_unit('', strtolower($arrObj['units']), $arrObj['units'], $arrObj['units_of_measure']);
        $item_unit_id = strtolower($arrObj['units']);
    } else {
        $item_unit_id = $row[0];
    }
   
    //Retrieve Category Id           
    $sqlCategory = "SELECT category_id FROM ".TB_PREF."stock_category WHERE description LIKE " . db_escape(stripslashes(trim($arrObj['category'])));
    $resultCategory = db_query($sqlCategory, "could not get stock category");

    $rowCategory = db_fetch_row($resultCategory);
   
    if (!$rowCategory) { //If category not exist, add new category
        add_item_category($arrObj['category'],$arrObj['tax_type'],    $arrObj['sales_act'],
                        $arrObj['cogs_act'], $arrObj['inventory_account'], $arrObj['adjustment_account'], $arrObj['assembly_account'],
                        $item_unit_id, $arrObj['mb_flag'],    $arrObj['dim1'], $arrObj['dim2'],    $arrObj['no_sale']);
   
        $category_id = db_insert_id();
    } else {
        $category_id = $rowCategory[0];
    }

    //Query Stock ID
    $sql = "SELECT a.stock_id FROM ".TB_PREF."stock_master a WHERE (a.stock_id = " . db_escape($arrObj['stock_id']) . " || a.description = " . db_escape($arrObj['description']) . ")";
   
    $result = db_query($sql, "stock item could not be retreived");
   
    $row = db_fetch_row($result);

    //Default to returning ID
    $selected_id = $arrObj['stock_id'];
   
    //Retrieve the default currency
    $currency_code = get_company_pref('curr_default');

    //Insert or update Item
    if (!$row) {
           
        add_item($arrObj['stock_id'], $arrObj['description'],
            $arrObj['long_description'], $category_id, $arrObj['tax_type'],
            $item_unit_id, $arrObj['mb_flag'], $arrObj['sales_act'],
            $arrObj['inventory_account'], $arrObj['cogs_act'],
            $arrObj['adjustment_account'], $arrObj['assembly_account'],
            $arrObj['dim1'], $arrObj['dim2'],$arrObj['no_sale'],$arrObj['editable']);
    } else {
            $sql = "UPDATE ".TB_PREF."stock_master SET long_description=".db_escape($arrObj['long_description']).",
                    description=".db_escape($arrObj['description']).",
                    category_id=".db_escape($category_id).",
                    tax_type_id=".db_escape($arrObj['tax_type']).",
                    units=".db_escape($item_unit_id).",
                    inactive=".db_escape($arrObj['inactive']).",
                    mb_flag=".db_escape($arrObj['mb_flag']) . " WHERE stock_id=".db_escape($arrObj['stock_id']);
           
            db_query($sql, "The item could not be updated");
       
            update_item_code(-1, $arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);
           
           
            if (isset($arrObj['currency_code'])) {
                $currency_code = $arrObj['currency_code'];
            }
           
            $sql = "UPDATE ".TB_PREF."prices SET curr_abrev=".db_escape($currency_code).",
                price=".db_escape($arrObj['price'])." WHERE stock_id=".db_escape($arrObj['stock_id']);
           
            db_query($sql,"an item price could not be updated");
        }
       
        //Update Item Codes
         $sql = "SELECT id from ".TB_PREF."item_codes WHERE item_code=" . db_escape($arrObj['stock_id']) . " || stock_id = " . db_escape($arrObj['stock_id']);
        $result = db_query($sql, "item code could not be retreived");
        $row = db_fetch_row($result);
        if (!$row) {
            add_item_code($arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);           
        } else {
            update_item_code($row[0], $arrObj['stock_id'], $arrObj['stock_id'], $arrObj['description'], $category_id, $arrObj['qty']);
        }
        
    return $selected_id;
}

-------------------------------------------------------------------------------------------------------------------------------------

If you are running 2.3.5 you should be able to go to Setup->Install/Activate Extensions and see "Inventory Items CSV Import"

Once you install it, change the pull-down from "Available" to your site and make sure it is enabled.

Then go to Setup->Access Setup, Select your role (System Administrator) and click the check boxes for "Import CSV Items"
at the end of the list.

That will give you an Import option under the "Items and Inventory" tab, Maintenance sub tab on the right.

See the README.TXT in the modules/import_items/ directory

tom

can you show us the first few lines of the csv file?

tom

humm... that does not help.

Which lines appear wrong?

Well, seems you have unescaped comma in description field, so the 'description' is imported into columns C&D, and all the following columns are shifted to the right.
Janusz

No, it means you have to escape commas in the description field.  that means you add "\" before each comma that's part of the "description" text so that the import module doesn't confuse those commas with field separators.  You can do this in any spreadsheet program and then save the file to cvs format for import into FA.  It's a bit of work, but using find and replace (find "," and replace with "\,") should help.

Hope that's helpful...

Regards,

Chris