How to add product info to MySQL databse

[fasad]

I have the osc cart easily installed through my hosting provider -fantastico package- i learning alot on this forum ( thanks all :P ) now i need to add the products i want to sell to the database , i can't add them one by one :unsure: , but i have a large database of the products in MS excel, but i can prepare an MS Access db/tables if needed.

 

Is there away to export my product data into osc database, what are the steps?

and what are the limitation of database fields , tables i.e. structure?

 

 

 

regards

[peterr]

now i need to add the products i want to sell to the database , i can't add them one by one  :unsure: , but  i have a large database of the products in MS excel, but i can prepare an MS Access db/tables if needed.

 

Is there away to export my product data into osc database, what are the steps?

and what are the limitation of database fields , tables i.e. structure

 

Have just had to load the osC db from M$ Access, there were a few things I would do differently next time (it was for a quick demo, so I took lots of shortcuts).

 

1. Look at the file /catalog/install/oscommerce.sql , and you will be able to see the structure and data required for the following tables:

 

* categories

* categories_description

* products

* products_description

* products_to_categories

 

These are the primary tables needed to define/load your product information.

 

2. Work out your product categories first. You may even have sub categrories as well. Possibly you have something like this defined in the Excel sheet/s.

 

3. Because there is so much 'system/config' data in the db, my next step would be to actually load (import) oscommerce.sql

 

4. Remove all rows from the following tables:

 

* categories

* products

* products_attributes

* products_description

* products_notifications

* products_to_categories

 

The simlest method to do this would be use the SQL code from file 'oscommerce.sql', for example, for table "products_to_categories"

 

DROP TABLE IF EXISTS products_to_categories;
CREATE TABLE products_to_categories (
 products_id int NOT NULL,
 categories_id int NOT NULL,
 PRIMARY KEY (products_id,categories_id)
);

 

5. Load the 5 tables mentioned in step 1. You can export from a MySQL db to a CSV, or 'Excel' file, so no doubt you can also import using the same format. Depends what version of phpMyAdmin you are using.

 

The SQL tab in phpMyAdmin has a "insert data from a textfile into table" link, and when you select that, you can specify the delimiters, terminators,etc,etc.

 

6. In formatting your Excel data, you will no doubt have to copy/paste your columns into a new sheet, and ensure the sequence of the columns is exactly the same as the layout/structure shown in the file oscommerce.sql

 

There are some tricks with doing that, I only know a few. For example, the products table, make all the rows in column 1 in your excel sheet as follows:

 

INSERT INTO products VALUES (

 

Then column 2 (row 1) will be...

 

1

 

and column 2 (row 2) will be ..

 

2

 

Col 1 is defined as general, and col 2 as numeric. Define other fields this way. I'm sure you can probably invent a better method, but I've done it this way, so that when you save as "CSV, comma delimitered", it will turn out in the correct format (I only tested with a few columns).

 

This is all just off the top of my head, and I'm very new to osC, and I'm positive there are much better ways of doing this, so others can advise.

 

Btw, I've given this example, "WITHOUT" any referencial integrity in mind at all. That is, don't try and load a product that has category "3", if category value "3" doesn't exists. Excel can do lookups, so I can _maybe_ help out there (it's a function I think).

 

The best advise I can give is to study the sample data, and see how it all fits together. Reformatting your Excel data to suit the import into the MySQL db won't be easy, I'd say you are in for some serious hack work. :)

 

Peter

[dootch]

Whoaa guys there is a contribution called Easy Populate that allows the simple import of CSV to OSC. Its is very easy to install and is very widely used. Check it out. The drawback is it only allows 400 or so products to be uploaded at a time, but it creates the splits. FYI I have 12k or so products in my DB.

[peterr]

Hi,

 

.. and I'm positive there are much better ways of doing this, so others can advise.

 

.. and then, the better method arrives .........

 

Whoaa guys there is a contribution called Easy Populate that allows the simple import of CSV to OSC.  Its is very easy to install and is very widely used.  Check it out.  The drawback is it only allows 400 or so products to be uploaded at a time, but it creates the splits.  FYI I have 12k or so products in my DB.

 

Can Easy Populate only do from CSV, or can it be easily modified to import from an Access db ?

 

Peter

[dootch]

EasyPopulate (EP) lets you quickly populate and update thousands of products in many categories for an OSC store with data from an:

Excel spreadsheet

OpenOffice spreadsheet

Filemaker database

Access database

Any delimited text file (you can now set a config var for the separator character)

[peterr]

Hi Brian,

 

EasyPopulate (EP) lets you quickly populate and update thousands of products in many categories for an OSC store with data from an:

Excel spreadsheet

OpenOffice spreadsheet

Filemaker database

Access database

Any delimited text file (you can now set a config var for the separator character)

 

That is _very_ good news, I have a new client who currently uses an Access db, so using EP will make that so much easier to load the (osC) db.

 

Thanks, :)

 

Peter

[marbor]

If i have an access DB, will i have to convert it to an tab delimited text file before i can use it with easy Populate?

[peterr]

Hi Brian,

 

EasyPopulate (EP) lets you quickly populate and update thousands of products in many categories for an OSC store with data from an:

Excel spreadsheet

OpenOffice spreadsheet

Filemaker database

Access database

Any delimited text file (you can now set a config var for the separator character)

 

Having tested Easy Populate today, I have realised that EP will of course only import data in the format/s it can export in. :D

 

Therefore, it cannot import data from Excel, OpenOffice, Filemaker or Access, but can import data that is exported first from those tools.

 

From the Easy Populate contribution:

 

Easy Populate lets you download all your products into a csv file, edit them in the spreadsheet of your choice, and the reupload them to update your products, changing prices, categories, quantities, etc.

 

Peter

[peterr]

Hi,

 

If i have an access DB, will i have to convert it to an tab delimited text file before i can use it with easy Populate?

 

Yes, and of course , all the columns will have to line up exactly. If you have the sample/demo osC up and running, use EP to do the exports, then study the formats, and then your Access exports will have to be exactly the same.

 

I need to do exactly the same (convert an Access db to a suitable format for importing to osC), so if you don't need it immediately, possibly we can either work together on it, or I can create some tool/utilty to do the Access export ??

 

This is just a maybe at present, I haven't thought it through much, but _do_ need to do it. An Access export doesn't even allow any options to swap columns around or do any sort of data manipulation, so what you see in the Access db, is what you get on export.

 

Peter

[kerch98]

What is the best way to work with images? I mean importing data from CVS file shouldn't be hard, but whats the best way to add images? Thank you

[peterr]

Hi,

 

What is the best way to work with images? I mean importing data from CVS file shouldn't be hard, but whats the best way to add images?

 

Do you mean, what is the best way to make/create images ??

 

Peter

[dootch]

We have found that when working with images that it is necessary to adhere to a strict naming convention of our image files. We have of 10k images in our db. We name them by part #/item# and have seperate folders set up for each beginning character and use ftp for image transfer. That being said we have 36+ image folders. This makes it very easy to use whatever to manage the DB. example For part #A1234 the image would be found at.... images/a/a1234.jpg or part#8568 would be found at ....images/8/8568.jpg . I am not saying this is the best way but it allows us to parse the data quickly in csv.

[kerch98]

thank you for such a detailed report. could you please advice on how to optimze the whole process of addin 500+ images to the database. is it possible to cut manual labor required.? what aumation tool can i use?

thank you

[dootch]

Kerch,

We get most of our image files from distributors for our products with the right naming conventions. The one we dont we use a program called picture ripper which will go to any site and pull the pictures down.

[peterr]

Kerch,

 

thank you for such a detailed report. could you please advice on how to optimze the whole process of addin 500+ images to the database. is it possible to cut manual labor required.? what aumation tool can i use?

 

I don't know if you want to automate referencing images, or whether you want to automate getting the images ??

 

Anyway, 'dootch' has gien some good tips for retrieving images.

 

For automatting the loading of the images into the database, only you know which image 'belongs' to which product, so if there is 500, that possibly means you will have to match 500 images with 500 products. You could have a look at Easy Populate 2.72 , which would assist you in loading the product/image data.

 

The stock/standard method to setup images is to place them in

 

/images/manufacturer

 

... where 'manufacturer' = the manufacturers name. I think for that many images, it would be best to keep them under that structure, unless they are your own products.

 

Automation, hmm, if you are retrieving the images from a manufacturers website, then the image name will usually be the product name, and also, the link to the image will usually have the "alternate text" reference, which may help you match an image to a product.

 

Peter

[fasad]

thanks for the help and useful tips :P

 

do you know guys if EP is limited to a 3 categories ( as the downloaded file ) shows in the excel sheet.

 

Or i can jsut insert anew Field(column) in Excel to a new categoy then upload the text file using EP.

 

will you help please?

 

thanks

[fasad]

may be my question wasn't clear,

 

i'm using EP.

 

after i have set the categories manually ( with 4 levels) and added some products to each catagory

 

then i downloaded the txt delemeted file using EP, opened it with Excel , add some more items to the products. and uploaded it.

 

after uploading, items were placed in wrong places, under wrong categories.

 

 

categories on the downloaded file looked like this

 

level0 | level1 | level2 | ERRO

 

for the items that goes under the forth level i found this

 

level1 | level2 | level3 | ERRO

 

that is, level0 was deleted and a left skip for the other levels was done.

 

while i ecpected to have something like this :

level0 | level1 | level2 | level3 | ERRO

 

 

so is it the maximum that EP can do 3 levels? or we can add more? if we can add more levels, how do we do that?

if not, then is there away around that?

 

to simplify the question more: How do you upload products to the database if you have more than 3 levels?

 

thanks

[♥ecartz]

If you read through the EP configuration, there is a setting for number of category levels. It currently defaults to 3, but you will see a commented out section for 7 levels. For your purposes, just change the 3 to a 4.

 

Hth,

Matt

[fasad]

thanks for the help, but to make it a full answer so others can benefit from it, i'll add:

 

To change the maximum level of categories in Easy Populate v 2.72:

 

Go to the file :

yourdomain/catalog/admin/easypopulate.php

 

at line 91 you will find:

 

91 // **** Max Category Levels ****

92 // change this if you need more or fewer categories

93 global $max_categories;

94 $max_categories = 3; // 7 is default

 

i advise the new users to read the comments in this file. myself too ;)

[Guest]

Having a problem with the install, when I go to use EP I get

Parse error: parse error, expecting `','' or `';'' in /home/lincgear/public_html/oscom/admin/easypopulate.php on line 659

I must have missed something, but I cant seem to find what I did wrong. I did not edit the easypopulate.php at all. Any Ideas? Thanks in advance.

[peterr]

Hi,

 

Having a problem with the install, when I go to use EP I get

Parse error: parse error, expecting `','' or `';'' in /home/lincgear/public_html/oscom/admin/easypopulate.php on line 659

I must have missed something, but I cant seem to find what I did wrong. I did not edit the easypopulate.php at all. Any Ideas?

 

Lines 658 to 659 of /easypopulate.php

 

echo "<p class=smallText>";
echo FILENAME ': ' . $localfile . "<br>";

 

so, there is nothing wrong with the syntax, not that I can see anyway. :)

 

Check that you have uploaded ALL the file, it os 2085 lines. Make sure is only has a LF (line feeds/Unix), and that you upload the file in ASCII mode.

 

Peter

[Guest]

I found the error,

 

echo "<p class=smallText>";

    echo FILENAME . ': ' . $localfile . "<br>";

 

A space and a period after FILENAME will fix the problem.

Thank You.

[peterr]

Hi,

 

This will also work, and follows conventions used in 'osC':

 

<?php
$localfile = 'somefilename.txt';
echo "<p class=smallText>";
echo 'FILENAME' . ': ' . $localfile . "<br>";
?>

 

Please inform the author/s of EP of the syntax error.

 

Peter

[djbazzer]

wrong topic - sorry