Update price CSV from reference, no ID?

[pmpal]

Hi, I had a request to update the prices through the administration (Tools-> Import).

If I have an example of the type of vendor price list:

Reference #;    name;    description;    categories;    price
15-HJ-A;        name1;    des1;          cat1;          100
12-HA-B;        name2;    des2;          cat2;          200
11-HC-D;        name3;    des3;          cat3;          300
…

How can I update prices in the database through Reference #?

[uddhava]

This is not possible with the import tool That one works on ID basis.
You might have to create a clever SQL statement in phpmyadmin and work from there.
Or start using fixed ID's

[pmpal]

Unfortunately when a supplier changes the order/sort of the products or any product deleted so fixed ID update will not work and products are duplicated.

[hasniou]

I did like this
In classes\product.php :

   public static function refInDatabase($ref)
   {
       $sql = 'SELECT id_product FROM '._DB_PREFIX_.'product WHERE reference = \''.$ref.'\'';
       $tab = Db::getInstance()->getValue($sql);
       return $tab;
   }

In AdminImport.php I added before
if ($product->id AND Product::existsInDatabase((int)($product->id)))
this :

if ($product->reference AND Product::refInDatabase($product->reference))
               {                    
                   $product->id = Product::refInDatabase($product->reference); 
               }

my problem is that it removes any fields that are not present in the cvs

someone has an idea ?

[raitiamine]

hi

 

can you upload your modified files here?

 

I do modification but when I import a csv the product is duplicated not updated.

thank you

[fitgura]

Simple use this script as a php file run it on your server, jut add your.csv file to the same folder what contains next:

 

1st column reference

2nd column price

3rd column wholesale price

4th column quantity!

 

 

you can use it as a cron if you want to update your database often!

 

of course modifies your.... details

 

<?php 


mysql_connect("your_hosthost", "your_username", "your_pass") or die(mysql_error()); 
mysql_select_db("your_database") or die(mysql_error()); 


$row = 0; 

$update_table = "ps_product"; 


$handle = fopen("your.csv", "r"); 


while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) { 
   $num = count($data); 
   echo "<p> $num fields to update in line $row: <br /></p>\n"; 
   $row++; 
   for ($c=0; $c < $num; $c++) { 
       if ($c = 1) { 
       $reference = $data[($c - 1)]; 
       echo $reference . " reference Assigned <br />\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 . " Imported to <b>$row</b> in product <b>$reference</b><br />\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 . " Imported to <b>$row</b> in product <b>$reference</b><br />\n"; 
       } 
       if ($c = 4) { 
       $quantity = $data[($c - 1)]; 
       mysql_query("UPDATE $update_table SET quantity='$quantity' WHERE reference='$reference'")  
       or die(mysql_error());  
       echo $product_quantity . " Imported to <b>$row</b> in product <b>$reference</b> <br />\n"; 
       } 

   } 
} 

fclose($handle); 
echo "<h1>Ready.</h1>"; 
?> 

[Josraso]

Hello

Watch this module, I think you may be worth

 

 

http://todoprestashop.com/foro/viewtopic.php?f=15&t=5274&hilit=actualizar+stock