Mysql – Dealing with empty strings while loading a table from a CSV

csvMySQLmysql-5.5

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

1,2,,4

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

1,2,\N,4

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?

Best Answer

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.

Related Question