MySQL – Using LOAD INFILE to Populate Certain Columns

csvMySQL

I have a csv file with a lot of data in it and I want to upload it into a mysql table. However, I would like to keep one column (primary id) empty so I can edit it myself.
In mysql's documentation it says that you can specify columns, but from my research it seems that you can only specify the columns of the csv file.

So is it possible to specify the columns of the table

Best Answer

Check this example:

# cat /tmp/db.txt
id,col1,col2
1,23,"dsafsdf"
2,-1,"ghfdhg"
7,9,"strsdrt"

# mysql test
mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL,
       `mycol1` int(11) DEFAULT NULL,
       `mycol2` varchar(20) DEFAULT NULL,
       PRIMARY KEY (`id`);

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, mycol1, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     23 | dsafsdf |
|  2 |     -1 | ghfdhg  |
|  7 |      9 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

If you want to specify certain columns:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, @ignore, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |   NULL | dsafsdf |
|  2 |   NULL | ghfdhg  |
|  7 |   NULL | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

And you can even set them with your own expressions:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY00 ','
       OPTIONALLY ENCLOSED by '"' IGNORE 1 LINES (id, @var1, mycol2)
       SET mycol1 = @var1 + 1;

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     24 | dsafsdf |
|  2 |      0 | ghfdhg  |
|  7 |     10 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

However, please understand that a primary key cannot be null nor duplicated, so you cannot set it to NULL unless it gets a default value, for example, because it is an auto_increment column.

Related Question