MySQL load NULL values from CSV data

by

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,'')
;

More Related Contents:

  1. How do I import CSV file into a MySQL table?
  2. Import data in MySQL from a CSV file using LOAD DATA INFILE
  3. How to import a CSV file into a MySQL table
  4. Importing a csv into mysql via command line
  5. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  6. Import CSV to MySQL
  7. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  8. SQL split values to multiple rows
  9. How can I output MySQL query results in CSV format?
  10. What is the opposite of GROUP_CONCAT in MySQL?
  11. MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
  12. MySQL: Split comma separated list into multiple rows
  13. Mysql string split [duplicate]
  14. Dump all tables in CSV format using ‘mysqldump’
  15. Importing a CSV to MySQL with different date format
  16. mysql pivot query results with GROUP BY
  17. How to export data from SQL Server 2005 to MySQL [closed]
  18. Dump a mysql database to a plaintext (CSV) backup from the command line
  19. access denied for load data infile in MySQL
  20. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  21. How can I correct MySQL Load Error
  22. Mysql permission errors with ‘load data’
  23. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  24. MySQL load data infile – acceleration?
  25. How to join two tables using a comma-separated-list in the join field
  26. Import CSV to Update only one column in table
  27. How I can Import data from CSV to MySQL?
  28. Import CSV to Update rows in table
  29. How to import a csv file into MySQL workbench?
  30. MySQL Error 1215: Cannot add foreign key constraint

Leave a Comment