Update quantity via CSV



  • Through my various dropshipping wholesalers, I’ve been using a separate php script for a few years now.
    I found it at that time after a long search in the PS forum.

    With this script all quantities are emptied during the update and then re-imported. If, for example, items are not listed in the CSV file, they will automatically receive the amount zero (may be irritating if the sub item number variant is not correct).

    It works for products with or without variants, if the article number (sub-article number of the variant is correct).

    Upload the additional script (php) once into the main directory (eg httpdocs). Upload the current CSV file (semicolon separated) to the main directory before each update.

    To update the quantity in the browser, enter the link and wait until it has run through. There is no extra notice after the update, only a white page and the favicon appears in the browser line.

    The link to the update:
    http://yourwebsite.com/update.php

    I myself have not tried to make any changes with the script, since it is sufficient for me so far.

    Personal note:
    for the top line of the CSV file - column 1 = “reference”, column 2 = “quantity”.
    With the renaming of the php file and the CSV file I had no problems so far. It is important only when renaming the CSV file to change this in the php file ($remote_csv_file = ‘update.csv’; //MY PATH; CHANGE TO YOUR NEED, SAME FOR THE FILE NAME).

    update.php

    <?php
     
    // PRESTASHOP SETTINGS FILE
    require_once ('config/settings.inc.php');
     
    // REMOTE CSV FILE (CUSTOMIZE YOURCSVFILEPATH, CAN BE AN URL OR A LOCAL PATH)
    $remote_csv_file = 'update.csv'; //MY PATH; CHANGE TO YOUR NEED, SAME FOR THE FILE NAME
     
    // DB CONNECTION (CUSTOMIZE YOURDBHOSTNAME AND YOURDBPORT)
    $db = new PDO("mysql:host=localhost;port=3306;dbname="._DB_NAME_."", _DB_USER_, _DB_PASSWD_);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    set_time_limit(600);
     
    // RESET CYCLE
    
    //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
     
    // 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 "._DB_PREFIX_."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 "._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']));
     
          } else {
     
            // IT'S A SIMPLE PRODUCT
            
            $res4 = $db->prepare("SELECT id_product from "._DB_PREFIX_."product WHERE reference = :reference");
            $res4->execute(array(':reference'=>$reference));
            if ($res4->rowCount() > 0) {
              $row4 = $res4->fetch();
        
              $res = $db->prepare("update "._DB_PREFIX_."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 "._DB_PREFIX_."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() .'<br /><br />';
        }
      }
      fclose($handle);
    }
    ?>
    

    example
    update.csv

    reference;quantity
    39034;43
    39035;39
    43591-52;4
    43591-54;7
    43591-56;4
    43591-58;8
    43591-60;6
    43591-62;9
    38490;38
    37941;36
    45399;17
    45411;17
    44998;16
    34897;16
    34896;9
    40247;9
    40717;9
    43275;9
    46685-50;2
    47314;1
    43100;1
    23667;1
    25992;0
    48971-54;0
    48971-56;0
    48971-58;18
    48971-60;0
    48971-62;0
    48971-64;0
    48971-66;0
    

    Unfortunately, I no longer have permission to upload files (except images). Hence these variants.



  • @zimmer-media said in Update quantity via CSV:

    With this script all quantities are emptied during the update and then re-imported. If, for example, items are not listed in the CSV file, they will automatically receive the amount zero (may be irritating if the sub item number variant is not correct).

    in this way, if the script fails all the quantities are set to zero?! right?



  • I do not know that. I suspect yes. This has not happened in the years of use with us.



  • Addendum, as it is already a kind of habit for me:
    For this script, the “max_execution_time” should be increased many times depending on the data volume of the lines etc in the php settings.
    In my Plesk servers, the default number is 30. To be on the safe side, I always entered 90,000 in advance, if it took a few minutes longer.


 

Looks like your connection to thirty bees forum was lost, please wait while we try to reconnect.