Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

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.

share|improve this answer
    
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). –  Hitesh Chouhan May 14 '13 at 9:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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