I am trying to load a CSV file into a table. The CSV file contains some empty strings for example:


I want to load the empty field as null. One way I know is to use \N i.e:


but it won't suit my condition because my files are large and I don't want to change the original data.

Is there a way that I can specify in the Load command itself that wherever it finds an empty string, it should convert it to a NULL?

1 Answer 1


According to the documentation, you can use SET statements to transform the data on the way in.

 [SET col_name = expr,...]

The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:

SET latitude = IF(latitude + 0 = 0,NULL,latitude),
    area_code = IF(area_code = '',NULL,area_code)

This example transforms 2 columns. If latitude + 0 is 0, latitude gets set to NULL, and otherwise it gets set to the value from the file as the data is inserted; if area_code contains an empty string, it gets set to NULL, otherwise to the data from the file. The more appropriate choice will depend on how MySQL handles casting the data, but I suspect either of these constructs would work in your situation.

You do not have to reference columns you don't intend to transform. They'll be inserted as-is.

  • 1
    Hi Michael, Thanks for your help. What if I don't want to look into the data or if i don't know which field is having problems. Do we have a way in MySql to handle it command line as we have on oracle (sqlldr ctl file concept). May 14, 2013 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.