Populating my shop database

Hi all.

First let me thank and congratulate Daniel for producing such a great cms.

I now apologise if this post has already been covered, here or elsewhere, as I am relatively new to cms, databases and spreadsheets.

I have been given stock.xls file along with the relative image files from my supplier. The stock.xls holds 3000+ stock items as you can imagine filling OpenCart with all these items is a daunting task. I am in the process of splitting the .xls file into separate .CSV files as follows:

product.csv (product_id [numbered 1 to 3100], quantity, model, manufacturer_id [numbered between 0 and 58],  shipping, price, sort_order [filled with 1's], date_added, date_modified, date_available, weight, weight_class, status, tax_class_id and viewed) columns.

Ive done the same for product_description.csv, image.csv, image_description.csv, category.csv, category_description.csv, product_to_category.csv and product_to_image.csv.

What I now propose to do is copy all supplied images to shop/image directory, then import each .csv file into their respective database table through phpMyAdmin.

My question is. Is this possible, or am I going to have to spend the next year inputing each item in through the shop admin'.

Any advice appreciated.

broose.

It is possible.

Probably not by using import from csv into the database. Rather, write a simple program to build a set of insert sql scripts from your csv files. This is the best way because you can easily try, test and repeat the script building and the import process if you find the results not quite right on the first attempt.

Add a column to the tables product, category and image as an alternative unique key. This is because the primary key of these tables is an autonumber field and some of your insert scripts will need to look it up later.

Import your products and use the added key field to get the actual key for when you import data into the tables that reference the base table. eg product_description, product_to_option, product_to_image etc. Same for categories and images.

I would also recommend that you delete all of the products and images from a basic installation and check the tables after each of the following steps. This will help confirm the design of the import scripts.

• Add the image(s) for a product.
• Add just one of your products and review the tables containing values.
• Add a category and do the same. Then sub category.
• Add a product to a category.

hope this helps

Bruce

I am planning to contribute an Import/Export module to OpenCart soon. If you can wait for few more days, you can use it. I am planning to upload it here.

I have been using it for a live site which has about 1500 products and 175 categories. The only outstanding problem I need to resolve is that of large text fields, greater than 255 characters, which get truncated when reading an uploaded XLS Excel Spreadsheet file. I am using an Excel Reader from http://sourceforge.net/projects/phpexcelreader, couldn't find a better Excel reader for this task.

Hi all.

Thanks for the rplies bruce and Jneuhoff.

Happen I should have given a little background:

I am no programmer! That said, what bit of programming I have done is self taught.

I've played with C++ (QT style), C and Pascal, so my knowledge of these is very basic. I have recently had to learn a little HTML and CSS, mainly to help my son with his little project he's had to do on his graphics design course. I haven't played with PHP or MYSQL – any database for that matter, apart from this project.

I am at the moment flirting with the idea of starting an online shop, looked at other CMS / cart systems i.e. ZenCart, osCommerce, EZPublish and they seem very complex. Then I stumbled across OpenCart and this has seemed refreshingly easy in comparison.

I only use Linux (PCLinuxOS) for various reasons. I've resurrected a redundant pc as a LAMP server where I can try out / test OpenCart – doesn't matter if I make mistakes, hopefully I'll learn from them.

Anyway. I went for broke and converted and split the supplied stock.xls into .csv files. Imported these into the OpenCart database through phpMyAdmin, uploaded all images and it seems to work – though I haven't done any extensive testing yet apart from adding items to basket and searching products. Admin side seems to be functioning OK too. The fields I wasn't too sure of I filled with 1 or 0.

My next questions – if I haven't bored you stupid yet – are as follows:

Q.1. I'm unsure of the functions of a few fields in some tables, namely sort_order and shipping – though I'm guessing shipping has to do with whether the items are to be posted, sent by courier or downloaded etc. Can anyone enlighten me?

Q.2. When I converted into .csv files, I made each *_id numbered from 1 to n, i.e.
category_id from 1 to 149.
product_id from 1 to 3099.
image_id from 1 to 3099.
parent_id from 1 to 144 (forming a tree using category_id, 0 being its root).
tax_class_id and weight_class_id set as the values from default installation (6 and 2 respectively).

is this set up OK, or should I have set them as say:

category_id from 10 to 159.
product_id from 160 to 3249.
image_id from 3250 to 6348.
parent_id from 10 to 154 (forming a tree using category_id, 0 being its root)?

Any advice is greatly appreciated.

Thanks for bearing with me, and please forgive my terminology – I'm new to this.

broose.

Well done.

The shipping flag is to indicate if a product requires shipping. The actual method for shipping is chosen by the customer from those that your store presents. The presented list is from the enabled shipping extensions (Administration) that are installed. Have a look in the contributions section for further examples of these.

Sort order determines the order that categories, products and extensions etc are displayed in the admin section and/or to the user