I'm working with a CSV file that uses a period '.' sign to represent a null value. For example,
item weight unit_price 1 5.1 10 2 . 20 3 7.5 30 4 . 40 5 2.0 50
This is read into a table in MySQL (v. 5.5) using :
CREATE TABLE products( item SMALLINT NOT NULL, weight float DEFAULT NULL, unit_price SMALLINT); LOAD DATA INFILE ".../data.txt" IGNORE INTO TABLE products FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n" IGNORE 1 LINES;
The IGNORE keyword in the LOAD DATA block coerces the '.' to a 0 - Is there a way I can set it to NULL instead?
The only fix I've tried is this:
LOAD DATA INFILE ".../data.txt" IGNORE INTO TABLE products FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n" IGNORE 1 LINES (item, @weight, unit_price ) SET weight = if(@weight = '.', NULL, @weight);
However the actual data I'm working with has over 10 million rows and 15 columns where '.' is used to represent nulls. Is there a cleaner way to default to null?