Load data infile messing up the numbers

[budimir]

I'm using the code below to load data from CSV to mysql, but the numbers imported are getting all messed up. I have mysql fields formated as decimal (10,2). Also, tried real, double, float, but nothing is helping.

 

For example, line I'm importing from CSV is like this:

967 54 71-66 872,64 1.679,20

2.099

Data I get in mysql is like this:

967 54 71-66 87264.00 1.68 kn 0.00 kn 0.00 kn 0.00 kn

 

How can I format the numbers to get them in mysql in exact shape like they are in CSV file?

 

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = '.$id_cjenika.' , 
				godina_pov = '.$godina.' ,
				prosjecna_nc = REPLACE (@prosjecna_nc, "," , "") ,
				vpc = REPLACE(@vpc, "," , "") ,
				mpc = REPLACE(@mps, "," , "") ,
				pvpc = REPLACE(@pvpc, "," , "") ,
				pmpc = REPLACE(@pmpc, "," , "")
				';

 

[Barand]

 

For example, line I'm importing from CSV is like this:

967 54 71-66 872,64 1.679,20

2.099

 

 

FIELDS TERMINATED BY ";"

 

How do you reconcile those two statements? There's no ; in the csv data.

[budimir]

Barand,

 

I'm using the same code on some other CSV files and is working properly, but on this CSV where I have different number formating is not working. The code is working, but number formating is not good. I'm getting article number and one price column in, but the second column is getting strange number and after that nothing.

 

I don't know why number formating is wrong? Do you have any suggestions how could I test what is failing?

[Barand]

if you post the output from

 

 

SHOW CREATE TABLE kalkulacija_import_povijest_stavke

 

and some sample CSV data I'll have a look at you problem

[budimir]

Barand,

 

I don't get any result from "SHOW CREATE TABLE kalkulacija_import_povijest_stavke". I have attached a CSV I'm trying to import so you can take a look and maybe see what is the problem...

NC i PC 2009.zip

Edited March 6, 2013 by budimir

[Barand]

SHOW CREATE TABLE xxxx

 

should give something like this example

 

CREATE TABLE `xxxx` (
  `locId` int(10) unsigned NOT NULL,
  `country` char(2) COLLATE latin1_general_ci NOT NULL,
  `region` char(2) COLLATE latin1_general_ci NOT NULL,
  `city` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `postalCode` char(5) COLLATE latin1_general_ci NOT NULL,
  `latitude` float DEFAULT NULL,
  `longitude` float DEFAULT NULL,
  `dmaCode` int(11) DEFAULT NULL,
  `areaCode` int(11) DEFAULT NULL,
  PRIMARY KEY (`locId`),
  KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

Edited March 6, 2013 by Barand

[budimir]

Here it is.

 

CREATE TABLE `kalkulacija_import_povijest_stavke` (  
`id` int(255) NOT NULL AUTO_INCREMENT,  
`id_povijest` int(255) NOT NULL,  
`vrijeme` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,  
`godina_pov` year(4) NOT NULL,  
`kataloski_broj` varchar(255) NOT NULL,  
`prosjecna_nc` decimal(10,2) NOT NULL,  
`vpc` decimal(10,2) NOT NULL,  
`mpc` decimal(10,2) NOT NULL,  
`pvpc` decimal(10,2) NOT NULL,  
`pmpc` decimal(10,2) NOT NULL,  
PRIMARY KEY (`id`)) 
ENGINE=MyISAM 
AUTO_INCREMENT=47838 
DEFAULT CHARSET=latin1

[budimir]

I tried to format cells in CSV as text instead as number and than is importing OK. The problem there is I don't get decimal, only a round number.

 

So I guess it is a problem with number formating, but I don't know how to deal with it...

[budimir]

I'm completly lost now... Tried some conversions with decimal holders changing from . to , and other way. No success. Numbers which don't have comma in it are imported properly, but numbers that have comma in it get rounded to a very small number like 1.678,99 gets 1.68???

 

I don't get it. Why is it doing that?

[Barand]

This CONVERT function seems to work

 

 

SELECT CONVERT(REPLACE(REPLACE('12.345,67', '.', ''), ',', '.'), DECIMAL(10,2))

 

--> 12345.67

[budimir]

I can't implement it in the code below. It displays error in mysql syntax. Can you show me how to use that function in this code?

 

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r\n"
				IGNORE 1 LINES
				(kataloski_broj, prosjecna_nc, vpc, mpc, pvpc, pmpc)
				SET id_povijest = '.$id_cjenika.' , godina_pov = '.$godina.'';			
				
	mysql_query($q_load) or die (mysql_error());

[budimir]

This is the code I use, but it doesn't work. It inserts 0 to fileds.

 

	$q_load =   "LOAD DATA LOCAL INFILE 'upload/".$_FILES['filename']['name']."'
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY ''
				LINES TERMINATED BY '\r\n'
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = ".$id_cjenika." , 
				godina_pov = ".$godina." , 
				prosjecna_nc = CONVERT(REPLACE(REPLACE('@prosjecna_nc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				vpc = CONVERT(REPLACE(REPLACE('@vpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				mpc = CONVERT(REPLACE(REPLACE('@mpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pvpc = CONVERT(REPLACE(REPLACE('@pvpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pmpc = CONVERT(REPLACE(REPLACE('@pmpc', '.', ''), ',', '.'), DECIMAL(10,2))";			
				
	mysql_query($q_load) or die (mysql_error());

[budimir]

Barand,

 

I got it. Tried to experiment a bit, and this is the code that is working for me.

 

	$q_load =   "LOAD DATA LOCAL INFILE 'upload/".$_FILES['filename']['name']."'
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY ''
				LINES TERMINATED BY '\r\n'
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = ".$id_cjenika." , 
				godina_pov = ".$godina." , 
				prosjecna_nc = CONVERT(REPLACE(REPLACE(@prosjecna_nc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				vpc = CONVERT(REPLACE(REPLACE(@vpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				mpc = CONVERT(REPLACE(REPLACE(@mpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pvpc = CONVERT(REPLACE(REPLACE(@pvpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pmpc = CONVERT(REPLACE(REPLACE(@pmpc, '.', ''), ',', '.'), DECIMAL(10,2))";			
				
	mysql_query($q_load) or die (mysql_error());

Thank you very much for you're help!!! You really are saving my life...