MySQL full LOAD DATA INFILE example

Here is a more detailed example of LOAD DATA INFILE syntax:

First of all create a table to be loaded:

mysql> create table 2beloaded (field_1 char(1), field_2 int, field_3 char(1), fi
eld_4 varchar(50));
Query OK, 0 rows affected (0.15 sec)


Then the text file:

c, 1, a
b, 2, d

(named 2beloaded.txt and placed in c:\, so it's a file created on Windows platform!!)

Now the query to load it, we want to load the file setting proper line termination and all, but also to load field_4 of the table with the file name, here it is:

mysql> load data infile 'c:\\2beloaded.txt' into table 2beloaded fields terminat
ed by ',' optionally enclosed by '"' lines terminated by '\r\n' (field_1, field_2, field_3)
set field_4 = '2beloaded.txt';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Note that the file name is properly escaped, that the line is terminated by the usual windows sequence, that we are telling the server to load the first 3 fields of the table with data coming from the file and to load field_4 with a value provided by us (the file name).
Now check the result:

mysql> select * from 2beloaded;
+---------+---------+---------+---------------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------------+
| c | 1 | a | 2beloaded.txt |
| b | 2 | d | 2beloaded.txt |
+---------+---------+---------+---------------+
2 rows in set (0.01 sec)

Another example, with a file that has values separated by a space:

mysql> load data infile 'd:/file.txt' into table target
-> fields terminated by ' ' lines terminated by '\r\n'
-> (status, value)
-> set date_time = now();
Query OK, 3 rows affected (0.05 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from target;
+---------------------+--------+-------+
| date_time | status | value |
+---------------------+--------+-------+
| 2006-08-18 16:56:58 | a | 1 |
| 2006-08-18 16:56:58 | b | 2 |
| 2006-08-18 16:56:58 | c | 3 |
+---------------------+--------+-------+
3 rows in set (0.00 sec)

mysql>