Importing CSV and numbers losing Zero's

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Importing CSV and numbers losing Zero's

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What type of field are you using to store the data in?

Mac
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

well, i have just noticed i was trying to use double, but it seem to be defaulting to varchar(50)?!!?

Mark
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

okay, change the field type to double and re-imported the dat, but the zeros are still dissapearing???
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Are you importing the data into an existing table or are you creating the table as part of the import?

Mac
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

according to my MySQL book, if you leave out X and Y, they default to 16,4?

Ill try your way.

Mark
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Just tried it to be sure, so it will work.
Have fun.
Post Reply