Page 1 of 1

Importing CSV and numbers losing Zero's

Posted: Mon Sep 15, 2003 4:52 am
by JayBird
I have a CSV files with a column of prices e.g.

1.20
1.30
1.40
1.50

but when i import them into my MySQL database using SQLyog, the figures now look like this

1.2
1.3
1.4
1.5

How do i stop this from happening?

Mark

Posted: Mon Sep 15, 2003 4:52 am
by twigletmac
What type of field are you using to store the data in?

Mac

Posted: Mon Sep 15, 2003 4:59 am
by JayBird
well, i have just noticed i was trying to use double, but it seem to be defaulting to varchar(50)?!!?

Mark

Posted: Mon Sep 15, 2003 5:06 am
by JayBird
okay, change the field type to double and re-imported the dat, but the zeros are still dissapearing???

Posted: Mon Sep 15, 2003 5:07 am
by twigletmac
Are you importing the data into an existing table or are you creating the table as part of the import?

Mac

Posted: Mon Sep 15, 2003 5:13 am
by JayBird
its being dropped and recreated

Code: Select all

create table `pricelist`.`generics` ( 
 	`code` varchar (50)   NULL ,
	`product` varchar (50)   NULL ,
	`quantity` varchar (10)   NULL ,
	`a` double   NULL ,
	`b` double   NULL ,
	`d` double   NULL   
);
Thats the code that is being used to generate the table

Mark

Posted: Mon Sep 15, 2003 5:17 am
by JAM
When you create your field you need to use :

Code: Select all

DOUBLE (X,Y)
where X is int, Y is amount of decimals.
If you set Y to 2, your loosing zeros problem will be solved.

Posted: Mon Sep 15, 2003 5:23 am
by JayBird
according to my MySQL book, if you leave out X and Y, they default to 16,4?

Ill try your way.

Mark

Posted: Mon Sep 15, 2003 5:55 am
by JAM
Just tried it to be sure, so it will work.
Have fun.