This script will help you to manage main OsCommerce components. Below you will find a detailed description how to generate (export) and to populate (import) products, categories, manufacturers and products attributes via Excel.

Default installation places Excel Populate into Catalog/Products box at the admin part of OsCommerce. Any additional fields (columns) could be removed or added in generated Excel file for customized versions of OsCommerce.

Generating Excel file (exporting process).

Go to Admin->Catalog/Products->Excel Populate (Picture 1)

Picture 1. Excel Populate in Catalog/Products box

You can see a simple file manager here. This file manager show files from admin/temp/ folder at your store server. A form to upload modified file is in the top left part of the content page. Below there is a list with excel files and some of their properties (file name, file size, last modified).

Click on "Generate file" link at the bottom right corner of the content part.

A new form will appear at the right column (Picture 2). You can specify what part of database you need to generate. Check/uncheck checkboxes near products, categories, manufactures or products attributes if you do not need/do need appropriate component for modification. You can generate all products items or only products from specific category. Below the categories dropdown you will see a minimum and maximum inputs for products_id. It shows min and max id for current products tables by default. Min and max id condition intersection with chosen categories. If you click off the products checkbox than product will not be generated in products sheet. Same rules are applied for products attributes. Similar rules are for categories and manufacturers but you can not specify "from category" for items here.
Click on "Generate" button and you'll see new Excel file in files list.

Picture 2. Excel Populate Generating Form

New message about successful file generation will appear at the top of the page. This message includes filename. Date of generation is included in the generated file name. Download generated file by clicking on its name or by clicking on icon near the file name.

Open file for changing. You'll see something like on Pictures 3,4. Look at the left bottom of the file and you'll find sheets with products, categories, manufacturers, attributes, options there.

Picture 3. Product sheet with default OsCommerce products

Picture 4. Attributes sheet with default Oscommerce products attributes

Populating Excel file (importing process).

Upload the modified file via the admin interface or via FTP. We recommend changing file name of changed Excel file to be able to see old (generated) file and new (changed) file. Old file will be a sort of a backup for you.

Changing items

Changing is very simple process. You can change any field except of fields which are used for technical purposes.

Products sheet. Change "id" in the top left cell to "PPL" (without quotation marks). This will let script know that there are changes, added or deleted values and products will be populated/changed appropriately. Do not change id value for product items. Same rules are applied to other components. Id shows which product is about to be changes or added for the script. You may change name, description, url, price, model, weight, status, date_added, last_modified, date_available, ordered without any precautions. These fields must be in appropriate data format like string, integer, real, date etc only. Do not change language_id field. Also products must be in same order like languages are. So if first is English after Spanish than such order should be applied to all added/changed products. So you can not change id and language_id cells for products items.
Image cell is part of URL to product images. This part is URL without http://store_url/store_dir/images/ prefix. You have to upload this image via FTP.
Quantity and quantity_new cells. Do not change quantity because such changing is ignored by script; set necessary quantity to quantity_new cell if it is required. Why it is so? Because during editing process quantity of product can change - someone can buy something. After you populate products quantity only for necessary products will be changed. Manufacturers_id is presented via manufacturers name. But this manufacturer has to be present in the manufactures sheet. In other case you'll see a warning message. In other case manufacturers should be present in database. If this is a new manufacturer than simply add it to manufacturers sheet. The hardest thing is changing categories_id cell. Here product categories are presented. As you know product in oscommerce can be in one or more categories. If product is in more than one category than click F2 or go to formula bar in Excel then use Alt+Enter and add new category below the current one. Categories must include full path to product devided by "=>" sign. For example "DVD Movies => Science Fiction". Here "Science Fiction" is a subcategory of "DVD Movies". This finishes the changing products section.

Categories sheet. The operation is the same as in products for "id" and parent_id. Change "id" in the top left cell to "PPL" (without quotation marks) and script will know that there are changes and categories will be populated/changed appropriately. Do not change id value for category items. Parent_id is for parent category. Parent category should include full path to current category divided by "=>" sign. Do not change language_id and remember about sort order of languages. Languages rule is only for multilanguage store. For one language store you can simply do not fill this cell. Change name, image, sort_order, date_added, last_modified as you needed.

Manufacturers sheet. The operation is the same as for products, categories for "id". Change "id" in the top left cell to "PPL" (without quotation marks) and script will know that there are changes or added values and manufacturers will be populated/changed appropriately. Do not change id value for manufacturer items. Change url, name, image, date_added, last_modified as you needed.

Attributes sheet. The operation is the same as for products, categories, manufacturers for left top cell. Type "PPL" (without quotation marks) ) and script will know that there are changes or added values and manufacturers will be populated/changed appropriately. Do not change first columns id value near product name. You can change product name - it is not used here. It is displayed only to show you with which product you work with. Do not change first and second rows. They are to help you with attributes options. Digits in main content of attributes sheet are prices to appropriate option value for appropriate product. You can use it but you must to add attributes correctly. This means that you can not use "Red" value to "Size" option for product attribute. Oscommerce allows this but this is incorrect. To simplify the attribute changing process you could use ajax attributes manager in admin area. With this script you'll never do such mistakes. Or you could delete all current attributes and add it using Excel Populate. So change any price and price for attribute will be changed. Add new price and attribute with such price will appear for appropriate product. Delete it and attribute for product will be deleted.

Adding items

Remember! Populate file with new items only one time. If will add it more times than these items will be added again.

Products sheet. Leave id cell empty and fill all other cells with appropriate values. All other rules are same as for products editing.

Categories sheet. Leave id cell empty and fill all other cells with appropriate values. All other rules are same as for editing categories.

Manufacturers sheet. Leave id cell empty and fill all other with appropriate values. All other rules are same as for editing manufacturers.

Attributes sheet. Fill in new price and appropriate attributes for appropriate product will be added.

Deleting items

Products sheet. Type "D" letter (in any case) in url cell and product will be deleted. Type it in first language row for multilingual stores. Why in url cell? Because it is near product name and does not used often.

Attributes sheet. Delete price of appropriate products attribute and attribute will be deleted.