load data in file

[budimir]

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???

[Ch0cu3r]

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?

[budimir]

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

[Barand]

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?

[budimir]

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

[budimir]

Found the solution!

 

CSV file needs to be saved as CSV (MS-DOS format) and then is working without any problems...