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 :
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.