How to update Prestashop product quantities from a csv file

24 thoughts on “How to update Prestashop product quantities from a csv file”

1. Javier 23 January 2015 at 20:40

   Sorry for my bad English.

   Does this script omits references CSV that are not in the database?

   I’m interested in updating stock only those in the database.

   My CSV has (;) instead of (,) what modifications should be made in the script?

   No product combinations in my Prestashop, is it could further simplify the script?

   Thank you for your contribution and kind regards.

   Reply ↓
   1. WhileTrue Post author26 January 2015 at 14:34

      Hi Javier,
      the script only updates the stock quantities for products already in the database.
      Also, you can edit the line 16 (“,” to “;”) to have the script process CSV files using the “;” as field separator.
      You can delete lines 36-52 to disable product combination updates.
      Please remember to create a full database backup before testing the script.

      Reply ↓
2. Mauro 29 January 2015 at 16:40

   Great work.
   I’ve only 1 question
   Where I can found the description of the combination?
   I want to update only one of this

   Thanks a lot
   Mauro

   Reply ↓
3. gian 4 April 2015 at 17:20

   Dear WhileTrue, great and helpful script….
   I suggest to add this few raws (or similar as you prefer) script to reset quantity…

   because if you receive a CSV with less quantity than you have in your ecommerce, at the end of execution your qty will wrong

   AT line 26

   //START
   $default_qta = 0;
   $updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”product SET quantity = :default_qta”);
   $updateAll->execute(array(‘:default_qta’=>$default_qta));

   $updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”product_attribute SET quantity = :default_qta”);
   $updateAll->execute(array(‘:default_qta’=>$default_qta));

   $updateAll = $db->prepare(“UPDATE “._DB_PREFIX_.”stock_available SET quantity = :default_qta”);
   $updateAll->execute(array(‘:default_qta’=>$default_qta));
   // END

   Reply ↓
   1. Ton Rijsdijk 5 January 2016 at 13:51

      I know, this is a rather late comment, but still, I’m dealing with it right now.
      First off, I want to wish you and all that are near to your heart all the best in this new year.

      Because I have different suppliers, I can’t just set all quantities to zero, because this will also affect products that are not in the csv-update.
      I need to reset only the stock of those products that are mentioned in the csv.
      What do I need to change?

      Reply ↓
      1. WhileTrue Post author5 January 2016 at 15:22

         Hi Ton,
         try copying lines from 13 to 75 and pasting them at the end of the file, then change line 29 as follows:
         $quantity = 0;
         Then change line 47 as follows:
         $res = $db->prepare("update "._DB_PREFIX_."stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0");
         Then change line 50 as follows:
         $res = $db->prepare("update "._DB_PREFIX_."product set quantity = :q where id_product = :id_product");

         This way your csv file is scanned two times: the first time the products stock is reset, the second time the quantities get updated. Please test it extensively before using it in production.

         Reply ↓
         1. Ton Rijsdijk 8 January 2016 at 12:10

            Indeed a brilliant idea. I’ve tried it and all product stocks are updated. However, the total amount of combinations of each product are not correct and only get higher every time I run the script.
            This is what I made of your suggestions:

            setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            set_time_limit(600);

            // RESET CYCLE
            $row_num = 0;
            if (($handle = fopen($remote_csv_file, “r”)) !== false) {
            while (($data = fgetcsv($handle, 1000, “;”)) !== false) {
            $row_num++;
            if ($row_num == 1) {
            // SKIP FIRST LINE (HEADER)
            continue;
            }
            if ($data[0] == ” || !is_numeric($data[1])) {
            // SKIP EMPTY VALUES
            continue;
            }
            // INPUT SANITIZATION
            //$reference =’:reference’;
            //$quantity =’:quantity’;
            $reference = trim($data[0]);
            $quantity = 0;

            try {
            $res4 = $db->prepare(“SELECT id_product, id_product_attribute from psh_product_attribute WHERE reference = :reference”);
            $res4->execute(array(‘:reference’=>$reference));

            if ($res4->rowCount() > 0) {

            // IT’S A PRODUCT COMBINATION

            $row4 = $res4->fetch();

            $res = $db->prepare(“update psh_stock_available set quantity = :q where id_product_attribute = :id_product_attribute”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

            $res = $db->prepare(“update psh_product_attribute set quantity = :q where id_product_attribute = :id_product_attribute”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

            $res = $db->prepare(“update psh_stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            $res = $db->prepare(“update psh_product set quantity = :q where id_product = :id_product”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            } else {

            // IT’S A SIMPLE PRODUCT

            $res4 = $db->prepare(“SELECT id_product from psh_product WHERE reference = :reference”);
            $res4->execute(array(‘:reference’=>$reference));
            if ($res4->rowCount() > 0) {
            $row4 = $res4->fetch();

            $res = $db->prepare(“update psh_stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            $res = $db->prepare(“update psh_product set quantity = :q where id_product = :id_product”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));
            }

            }
            } catch (PDOException $e) {
            echo ‘Sql Error: ‘. $e->getMessage() .”;
            }
            }
            fclose($handle);
            }
            // MAIN CYCLE
            $row_num = 0;
            if (($handle = fopen($remote_csv_file, “r”)) !== false) {
            while (($data = fgetcsv($handle, 1000, “;”)) !== false) {
            $row_num++;
            if ($row_num == 1) {
            // SKIP FIRST LINE (HEADER)
            continue;
            }
            if ($data[0] == ” || !is_numeric($data[1])) {
            // SKIP EMPTY VALUES
            continue;
            }
            // INPUT SANITIZATION
            //$reference =’:reference’;
            //$quantity =’:quantity’;
            $reference = trim($data[0]);
            $quantity = ($data[1] >= 0) ? $data[1] : 0;

            try {
            $res4 = $db->prepare(“SELECT id_product, id_product_attribute from psh_product_attribute WHERE reference = :reference”);
            $res4->execute(array(‘:reference’=>$reference));

            if ($res4->rowCount() > 0) {

            // IT’S A PRODUCT COMBINATION

            $row4 = $res4->fetch();

            $res = $db->prepare(“update psh_stock_available set quantity = :q where id_product_attribute = :id_product_attribute”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

            $res = $db->prepare(“update psh_product_attribute set quantity = :q where id_product_attribute = :id_product_attribute”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

            $res = $db->prepare(“update psh_stock_available set quantity = quantity + :q where id_product = :id_product AND id_product_attribute = 0”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            $res = $db->prepare(“update psh_product set quantity = quantity + :q where id_product = :id_product”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            } else {

            // IT’S A SIMPLE PRODUCT

            $res4 = $db->prepare(“SELECT id_product from psh_product WHERE reference = :reference”);
            $res4->execute(array(‘:reference’=>$reference));
            if ($res4->rowCount() > 0) {
            $row4 = $res4->fetch();

            $res = $db->prepare(“update psh_stock_available set quantity = :q where id_product = :id_product and id_product_attribute = 0”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

            $res = $db->prepare(“update psh_product set quantity = :q where id_product = :id_product”);
            $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));
            }

            }
            } catch (PDOException $e) {
            echo ‘Sql Error: ‘. $e->getMessage() .”;
            }
            }
            fclose($handle);
            }
            ?>

            Furthermore: when there are more combinations within a product with the same reference, it only updates the first one. It should check every line in the table.

            Reply ↓
            1. Ton Rijsdijk 1 February 2016 at 12:14

               Could someone please look at this once more? It’s essential for my project.
4. Chris 17 July 2015 at 17:01

   Hi,

   I have one question.

   If my CSV supplied by a manufacturer has the refference number in the 3rd column, and the quantity in the 5th column ? How to modify this script ?

   Please help.

   Kind regards
   Chris

   Reply ↓
5. Roberto Alonso 1 October 2015 at 11:06

   This code are for SQL SERVER.
   First, vincule server in SQL SERVER and use

   openquery(TIENDAWEB,’Select id_product, id_product_attribute, reference from TABLENAME’)
   instead TABLENAME

   In SELECT FIELDS, you must include ONLY the fields necesary. If you include ALL, can recive errors by null/zeros values stored in database.

   create procedure SetStockWEBV2(@Reference nvarchar(20), @Stock Numeric(11,2))
   as
   declare @id_product int
   declare @id_product_attribute int

   SELECT @id_product = id_product, @id_product_attribute=id_product_attribute
   from
   openquery(TIENDAWEB,’Select id_product, id_product_attribute, reference from product_attribute’)
   WHERE reference = @reference

   if @@rowcount > 0
   begin
   — IT’S A PRODUCT COMBINATION

   update
   openquery(TIENDAWEB,’Select quantity, id_product_attribute from stock_available’)
   set quantity = @Stock where id_product_attribute = @id_product_attribute

   update
   openquery(TIENDAWEB,’Select quantity, id_product_attribute from product_attribute’)
   set quantity = @Stock where id_product_attribute = @id_product_attribute

   update
   openquery(TIENDAWEB,’Select quantity, id_product, id_product_attribute from stock_available’)
   set quantity = @stock where id_product = @id_product and id_product_attribute = 0

   update
   openquery(TIENDAWEB,’Select quantity, id_product from product’)
   set quantity = @stock where id_product = @id_product
   end
   else
   begin

   — IT’S A SIMPLE PRODUCT

   SELECT id_product from
   openquery(TIENDAWEB,’Select id_product, reference from product’)
   WHERE reference = @reference

   if @@rowcount > 0
   begin

   update
   openquery(TIENDAWEB,’Select id_product, id_product_attribute, quantity from stock_available ‘)
   set quantity = @stock where id_product = @id_product and id_product_attribute = 0

   update
   openquery(TIENDAWEB,’Select id_product, quantity from product’)
   set quantity = @stock where id_product = @id_product
   end
   end

   Reply ↓
6. Thijs 16 October 2015 at 17:12

   Thanks for this solution, it works for me, to some extent.
   It updates the quantities, but it also creates random quantities.
   It adds the quantities for the different product variations, which is fine, but it also multiplies the totals on occasion.
   If I add Gian’s code (4th of April), it does set the stock to zero, but then it won’t update afterwards, but that might be due to time out issues.
   Any suggestions?
   Thanks.

   Thijs

   Reply ↓
7. Sean 29 December 2015 at 20:05

   hello, Ive used a slightly altered version of this script found here; it wipes the stock back to zero but it doesnt then update the stock from the csv file, any ideas what i am doing wrong?

   Reply ↓
8. Miro 12 January 2016 at 11:10

   Hi,
   I have multustore with share quantities. I can not find in which table prestashop store share quantities. In ps_product I have 0s for quantity

   Thanks for the script

   Reply ↓
9. Dixital - Digital Business Solutions 15 May 2016 at 15:06

   Will be more efficient to use static method StockAvailable::set in loop
   Why :
   on combination product method calculate automatically quantity sum in parent product(0)

   Usage :
   QuantityStockAvailable::setQuantity(id_product,id_product_attribute,quantity,$shop=null)

   eg :
   ‘PAN0008000D07000’, ‘quantity’ => 1); //1
   $productStockUpdates[] = array(‘sku’ => ‘PAN0008000D08000’, ‘quantity’ => 1); // 1
   $productStockUpdates[] = array(‘sku’ => ‘VEI0002’, ‘quantity’ => 0); //0
   $productStockUpdates[] = array(‘sku’ => ‘VES0008000M06000’, ‘quantity’ => 0); // 0
   $productStockUpdates[] = array(‘sku’ => ‘VES0008000M03000’, ‘quantity’ => 0); // 0
   $productStockUpdates[] = array(‘sku’ => ‘BO00001000000018’, ‘quantity’ => 0); // 0

   foreach ($productStockUpdates as $productUpdate) {
   //Combination set
   $results = db::getInstance()->executeS(“SELECT id_product, id_product_attribute from ” . _DB_PREFIX_ . “product_attribute WHERE reference = ‘” . $productUpdate[‘sku’] . “‘”);
   // No combinations results / Simple
   if (!db::getInstance()->numRows()) {
   $results = db::getInstance()->executeS(“SELECT id_product FROM ” . _DB_PREFIX_ . “product WHERE reference = ‘” . $productUpdate[‘sku’] . “‘”);
   }

   // Update Stock
   foreach ($results as $product) {
   StockAvailable::setQuantity($product[‘id_product’], $product[‘id_product_attribute’], $productUpdate[‘quantity’]);
   }
   }

   Reply ↓
10. Dixital - Digital Business Solutions 18 May 2016 at 16:19

    my last contribution on this topic 😉

    Complete code, i have replace CSV by JSON.

    You can create a folder on module directory and put below in file

    include(dirname(__FILE__) . ‘/../../config/config.inc.php’);

    if (‘12387539_99_charli_alpha_tango’ != Tools::getValue(‘token’))
    die(‘Bad token’);

    $data = json_decode(file_get_contents(‘globalStock.json’));

    foreach ($data->stock as $item) {

    //get combination’s IDs
    $result = db::getInstance()->executeS(“SELECT id_product, id_product_attribute from ” . _DB_PREFIX_ . “product_attribute WHERE reference = ‘” . $item->sku . “‘”);
    // No combination’s IDs result -> //get simple product’s IDs
    if (!db::getInstance()->numRows()) {
    $result = db::getInstance()->executeS(“SELECT id_product FROM ” . _DB_PREFIX_ . “product WHERE reference = ‘” . $item->sku . “‘”);
    }
    //Update Stock
    if (db::getInstance()->numRows()) {
    foreach ($result as $product) {
    StockAvailable::setQuantity($product[‘id_product’], $product[‘id_product_attribute’], $item->inVirtualStock);
    }
    }
    }

    Reply ↓
11. friskytask 10 June 2016 at 15:24

    Thank you very much!

    Reply ↓
12. NikoBelic 1 August 2016 at 12:17

    Hello to all, very usefull script. As you know the problem of that script is that don’t update correctly the global store quantity. I saw different soulutions but have my own, just with a simple query:

    // IT’S A PRODUCT COMBINATION

    $row4 = $res4->fetch();

    $res = $db->prepare(“update “._DB_PREFIX_.”stock_available set quantity = :q where id_product_attribute = :id_product_attribute”);
    $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

    $res = $db->prepare(“update “._DB_PREFIX_.”product_attribute set quantity = :q where id_product_attribute = :id_product_attribute”);
    $res->execute(array(‘:q’=>$quantity, ‘:id_product_attribute’=>$row4[‘id_product_attribute’]));

    $res = $db->prepare(“update “._DB_PREFIX_.”stock_available set quantity = quantity + :q where id_product = :id_product and id_product_attribute = 0”);
    $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

    $res = $db->prepare(“update “._DB_PREFIX_.”product set quantity = quantity + :q where id_product = :id_product”);
    $res->execute(array(‘:q’=>$quantity, ‘:id_product’=>$row4[‘id_product’]));

    ————————————– REPLACE lines from 47 to 51:

    $res=$db->prepare(“update “._DB_PREFIX_.”stock_available as a inner join (select sum(quantity) as qty from “._DB_PREFIX_.”stock_available where id_product like :id_product and id_product_attribute 0) as b on a.id_product = :id_product and a.id_product_attribute = 0 set a.quantity = b.qty”);
    $res->execute(array(‘:id_product’=>$row4[‘id_product’]));

    ——————————————–

    My last query makes sum of the quantity to all atributes that have different id_product_attribute of 0(this ones are the combinations) and places that sum to the product’s quantity that has id_product_attribute = 0 (simple product or global product).
    That updates only the products that are in CSV file

    Sorry for my Inglish :), hope you enjoy it.

    Reply ↓
    1. NikoBelic 1 August 2016 at 12:24

       i forgot about the “” xD sry, thats the correct one to replace with:

       $res=$db->prepare(“update “._DB_PREFIX_.”stock_available as a inner join (select sum(quantity) as qty from “._DB_PREFIX_.”stock_available where id_product like :id_product and id_product_attribute 0) as b on a.id_product = :id_product and a.id_product_attribute = 0 set a.quantity = b.qty”);
       $res->execute(array(‘:id_product’=>$row4[‘id_product’]));

       Reply ↓
    2. NikoBelic 1 August 2016 at 12:32

       i dont know whats happening but it deletes the “” (different) from my query when i paste it here and leaves it like “id_product_attribute 0”

       deal with it…

       Reply ↓
13. Olysh 30 August 2016 at 07:57

    Hi there,

    Since I have some products variations that have the same reference number but applied to different products.
    I’ve change line 39 from
    $row4 = $res4->fetch();
    to
    While($row4 = $res4->fetch();){
    and added } to line 52.

    Not sure it helps, just wanted to share.

    thanks

    Reply ↓
    1. Ton Rijsdijk 22 December 2017 at 11:57

       this results as a error 500 in the browser.

       Reply ↓
14. Mark Jenkins 19 April 2019 at 16:15

    why is it not possible to just update using the csv import feature why do we have to write scripts , so difficult fo non programmers

    Reply ↓
15. Dean 4 February 2020 at 09:20

    I don’t suppose you would know what changes would be needed to get this working for 1.7?

    Reply ↓
    1. WhileTrue Post author23 February 2020 at 08:49

       Unfortunately I don’t know

       Reply ↓