MySQL load NULL values from CSV data

Question

I have a file that can contain from 3 to 4 columns of numerical values which are separated by comma  Empty fields are defined with the exception when they are at the end of the row   1 2 3 4 5 1 2 3  5 1 2 3   The following table was created in MySQL     ------- -------- ------ ----- --------- -------    Field   Type     Null   Key   Default   Extra    ------- -------- ------ ----- --------- -------    one     int 1    YES          NULL                 two     int 1    YES          NULL                 three   int 1    YES          NULL                 four    int 1    YES          NULL                 five    int 1    YES          NULL                ------- -------- ------ ----- --------- -------    I am trying to load the data using MySQL LOAD command   LOAD DATA INFILE   tmp testdata txt  INTO TABLE moo FIELDS  TERMINATED BY     LINES TERMINATED BY   n     The resulting table     ------ ------ ------- ------ ------    one    two    three   four   five    ------ ------ ------- ------ ------       1      2       3      4      5         1      2       3      0      5         1      2       3   NULL   NULL     ------ ------ ------- ------ ------    The problem lies with the fact that when a field is empty in the raw data and is not defined  MySQL for some reason does not use the columns default value  which is NULL  and uses zero  NULL is used correctly when the field is missing alltogether   Unfortunately  I have to be able to distinguish between NULL and 0 at this stage so any help would be appreciated   Thanks S   edit  The output of SHOW WARNINGS     --------- ------ --------------------------------------------------------    Level     Code   Message                                                   --------- ------ --------------------------------------------------------    Warning   1366   Incorrect integer value     for column  four  at row 2      Warning   1261   Row 3 doesn t contain data for all columns                  Warning   1261   Row 3 doesn t contain data for all columns                 --------- ------ --------------------------------------------------------

User · Answer

The behaviour is different depending upon the database configuration  In the strict mode this would throw an error else a warning  Following query may be used for identifying the database configuration   mysql gt  show variables like  sql mode

User · Answer

MySQL manual says      When reading data with LOAD DATA   INFILE  empty or missing columns are   updated with     If you want a NULL    value in a column  you should use  N   in the data file  The literal word      NULL    may also be used under some   circumstances    So you need to replace the blanks with  N like this   1 2 3 4 5 1 2 3  N 5 1 2 3

User · Answer

This will do what you want  It reads the fourth field into a local variable  and then sets the actual field value to NULL  if the local variable ends up containing an empty string   LOAD DATA INFILE   tmp testdata txt  INTO TABLE moo FIELDS TERMINATED BY     LINES TERMINATED BY   n   one  two  three   vfour  five  SET four   NULLIF  vfour         If they re all possibly empty  then you d read them all into variables and have multiple SET statements  like this   LOAD DATA INFILE   tmp testdata txt  INTO TABLE moo FIELDS TERMINATED BY     LINES TERMINATED BY   n    vone   vtwo   vthree   vfour   vfive  SET one   NULLIF  vone      two   NULLIF  vtwo      three   NULLIF  vthree      four   NULLIF  vfour

User · Answer

variable1   variable2      SET variable2   nullif  variable2     or         you can put any condition

User · Answer

Converted the input file to include  N for the blank column data using the below sed command in UNix terminal  sed -i  s       N  g   file name and then use LOAD DATA INFILE command to load to mysql

User · Answer

show variables  Show variables like   secure file priv     Note  keep your csv file in location given by the above command   create table assessments  course code varchar 5  batch code varchar 7  id assessment int  assessment type varchar 10   date int   weight int     Note  here the  date  column has some blank values in the csv file   LOAD DATA INFILE  C  ProgramData MySQL MySQL Server 8 0 Uploads assessments csv   INTO TABLE assessments FIELDS TERMINATED BY      OPTIONALLY ENCLOSED BY     LINES TERMINATED BY   n   IGNORE 1 ROWS   course code batch code id assessment assessment type  date weight  SET date   IF  date       NULL   date

User · Answer

Preprocess your input CSV to replace blank entries with  N   Attempt at a regex  s      n  g and s      N g  Good luck

[mysql] MySQL load NULL values from CSV data

Examples related to mysql

Examples related to csv

Examples related to load-data-infile