Geek Answers Handbook

Mysql table update from CSV file

Hi, I am trying to update the testprod table in my MYSQL db from csv file. (I found the code here and updated it to my needs, well almost :))

It is updated, but the decimal value is truncated and loses information after (.) For example, 400.25 is truncated to 400

Here is an example csv file

'Product_ID','Model','HighPic','ManuId','Model_Name','categories_id','categories_image','parent_id','sort_order','categories_name','categories_description','categories_keywords','Name','Image','Price','Supplier','Weight','Stock','datetime' 2055332,,,,,,,,,,,,,,800.4,,,'1', 3916211,,,,,,,,,,,,,,444,,,'15', 12073922,,,,,,,,,,,,,,737.6215,,,'0', 4593772,,,,,,,,,,,,,,2822.4,,,'4', 1684786,,,,,,,,,,,,,,4333.2,,,'1',

and here is the PHP code.

 <?php // set local variables $connect = mysql_connect("localhost","db","password") or die('Could not connect: ' . mysql_error()); $handle = fopen("dailyupdates.csv", "r"); // connect to mysql and select database or exit mysql_select_db("rapido_creloaded", $connect); // loop content of csv file, using comma as delimiter while (($data = fgetcsv($handle, 1000, ",")) !== false) { $Product_ID = $data[0]; $price = $data[14]; $stock = $data[17]; $query = 'SELECT Product_ID FROM testprod'; if (!$result = mysql_query($query)) { continue; } if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { // entry exists update $query = "UPDATE testprod SET price ='$price' , stock = '$stock' WHERE Product_ID = '$Product_ID'"; mysql_query($query); if (mysql_affected_rows() <= 0) { // no rows where affected by update query } } else { // entry doesn't exist continue or insert... } mysql_free_result($result); } fclose($handle); mysql_close($connect); ?>
+4
source share
2 answers

Set your data type to DECIMAL(20,5) OR DOUBLE(20,5)

Edit

Use this query

 $query = "UPDATE testprod SET price ='$price' , stock = $stock WHERE Product_ID = '$Product_ID'";
+4
source

Not sure if you're on a Mac, but I'm using SequelPro. Check them out at http://www.sequelpro.com/ . There are several SQL management tools, but for ease of use and cost these guys are the best. I just used it to export the table to csv. I changed the entire column to about 25,000 records and then imported it back into the original table. Try this on your test db and see if it works for you.

Best of luck MKW

0
source

More articles:


All Articles