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?
MySQL – Specify Default Value for Incorrect Data
MySQL
Related Question
- Mysql – Inserting empty string on Windows MySQL doesn’t use default value
- MySQL Import – Fixing CSV Import Errors with Empty Fields
- MySQL – Include Default Value in LOAD XML INFILE Statement
- Mysql – Load csv file to thesql using load data local infile
- MySQL – Blank fields in tab-delimited .txt file causing errors in LOAD DATA INFILE
- Mysql – How to prevent the creation of Mysql columns with default values that are not defined
Best Answer
When you need to 'cleanse' the incoming data, there are two techniques to consider. Suppose the column
fooneeds to be-1to represent"NA":Using
@variables...LOADinto and@variable, then useSETto manipulate the value while storing it. There are examples in the docs. In your case you might use something likeSET foo := IF(@foo = 'NA', -1, @foo)inside theLOAD DATAstatement.Using a temp table...
Instead of
LOADingdirectly into the target table, load into a separate table (possibleTEMPORARY). 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';. ThenINSERT INTO real SELECT * FROM tmp;. Notefoowould beVARCHARintmpin spite of beingINTinreal.The temp table approach can handle more complex transformations.