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.