Hi,
This is my database structure.
CREATE TABLE IF NOT EXISTS condo (
id int(6) NOT NULL auto_increment,
date date NOT NULL default ‘0000-00-00’,
location varchar(100) NOT NULL,
property_type varchar(100) NOT NULL,
tenure varchar(50) NOT NULL,
status varchar(50) NOT NULL,
sale_rent varchar(50) NOT NULL,
size decimal(6,2) default ‘0.00’,
price decimal(8,2) default ‘0.00’,
price_psf decimal(8,2) default ‘0.00’,
rooms varchar(5) NOT NULL,
baths varchar(5) NOT NULL,
facilities varchar(150) NOT NULL,
renovations varchar(150) NOT NULL,
furnishings varchar(150) NOT NULL,
phone_1 varchar(20) NOT NULL,
advertiser varchar(50) NOT NULL,
e_num varchar(20) NOT NULL,
unique varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique (unique)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30390 ;
My problem lies with the size, price, price_psf fields. I want to perform some calculations on these fields. However, when I import numbers into these fields, mysql converts it into a different format. The data is being imported from a tab delimited text file. For example:-
Original Raw Data Before Import
(size)90017 (price)30,000.00 (price_psf)0.33
Data After Import
(size)9999.99 (price)30.00 (price_psf)0.33
I have no idea how 90017 got converted to 9999.99. I suppose that 30,000.00 got converted to 30.00 due to the comma.
Therefore, how do I modify the table structure of to prevent these conversions? How do I tell mysql to ignore the commas in these fields when importing raw data?
Thanks for your advice.
