Is there a way to set the character that "n / a" represents in MySQL?
I am working with a CSV file that uses the period '.' a 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 (version 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 forces the '.' to 0 - Is there a way I can set it to NULL instead?
The only fix I've tried is the following:
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 contains over 10 million rows and 15 columns, where "." used to represent zeros. Is there a cleaner way to default to null?
+3
source to share