How to add new products directly with sql

[omidhz]

Hi,

I was looking for information on how to add new products to my store directly with a sql codes.

The reason is that I have many items to add all together and I have all the information in a spreadsheet (Excel file).

I was looking for the code that I can add to the sql and run it in the phpMyAdmin so that it adds my products information to the database without going through the admin area and adding them one by one.

I know that we need to add each product information to two tables (products and products description) but I am not sure about the code and how to write it.

 

I tried using the easy populate contribution but it is not working and has too many bugs and the provider is not supporting the contribution. That is why I need to add the products directly using sql.

 

 

Any suggestion would help.

[nsoft]

Hi,

I was looking for information on how to add new products to my store directly with a sql codes.

The reason is that I have many items to add all together and I have all the information in a spreadsheet (Excel file).

I was looking for the code that I can add to the sql and run it in the phpMyAdmin so that it adds my products information to the database without going through the admin area and adding them one by one.

I know that we need to add each product information to two tables (products and products description) but I am not sure about the code and how to write it.

 

I tried using the easy populate contribution but it is not working and has too many bugs and the provider is not supporting the contribution. That is why I need to add the products directly using sql.

Any suggestion would help.

You don't need any sql. All you need is Easy Populate contribution. It works nicely.

[omidhz]

You don't need any sql. All you need is Easy Populate contribution. It works nicely.

I have a problem with that contribution because it is only adding one item and then stops. I tried to get a solution for that from the contribution thread but looks like there are many other store owners with the same problem and no one is answering with any solution.

Do you have any idea how to fix this problem?

Thanks

[nsoft]

I have a problem with that contribution because it is only adding one item and then stops. I tried to get a solution for that from the contribution thread but looks like there are many other store owners with the same problem and no one is answering with any solution.

Do you have any idea how to fix this problem?

Thanks

I use EP contrib for about 3 months, every week I upload new items via EP interface and 3 times diring this period I uploaded all items 8000 from my SQL Server local database to oscommerce database. Had no any problem.

Sometimes my data file contents some illegal characters and upload stops. I fixed that row and it helps.

Did you get any error message after first uploaded item? May be you prepared wrong file format for upload? - it is very important.

[AlanR]

See: http://www.oscommerce.com/forums/index.php?act=ST&f=12&t=172479

 

Use phpMyAdmin and CSV files. None of the hassles associated with Easypopulate and clear, understandable results.

 

phpMyAdmin is a superb, specialized, tool designed for tasks like this.

[nsoft]

See: http://www.oscommerce.com/forums/index.php?act=ST&f=12&t=172479

 

Use phpMyAdmin and CSV files. None of the hassles associated with Easypopulate and clear, understandable results.

 

phpMyAdmin is a superb, specialized, tool designed for tasks like this.

You are right, AlanR, but phpMyAdmin deals with tables only, and omidhz have to know all relationships between products table and manufacturers, categories, specials and so on. It is not an easy way to update products: prepare one text or XLS file for every related table, import files one by one into database, test updates...

[omidhz]

I use EP contrib for about 3 months, every week I upload new items via EP interface and 3 times diring this period I uploaded all items 8000 from my SQL Server local database to oscommerce database. Had no any problem.

Sometimes my data file contents some illegal characters and upload stops. I fixed that row and it helps.

Did you get any error message after first uploaded item? May be you prepared wrong file format for upload? - it is very important.

Thank you for your reply.

Can you tell me what version of the easy populate are you using? I'm using the latest one (2.78).

 

Here is the error message that I get:

File uploaded.
Temporary filename: /tmp/phpBAyPMY
User filename: To Upload - Intel2.txt
SIZE_EP97185
| BX80547PG3 | intel/pent | Intel Pent | Get advanc | | 218.99 | 7 | | | 5 | Intel | Processors | Intel | | Taxable Go | Active !New Product!
| BX80547PG3 | intel/pent | Intel Pent | Get advanc | | 264.99 | 7 | | | 5 | Intel | Processors | Intel | | Taxable Go | Active Updated
1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's productivity, digital imaging, music and entertainment applic

INSERT INTO products_description (products_id, language_id, products_name, products_description, products_url) VALUES ( '590', 1, 'Intel Pentium 4 640 3.2 GHz, 800 MHz, LGA775 Socket, L2 2 MB', '

 

The other problem that I have is that I have changed my product model length to 20 and I have change it in my database and in easypopluate.php, but still when I try to upload products it is cutting off my product model to 12 characters!.

Any suggestion would be helpful.

Thank you for your support.

[nsoft]

Thank you for your reply.

Can you tell me what version of the easy populate are you using? I'm using the latest one (2.78).

 

Here is the error message that I get:

File uploaded.
Temporary filename: /tmp/phpBAyPMY
User filename: To Upload - Intel2.txt
SIZE_EP97185
| BX80547PG3 | intel/pent | Intel Pent | Get advanc | | 218.99 | 7 | | | 5 | Intel | Processors | Intel | | Taxable Go | Active !New Product!
| BX80547PG3 | intel/pent | Intel Pent | Get advanc | | 264.99 | 7 | | | 5 | Intel | Processors | Intel | | Taxable Go | Active Updated
1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's productivity, digital imaging, music and entertainment applic

INSERT INTO products_description (products_id, language_id, products_name, products_description, products_url) VALUES ( '590', 1, 'Intel Pentium 4 640 3.2 GHz, 800 MHz, LGA775 Socket, L2 2 MB', '

 

The other problem that I have is that I have changed my product model length to 20 and I have change it in my database and in easypopluate.php, but still when I try to upload products it is cutting off my product model to 12 characters!.

Any suggestion would be helpful.

Thank you for your support.

I use this one - 2.76-MS2 (with attributes) Modified by ManMachine on 15 April 2005. I think that you have the problems with product description text - ['s productivity, digital imaging, music and entertainment applic] - it contains "'" (apostrof) character. I had this problems also. Some advices:

1. You have to proccess your data and to delete all "dangerous" characters - "'", "/", "\", ">", "<". Actually all the characters where you will get error massage like that.

2. Try to enclose texts containing "dangerous" characters into quotes.

3. Proccess youe data so that "'" must look like "\'" - add slash.

[omidhz]

I use this one - 2.76-MS2 (with attributes) Modified by ManMachine on 15 April 2005. I think that you have the problems with product description text - ['s productivity, digital imaging, music and entertainment applic] - it contains "'" (apostrof) character. I had this problems also. Some advices:

1. You have to proccess your data and to delete all "dangerous" characters - "'", "/", "\", ">", "<". Actually all the characters where you will get error massage like that.

2. Try to enclose texts containing "dangerous" characters into quotes.

3. Proccess youe data so that "'" must look like "\'" - add slash.

Thank you so much for your reply,

That solved my problem.