But went I see inventory evaluation report all my items are in zero. PLEASE tell me what chart I miss so that my inventory quantity is updated.

I appreciated your help and knowledge on using FA.

Thanks,
Jose.

Thanks for response.

I appreciate your promptness, way of interacting and great programming skills ! Hats off Sir! It is very nice to interact with you !

With regards,

That is because that example is before the line that says:

VERSION 2 by Tom Moulton 5/2009

Look BELOW this line to see the updated format

What you need is in the last 10-15 lines of the file

tom

I may be wrong somewhere but import format mentioned below does not seem to work

id; description; category; units; mb_flag; currency; price;
21; Item21; MyCat; each; B; ; 300;
22; Item22; MyCat; each; B; ; 200;
23; Item23; MyCat; each; B; ; 300;
24; Item24; MyCat; each; B; ; 200;
25; Item25; MyCat; each; B; ; 300;
26; Item26; MyCat; each; B; ; 200;

Please advise.

Thanks

See the note:
***** Big changes see VERSION 2 below *******

Now go down to here:
VERSION 2 by Tom Moulton 5/2009
------------------------------------------------------------------------

/****************************************************************************
Author: Joe Hunt
Name: Import of CSV formatted items
Free software under GNU GPL
*****************************************************************************/

***** Big changes see VERSION 2 below *******

Recommended settings during install:

Menu Tab: Items and Inventory

Name: Import of CSV formatted items

Folder: import_items   (should follow unix folder convention)

Browse for the file: import_items.php on you local harddisk.

Press the Install button.

--- Before you use the Import Items module ---

Do a Company Backup from the Setup tab, Backup and Restore.

The comma separated file should be of the following type:
1. line should contain the following description:

id; description; category; units; mb_flag; currency; price;

In this case the separator is ';', id is the Stock ID, description is the Stock description,
category is a category you want to put your Stock. If this category doesn't exist it will
be created during import. units is the unit for the Stock.

The mb_flag is M for Manufacturered, B for Purchased or S for Service (no inventory)

currency is the currency for the price.
Leave empty for company currency.
price is the stock price. If you don't want the price to be entered, leave it empty.

Example of a CSV import file:

id; description; category; units; mb_flag; currency; price;
21; Item21; MyCat; each; B; ; 300;
22; Item22; MyCat; each; B; ; 200;
23; Item23; MyCat; each; B; ; 300;
24; Item24; MyCat; each; B; ; 200;
25; Item25; MyCat; each; B; ; 300;
26; Item26; MyCat; each; B; ; 200;

When the Import file is ready (you might prepare it in a spreadsheet if it is a huge one),
you are ready to go into the Import Module via Items and Inventory, right section.
First you see the default accounts for the various operations for the stocks. Leave them
as is, if you are satisfied with them.
The field separator is set to ';'. If you have used another separator, select it here.
Be aware, that if you have chosen ',' as a separator, you should enclose the fields with
double quotes ("Item21") because it might be common to put a comma inside a description.
Next you should select a Location to put the stocks into (the 'B' ones), Item Tax Type and
Sales Type. Last you browse to the CSV file and put it here.

If the items exist, they will be updated with the information here.

Now you are ready to press the Import CSV file button (Did you take a backup??)

Have fun!!

PS. The language inside the Import CSV file does NOT follow the traditional GETTEXT translations.
If you want to translate to another language, please rename the texts inside the file
import_items.php to your own language.
If you make improvements to this module, please share it with us!!.
We will then incorporate it into the module.

Contributor: Tom Moulton

Upgrade code to v2.1 database, perform same actions done during upgrade to create item_codes table

Also here is osCommerce SQL to dump items.

Remember to add trailing ";", also extra spaces on non-quoted strings may be a problem.

SELECT p.products_model, pd.products_name, cd.categories_name, 'each', 'B', 'USD', p.products_price FROM
products p left join products_description pd on p.products_id = pd.products_id and pd.language_id = 1
left join products_to_categories pc on p.products_id = pc.products_id
left join categories_description cd on pc.categories_id = cd.categories_id and cd.language_id = 1
where p.products_status = 1

The mb_flag is M for Manufacturered, B for Purchased or S for Service

tom
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
VERSION 2 by Tom Moulton 5/2009

This version supports importing Items, Sales Kits and BOM

The fields used will be different depending upon what is being imported.
It will still be a good idea to have a fixed number of (empty) fields to keep parsing simple.

The first field is the record type and that will decide what the rest of the line means.

TYPE = BOM

BOM;PARENT;COMPONENT;WORK CENTER;dummy;dummy;QUANTITY;dummy;dummy;dummy;

The loc_code will be selected from a Pull-Down menu as a default for the entire import.
The work center will be added if needed.

TYPE = UOM

UOM;ABBR;NAME;dummy;dummy;dummy;DECIMAL PLACES;dummy;dummy;dummy;

TYPE = KIT

KIT;ITEM_CODE;STOCK_ID;Description;Category;dummy;Quantity;dummy;Currency;Price;

Where ITEM_CODE is the code for a Kit and Stock_id is a part of that kit (Quantity each)
The Currency and Price, if present will update the price of the kit.

TYPE = FOREIGN

FOREIGN;ITEM_CODE;STOCK_ID;Description;Category;dummy;Quantity;dummy;dummy;dummy;

Where ITEM_CODE is the upc/etc code for an item and Stock_id is the item as we know it

ITEM;ITEM_CODE;STOCK_ID;Description;Category;units;dummy;MB_FLAG;Currency;Price;

Where Item_Code = STOCK_ID of the item.

TYPE = BUY

BUY;STOCK_ID;dummy;Description;Supplier Name;Supplier Units;Conversion Factor;dummy;Currency;Price;

Where Supplier must alrady exist

The rest should be as described elsewhere.

Readme of item import module is confusing and difficult to understand. (Downloaded today)
Can we give clear idea on what should be format of CSV.

There are two formats mentioned.
I tried first one but it raised array of errors.

Could you please state how to use this crucial feature ?

With Regards,

I see the quantity is used as a case quantity of the Stock Item we work with.

Are entries in the item_codes table required?

Why is item_code == stock_id in item_codes?
I expected item_code to be a foreign id, not a stock id.

tom

Why do they both have category_id?

Since item_codes.id is the key what is the meaning of a specific stock_id appearing more than once? Will they have different category_id's?

Also there is a Quantity field, isn't the quantity of a given item determined by looking it up in Stock_Moves as menti0ned above?

I would like to update items_import, but will need to understand what needs to be done in very simple terms.

I may be able to take initial stock quantities at the same time, but I think that is a secondary step at th moment...

tom

in gl_trans, it looks like the type_no field ties up to the trans_no field in stock_moves

Janusz

I am happy to dive in manually for now - can you confirm that my understanding is correct?

To add stock I need to work with the following tables:
* stock_master
* loc_stock
* item_codes
* stock_category

to set pricing I only need to update
* prices

and to set quantity on hand I need to work with
* stock_moves
* gl_trans