Jump to content
phantomeye

Update inventory via database from CSV

Recommended Posts

Hi Guys,

 

I tried looking for a solution to update my inventory automatically from a csv file we receive every day/week. I would love to have this script run automatically, but am not sure how to do that. (ps version 1.4.x)

 

So currently this is what i do:

 

1) Create csv file with inventory update script for all my products

2) Copy and paste the script in the database in the ps_products table

3) Thats it, all my products are update in one shot. Takes about 5 minutes all together

 

Here is the script you can use:

 

UPDATE `ps_product` SET `quantity`=185 WHERE `id_product` = 4;

 

The only complicated part in this was setting up my csv file. So attached is a sample CSV file that you can use to manipulate the data. Here is the concatenate statement i ran to merge the script

 

=concatenate("UPDATE `ps_product` SET `quantity`=", D2, " WHERE `id_product` = ", A2, ";")

 

If someone knows how i can run this automatically, i would love to hear that. Essentially i place this file on my site and a cron job runs this everynight.

  • Like 1

Share this post


Link to post
Share on other sites

I can help with this with a regular subscription service. Quantities and/or prices are updated every day for less than $1 per day.

Share this post


Link to post
Share on other sites

Hi Guys,

 

I tried looking for a solution to update my inventory automatically from a csv file we receive every day/week. I would love to have this script run automatically, but am not sure how to do that. (ps version 1.4.x)

 

So currently this is what i do:

 

1) Create csv file with inventory update script for all my products

2) Copy and paste the script in the database in the ps_products table

3) Thats it, all my products are update in one shot. Takes about 5 minutes all together

 

Here is the script you can use:

 

UPDATE `ps_product` SET `quantity`=185 WHERE `id_product` = 4;

 

The only complicated part in this was setting up my csv file. So attached is a sample CSV file that you can use to manipulate the data. Here is the concatenate statement i ran to merge the script

 

=concatenate("UPDATE `ps_product` SET `quantity`=", D2, " WHERE `id_product` = ", A2, ";")

 

If someone knows how i can run this automatically, i would love to hear that. Essentially i place this file on my site and a cron job runs this everynight.

 

Hello.

would this much to me your work.

this scrip you say to put on the csv file, put exactly where it goes?

I would just something that I update only the quantities and prices of the various files that I receive from suppliers.

thanks for any help

Share this post


Link to post
Share on other sites

Hi Albarelys

 

To update both quantity and price, you need to run the following sql statement if you are looking up reference numbers.

 

UPDATE `ps_product` SET `quantity`=185,`price`=3.35 WHERE `reference` = "itemref1";

 

So open your csv / excel you receive from your supplier and point the concatenate statement to the whichever columns has the quantity, price and reference (item number). Here is what the concatenate statement looks like

 

=CONCATENATE("UPDATE `ps_product` SET `quantity`=", C2, ",`price`=", D2, " WHERE `id_product` = ", A2, ";")

 

ive attached a text file and jpg with examples. Copy and paste the concatenate code from above in column E.

 

Please note, always run a test on one product first to see if it works properly and then run it for all products. Good Luck !!

Sample.txt

post-26755-0-67718000-1374846332_thumb.jpg

  • Like 1

Share this post


Link to post
Share on other sites

Hi Albarelys

 

To update both quantity and price, you need to run the following sql statement if you are looking up reference numbers.

 

UPDATE `ps_product` SET `quantity`=185,`price`=3.35 WHERE `reference` = "itemref1";

 

So open your csv / excel you receive from your supplier and point the concatenate statement to the whichever columns has the quantity, price and reference (item number). Here is what the concatenate statement looks like

 

=CONCATENATE("UPDATE `ps_product` SET `quantity`=", C2, ",`price`=", D2, " WHERE `id_product` = ", A2, ";")

 

ive attached a text file and jpg with examples. Copy and paste the concatenate code from above in column E.

 

Please note, always run a test on one product first to see if it works properly and then run it for all products. Good Luck !!

 

 

Thank you for responding.

Now I try to do what you say.

Only one thing I did not understand, or csv file with the new quantity and price, where do I start it?

Share this post


Link to post
Share on other sites

Thank you for responding.

Now I try to do what you say.

Only one thing I did not understand, or csv file with the new quantity and price, where do I start it?

 

 

 

Hello, and thank you again for your help

I'll put in the work attachment of two products.

do you think are good?

the baby has only the id of the supplier and price

the second is processed.

of course I have to take only the part: UPDATE `ps_product` SET `quantity`=18,`price`=7.36 WHERE `id_product` = ACC-0065;

 

but now I do not know where to put this file?

 

waiting to be answered if possible, I wish you a good weekend.

thanks again

Alberto

post-644039-0-75200400-1374863024_thumb.jpg

post-644039-0-63058800-1374863046_thumb.jpg

Share this post


Link to post
Share on other sites

Hello phantomeye, thank you very much for the help you have given me, now I can almost do everything perfectly.

it remains only a big problem, or doing so I update both quantity and price, but it only updates the sql database and not in the store, the store is in fact the client side, both on the job the prices and quantities are not updated.

could you give me some help for this problem?

Thank you very much aid at the time you gave me.

but unfortunately with this problem I can not begin to work

expect from you.

thanks again

Share this post


Link to post
Share on other sites

HI Albarelys,

Is ACC-0065 your reference number or id_product. To me it looks like your reference and hence it will not update your products. Please correct your code to the following and then try:

 

UPDATE `ps_product` SET `quantity`=18,`price`=7.36 WHERE `reference` = "ACC-0065";

 

reference is your model number / item number

id_product is the prestashop autogenerated unique id for the table ps_product.

 

Please again, only do it on one product first, if it works, then copy and paste for all products.

Good luck !

Share this post


Link to post
Share on other sites
HI Albarelys, Is ACC-0065 your reference number or id_product. To me it looks like your reference and hence it will not update your products. Please correct your code to the following and then try:
 UPDATE `ps_product` SET `quantity`=18,`price`=7.36 WHERE `reference` = "ACC-0065"; 

reference is your model number / item number id_product is the prestashop autogenerated unique id for the table ps_product. Please again, only do it on one product first, if it works, then copy and paste for all products. Good luck !

 

 

 

Hello, excuse me for being here again.

I did as you told me but I do not. where am I wrong?

in Annex No. 2 see the answer you gave me after the query in sql

tells me he has changed a row

I launched:

 

UPDATE `ps_product` SET `quantity`=185,`price`=3.35 WHERE `reference` = "itemref1";

 

UPDATE `ps_product` SET `quantity`=19,`price`=107.36 WHERE `id_product` = 8090;

 

Annex No. 4: see that I replaced the base price, but not the quantity.

Annex No. 5: This is the insertion side customers, how do you see the price has not changed, nor the quantity.

 

Annex 6: as you can see he followed the instructions correctly, the problem but do not send me on the site.

 

Another question that I wanted to faree, price lists I receive have other product-id, how do I associate them ID_ self-generated by prestashop product?

 

sorry for the many questions

and thanks for the previous answer

really thanks

post-644039-0-25768700-1374947771_thumb.jpg

post-644039-0-07292700-1374947783_thumb.jpg

post-644039-0-09317300-1374947795_thumb.jpg

post-644039-0-62345300-1374947808_thumb.jpg

Edited by albarelys (see edit history)

Share this post


Link to post
Share on other sites

Hi Albarelys,

I believe there is an understanding problem here. Please do the following to really understand what it is we are trying to achieve:

 

1) Goto MySQL and export the table ps_product (include all header names)

2) This will show you all the field names in the table

3) We are changing the price and quantity based on the reference number (riferimento)

4) So please run the following command only and see if changes the right field

5) If it does not, then you need to look at field names (step 2) and correct the statement to reflect the correct names

UPDATE `ps_product` SET `quantity`=18,`price`=7.36 WHERE `reference` = "RM120";

 

That code should update the product name 'Revoltec Neon a Catodo Freddo da 30cm Blu' to show quantity 18, price 7.36. If the price field you want to change is different, just look up the correct price field name and change it in the code.

  • Like 1

Share this post


Link to post
Share on other sites
Hi Albarelys, I believe there is an understanding problem here. Please do the following to really understand what it is we are trying to achieve: 1) Goto MySQL and export the table ps_product (include all header names) 2) This will show you all the field names in the table 3) We are changing the price and quantity based on the reference number (riferimento) 4) So please run the following command only and see if changes the right field 5) If it does not, then you need to look at field names (step 2) and correct the statement to reflect the correct names
 UPDATE `ps_product` SET `quantity`=18,`price`=7.36 WHERE `reference` = "RM120"; 

That code should update the product name 'Revoltec Neon a Catodo Freddo da 30cm Blu' to show quantity 18, price 7.36. If the price field you want to change is different, just look up the correct price field name and change it in the code.

 

 

 

Hello.

I deleted the ito and redone again, in case there was something that gave conflict.

I sent this query:

UPDATE `ps_product` set `quantity` = 15, `price` WHERE `reference` = 1:36 = "DEA14363";

 

as you told me, changed only the name and code being another article.

. in the office I changed the base price, but not the final price, however the amount left them as they were without changing anything.

I'll put you attached the result, the photo of mysql, and product.sql way it turned out after updating the price.

I find it strange that I cvambi only the price and not the quantity.

if you come up with .....

I while I still have to change some parameters.

I do want to thank you very warmly for the help you're giving me, I hope to succeed in this business.

look your news and still many thanks

Alberto

post-644039-0-36048100-1375223138_thumb.jpg

post-644039-0-68479900-1375223154_thumb.jpg

_ps_product.txt

Share this post


Link to post
Share on other sites

Hello.

I deleted the ito and redone again, in case there was something that gave conflict.

I sent this query:

UPDATE `ps_product` set `quantity` = 15, `price` WHERE `reference` = 1:36 = "DEA14363";

 

as you told me, changed only the name and code being another article.

. in the office I changed the base price, but not the final price, however the amount left them as they were without changing anything.

I'll put you attached the result, the photo of mysql, and product.sql way it turned out after updating the price.

I find it strange that I cvambi only the price and not the quantity.

if you come up with .....

I while I still have to change some parameters.

I do want to thank you very warmly for the help you're giving me, I hope to succeed in this business.

look your news and still many thanks

Alberto

 

 

 

In the end I managed to change the prices.

I got there just five minutes ago sifting through the sql, but really thank you for your help.

now for the price I did the following:

 

UPDATE ps_product_shop

SET price = '1000 ', minimal_quantity = '1' WHERE id_product = '3 '

 

the table was wrong, the correct one is ps_product_shop

 

and works on the price.

 

I use the version of prestashop is the ver.1.5.4.1 and perhaps differs from yours that is an earlier version, I thought that not centered is nothing I've wasted your time

 

I'll see if I can find some other table that has quantity and if all goes well I finished sailors have

 

I thank you for leaving aside only for having responded already and without you I would not have to do anything risucire

 

ps do you think you can add a "table quantity" in this table?

 

thanks thanks thanks

Alberto

Share this post


Link to post
Share on other sites

In the end I managed to change the prices.

I got there just five minutes ago sifting through the sql, but really thank you for your help.

now for the price I did the following:

 

UPDATE ps_product_shop

SET price = '1000 ', minimal_quantity = '1' WHERE id_product = '3 '

 

the table was wrong, the correct one is ps_product_shop

 

and works on the price.

 

I use the version of prestashop is the ver.1.5.4.1 and perhaps differs from yours that is an earlier version, I thought that not centered is nothing I've wasted your time

 

I'll see if I can find some other table that has quantity and if all goes well I finished sailors have

 

I thank you for leaving aside only for having responded already and without you I would not have to do anything risucire

 

ps do you think you can add a "table quantity" in this table?

 

thanks thanks thanks

Alberto

 

 

 

hello.

I also found the rest of the table to be modified is

ps_stock_available

 

UPDATE ps_stock_available

September quantity = '12287 'WHERE id_product = '5'

with this query I regularly updates the amount

 

 

. today are really happy to have almost solved the issue, now I see the light at the end of the tunnel.

 

you know if you can put together in a single quantity or price list?

 

I thank you again so much for the help you have given me,

if a day do you spend in Italy offer you a dinner, I hope not to offend

Share this post


Link to post
Share on other sites

Hi Alberto,

 

Wow that is really good that you were able to solve this. Yes my solution is for 1.4.x and earlier, sorry for the confusion. I havent yet worked on PS 1.5, but it seems they have changed the tables quite a bit.

 

Since its in two separate tables, you would probably have to run two separate SQL update scripts, as you have shown above. I highly recommend you do a backup of your database first. Next perform a test on only 1 product too see the results.

 

If the test is successful, then you can apply it to whole list of products.

Dont forget to post your successful script to the forum so others can use it.

 

Good Luck !

Share this post


Link to post
Share on other sites

Hi Alberto,

 

Wow that is really good that you were able to solve this. Yes my solution is for 1.4.x and earlier, sorry for the confusion. I havent yet worked on PS 1.5, but it seems they have changed the tables quite a bit.

 

Since its in two separate tables, you would probably have to run two separate SQL update scripts, as you have shown above. I highly recommend you do a backup of your database first. Next perform a test on only 1 product too see the results.

 

If the test is successful, then you can apply it to whole list of products.

Dont forget to post your successful script to the forum so others can use it.

 

Good Luck !

 

 

Hello, the fault was mine. you wrote that it was for the version 1.4x, a fool I did not take in cosider that it could change and you've wasted your time and I apologize for that.

unfortunately I will have to launch two scripts, but it is not a problem,

the problem and associate the id of prestashop on that of the supplier, which means you can make macros in excel but it is a bit long, I have to try to do so that it is automatically several times a day.

but today I'm really happy to have already solved.

I'm trying to solve the problem on descizione I is truncated and remains short, I wanted to solve this problem before you begin

. said this thank you very much for your help patience and kindness had in mei comparisons.

I know you need a script to do it automatically, even if you solve this you'll be the first to know

caricvato soon as I get all the catalog will test everything and if all goes well (now I have only tested on a product) will write in dettagòio everything so that others can use it and maybe improve

again thank you and hello

Alberto

Share this post


Link to post
Share on other sites

You're welcome ! Im glad we were able to solve the problem.

 

For association of product id with that of the supplier, export your current ps_product_shop with headers. Then delete all the columns except id_product and id_reference. This will give you a new file with both the ps_id and supplier id. Now you can update the file with the new inventory everytime you receive the file from your supplier.

 

Just run a simple 'vlookup' in excel to get the updated inventory and price values from the suppliers sheet.

Share this post


Link to post
Share on other sites

You're welcome ! Im glad we were able to solve the problem.

 

For association of product id with that of the supplier, export your current ps_product_shop with headers. Then delete all the columns except id_product and id_reference. This will give you a new file with both the ps_id and supplier id. Now you can update the file with the new inventory everytime you receive the file from your supplier.

 

Just run a simple 'vlookup' in excel to get the updated inventory and price values from the suppliers sheet.

 

 

 

 

Hello, I am again here ehhheh.

I encountered a problem, that is, every day I get a list, which I will elaborate on and then upload. the problem will be that I will have the following, I hope to explain well ....

the file will modify all fields correctly which of course there are, but there are those who will leave the previous amount, thus leaving a positive quantity since the its id has not been touched, the easiest thing I think it send one query that before I resets all quantities, and only after'll send the data with the correct amounts, and those that there are no last price will be left to 0 and I solve the problem.

but I looked everywhere and I could not find an example of how to do to clear the entire column in sql, I have tried to put the id and then the first, the last, but it did not work, how can you do?

Thank you so much for any answers help

Alberto

 

 

p.s :

UPDATE ps_product_shop

SET price = '12287', wholesale_price = '3548' , minimal_quantity = '3548' WHERE id_product = '1'

 

-------------------------------------------------------------------------------------------------------------------------

 

for product quantity

 

 

UPDATE ps_stock_available

SET quantity = '12287' WHERE id_product = '5'

 

 

 

Over here I put the query that I am using to update the quantities and prices, unfortunately I have to do twice the work, but no matter, I see that it is very fast once developed the whole.

The problem, however, and I have to reset the amount before launching the query, as this will only change the id currently available, leaving the amount of the previous positive list and this is not just fine.

you know how to clear an entire column from the table?

 

thanks for any help

Share this post


Link to post
Share on other sites

hello. here I am again

 

 

I change everything without problem, the one big problem and that does not allow me to change more than one, it'll take two already

 

UPDATE ps_stock_available

SET quantity = '12287 'WHERE id_product = '315'

SET quantity = '12287 'WHERE id_product = '316'

 

 

 

I get this error

 

# 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 'SET quantity = '12287' WHERE id_product = '316'' at line 3

how can I do to risovere this problem?

thanks as always for any response

 

thanks

Alberto

Edited by albarelys (see edit history)

Share this post


Link to post
Share on other sites

Hi Albarelys,

The code to update the whole table is

UPDATE `ps_stock_available` SET `quantity`=18;

 

Please be aware, this will set the quantity for all products to 18.

 

Share this post


Link to post
Share on other sites

Hi Albarelys,

The code to update the whole table is

UPDATE `ps_stock_available` SET `quantity`=18;

 

Please be aware, this will set the quantity for all products to 18.

 

 

Hello, unfortunately this is not good, as each id to a different number on the other

I found online some formulas that can perhaps serve, I wrote to them but the post is very old and certainly do not think that will answer

.

I read that this command can change multiple lines at once but I do not know what should I write in the fields

 

REPLACE table_name (field 1, field 2, ..., field N) VALUES

(value 11, value 12, ..., 1N value)

(value 21, value 22, ..., 2N value)

...

(value M1, M2 value, ..., value MN)

-------------------------------------------------- -----------------------------

other

 

 

REPLACE table (ID, field) VALUES

(1, newValue 1),

(2, newValue 2),

(3, newValue 3),

 

 

I'll have to write in the name table ps_stock_available

then no one else know if I should put commas or another, you think of something?

thanks pereventuali answers

Albarelys

Share this post


Link to post
Share on other sites

I'm not sure, but I do not recommend the "replace" command. This will change the name of the field and that will effect prestashop badly.

 

Your above code looks okay, only remove the quotes from before and after the number values. Also you have to repeat the Update statement for each line. Eg:

 

UPDATE ps_stock_available SET quantity=12287 WHERE id_product = 315;

 

 

Share this post


Link to post
Share on other sites

Here is a quick solution you can put into a php file and make a scheduled cronjob on your server (or run from browser)

 

It works on ps 1.4.xx but myabe you can varie this to works newer versions too...

 

change your DB values and path to the csv file and put this php next to csv to run properly

 

----

This can set the retail, a wholesale price and quantites, but you can broaden it easily

<?php 


mysql_connect("localhost", "db_user", "db_password") or die(mysql_error()); 
mysql_select_db("db_name") or die(mysql_error()); 


$row = 0; 

$update_table = "ps_product"; 


$handle = fopen("/var/www/yoursite.com/path_to/inventory.csv", "r"); 


while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) { 
    $num = count($data); 
    echo "$num field update in this row $row:\n"; 
    $row++; 
    for ($c=0; $c < $num; $c++) { 
        if ($c = 1) { 
        $reference = $data[($c - 1)]; 
        echo $reference . " choosen reference\n"; 
        } 
        if ($c = 2) { 
        $price = $data[($c - 1)]; 
        mysql_query("UPDATE $update_table SET price='$price' WHERE reference='$reference'")  
        or die(mysql_error());   
        echo $price . " reatil price updated to\n"; 
        } 
		 if ($c = 3) { 
        $wholesale_price = $data[($c - 1)]; 
        mysql_query("UPDATE $update_table SET wholesale_price='$wholesale_price' WHERE reference='$reference'")  
        or die(mysql_error());   
        echo $wholesale_price . " wholesale price updated to\n"; 
        } 
        if ($c = 4) { 
        $quantity = $data[($c - 1)]; 
        mysql_query("UPDATE $update_table SET quantity='$quantity' WHERE reference='$reference'")  
        or die(mysql_error());  
        echo $quantity . " quantity updated to\n";
		echo " --------------------\n"; 
        } 

    } 
} 

fclose($handle);
echo " * * * * * * * * * * * * * * *\n"; 
echo strip_tags("<b><i>Update done!</i></b>","<b>");
echo " * * * * * * * * * * * * * * *\n"; 
?> 
Edited by fitgura (see edit history)

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More