budimir Posted January 9, 2014 Share Posted January 9, 2014 I have a problem with the import of CSV file using code below $q_load = 'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'" INTO TABLE kalkulacija_import_cjenik_stavke FIELDS TERMINATED BY ";" ENCLOSED BY "\'" LINES TERMINATED BY "\r\n" IGNORE 1 LINES (kataloski_broj, naziv, @cijena_EUR, valuta, @cijena_DD, valuta_DD) SET id_cjenika = '.$id_cjenika.' , cijena_EUR = CONVERT(REPLACE(@cijena_EUR, ",", "."), DECIMAL(10,2)) , cijena_DD = CONVERT(REPLACE(@cijena_DD, ",", "."), DECIMAL(10,2)) , godina = '.$godina.''; mysql_query($q_load) or die (mysql_error()); Problem is that cijena_EUR field is imported but some of the rows have value 0 while in the CSV it has a value. This is happening on just some of the rows but I can't find a reason why. All the rows are formated as number and I don't see a reason why it doesn't import a value from CSV but it imports 0??? Any ideas??? Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/ Share on other sites More sharing options...
Ch0cu3r Posted January 9, 2014 Share Posted January 9, 2014 It seems some of the decimals are not being converted correctly here cijena_EUR = CONVERT(REPLACE(@cijena_EUR, ",", "."), DECIMAL(10,2)) , cijena_DD = CONVERT(REPLACE(@cijena_DD, ",", "."), DECIMAL(10,2)) , Can you post some same data? Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/#findComment-1464575 Share on other sites More sharing options...
budimir Posted January 9, 2014 Author Share Posted January 9, 2014 (edited) This is CSV data: 579 38 10-08 GLOVES TECHNICAL 20 8 CLASS 1 12.33 EUR 579 38 10-09 GLOVES TECHNICAL 20 9 CLASS 1 12.33 EUR 579 38 10-10 GLOVES TECHNICAL 20 10 CLASS 1 12.33 EUR 579 23 80-01 TRIMMER LINE KIT SEMI PRO 679.56 EUR This is what is imported 579 38 10-10 GLOVES TECHNICAL 20 10 CLASS 1 0.00 579 38 10-09 GLOVES TECHNICAL 20 9 CLASS 1 0.00 579 38 10-08 GLOVES TECHNICAL 20 8 CLASS 1 0.00 579 23 80-01 TRIMMER LINE KIT SEMI PRO 679.56 Edited January 9, 2014 by budimir Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/#findComment-1464578 Share on other sites More sharing options...
Barand Posted January 9, 2014 Share Posted January 9, 2014 This is CSV data: 579 38 10-08 GLOVES TECHNICAL 20 8 CLASS 1 12.33 EUR 579 38 10-09 GLOVES TECHNICAL 20 9 CLASS 1 12.33 EUR 579 38 10-10 GLOVES TECHNICAL 20 10 CLASS 1 12.33 EUR 579 23 80-01 TRIMMER LINE KIT SEMI PRO 679.56 EUR No field terminators? No commas in value? Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/#findComment-1464581 Share on other sites More sharing options...
budimir Posted January 9, 2014 Author Share Posted January 9, 2014 (edited) Barand, That was copy/paste from CSV file. Nothing dropped during copy/paste. No commas. No field terminators. I don't understand why is working on 90% of rows, but on 10% it doesn't? Edited January 9, 2014 by budimir Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/#findComment-1464582 Share on other sites More sharing options...
Solution budimir Posted January 13, 2014 Author Solution Share Posted January 13, 2014 Found the solution! CSV file needs to be saved as CSV (MS-DOS format) and then is working without any problems... Quote Link to comment https://forums.phpfreaks.com/topic/285236-load-data-in-file/#findComment-1465035 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.