How to update Prestashop product quantities from a csv file

Prestashop is a good e-commerce software, but sometimes is difficult to find free modules or easy solutions to common needs.

I’ve implemented a simple script for Prestashop (>=1.6) , to update product quantities from an external csv file. The script is able to update simple products and product combinations.

Sample CSV File

The sample csv file is structured as follows:

  • first row: headers (reference, quantity)
  • other rows: (1) product reference name, (2) product quantity

Sample content:

Script code

Disclaimer

The script is provided “as is”, I take no responsibility about data loss while using it on a production site. Customize it to suit your needs, apply the input sanitization that best fits your data and take a full database backup BEFORE using it in production.

If you plan to combine it with a cron job for scheduled runs, consider adjusting the PHP script timeout limits.

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

  1. Javier

    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 author

      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

    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

    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

      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 author

        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

          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
  4. Chris

    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

    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

    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. Miro

    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
  8. Dixital - Digital Business Solutions

    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
  9. Dixital - Digital Business Solutions

    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
  10. NikoBelic

    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

      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

      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
  11. Olysh

    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

Leave a Reply

Your email address will not be published. Required fields are marked *