Posted on 2 Comments

LOAD DATA INFILE extended in MySQL 5.0

Have you ever used the LOAD DATA INFILE command, say to load data from a comma separated file into a table, but really needed to also do some transformation or calculation on the data? Of course you can use a temporary table for that, but with MySQL 5.0 there’s a neat direct way:

LOAD DATA INFILE 'file.txt' INTO TABLE t1
   (col1, @var1, @var2, @var3)
  SET col2 = @var1-@var2, col3 = @var2, 
      col4 = LOCATE(@var3, 'apple,banana,pear');

So in essense, the SET style syntax (also used in UPDATE statements, and the alternate form of INSERT statements) was added onto the end of the regular old syntax of LOAD DATA INFILE. Any valid expression can be used there.

For the basic syntax of LOAD DATA INFILE, see this section in the MySQL reference manual: http://dev.mysql.com/doc/mysql/en/load-data.html

Posted on 2 Comments

2 thoughts on “LOAD DATA INFILE extended in MySQL 5.0

  1. Would you happen to have any recommendations on what to do about empty fields. I have a comma deliminated text file that I wa using on mysql 4.1 and importing to the db with the load data infile function. Everything worked as it should. Now with 5.0 I can’t get the Load Data Infile function to read my .txt file because I have empty fields. Mysql use to just read the fields terminated with ‘,’ and skip the field. So if there were 2 commas in a row it new the field was empty. Not it doesn’t seem to get past this. If you have a work around I’d greatly appreciate it.
    Thanks for your time.

  2. Sounds like a bug. Please create a little file and LOAD DATA command line to reproduce it, and report to bugs.mysql.com with output from 4.1 and 5.0.
    Thanks.

Comments are closed.