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?
2
-
possible duplicate of load data infile default value not inserting in table; see also this and thismarkp-fuso– markp-fuso2017-09-28 13:41:02 +00:00Commented Sep 28, 2017 at 13:41
-
I don't see this as a duplicate. My table has default values in the columns but the defaults are not being used in cases where the data file has incorrect integer values.Lee– Lee2017-09-28 14:58:03 +00:00Commented Sep 28, 2017 at 14:58
Add a comment
|
1 Answer
When you need to 'cleanse' the incoming data, there are two techniques to consider. Suppose the column foo needs to be -1 to represent "NA":
Using @variables...
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...
Instead of 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 VARCHAR in tmp in spite of being INT in real.
The temp table approach can handle more complex transformations.
-
Two excellent suggestions, thank you. It would be nice if there were an option to LOAD/INSERT to use a column's default value in case of incorrect data.Lee– Lee2017-09-28 18:08:28 +00:00Commented Sep 28, 2017 at 18:08
-
bugs.mysql.com accepts "feature requests".Rick James– Rick James2017-09-28 23:26:40 +00:00Commented Sep 28, 2017 at 23:26