I have a csv file where some integer fields are blank (e.g. , ,) or have an NA value (e.g. ,NA ,). When I load the data (LOAD DATA LOCAL INFILE...) into a table MySQL correctly warns that the fields have incorrect integer values, however it inserts a 0, not the DEFAULT value specified in the CREATE TABLE DDL, into the columns. Is there any way I can specify the value to use in case of incorrect values in a data file?
When you need to 'cleanse' the incoming data, there are two techniques to consider. Suppose the column
foo needs to be
-1 to represent
LOAD into and
@variable, then use
SET to manipulate the value while storing it. There are examples in the docs. In your case you might use something like
SET foo := IF(@foo = 'NA', -1, @foo) inside the
LOAD DATA statement.
Using a temp table...
LOADing directly into the target table, load into a separate table (possible
TEMPORARY). Then have a few SQL statements to manipulate the data in that table before copying to the "real" table. In your case:
UPDATE tmp SET foo = -1 WHERE foo = 'NA';. Then
INSERT INTO real SELECT * FROM tmp;. Note
foo would be
tmp in spite of being
The temp table approach can handle more complex transformations.