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


1 answer


use find and replace tool in csv to take out. and replace with "" (nothing) and then when you load it, it will automatically be NULL for that row under the weight column



0


source







All Articles