Bug #1263 LOAD DATA INFILE
Submitted: 12 Sep 2003 8:37 Modified: 12 Sep 2003 13:36
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version: OS:
Assigned to: CPU Architecture:Any

[12 Sep 2003 8:37] [ name withheld ]
Description:
I like what is coming for the new LOAD DATA INFILE...

Make LOAD DATA INFILE understand syntax like:

LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name
     TEXT_FIELDS (text_field1, text_field2, text_field3)
     SET table_field1=CONCAT(text_field1, text_field2),
         table_field3=23
     IGNORE text_field3

I would just add the ability to set column fields for the table that are not in the 'file_name.txt'.   For example, I have a comma delimited file of data that does not include a date in one of the columns.  Instead of regenerating the file with dates appended to the front, it would be much nicer to just specify table_field1="10-sep-03" and let the rest of the fields load as normal.

This option would really rock and I hope you can do it soon!

How to repeat:
I like what is coming for the new LOAD DATA INFILE...

Make LOAD DATA INFILE understand syntax like:

LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name
     TEXT_FIELDS (text_field1, text_field2, text_field3)
     SET table_field1=CONCAT(text_field1, text_field2),
         table_field3=23
     IGNORE text_field3

I would just add the ability to set column fields for the table that are not in the 'file_name.txt'.   For example, I have a comma delimited file of data that does not include a date in one of the columns.  Instead of regenerating the file with dates appended to the front, it would be much nicer to just specify table_field1="10-sep-03" and let the rest of the fields load as normal.

This option would really rock and I hope you can do it soon!
[12 Sep 2003 13:36] Dmitry Lenev
Thank you for your feedback! 

We have similar feature in our ToDo for version 4.1.

Note that you can achieve behavior that you have described with existing LOAD DATA. If you specify "10-sep-03" as default value for table_field1 column 
and then do

LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name (table_field2, table_field3)

You will have table_field2 and table_field3 loaded from file and table_field1 set to default value ("10-sep-03").

Of course there is no way now to do that thing with CONCAT, which you have described (using only LOAD DATA).