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

up vote 9 down vote accepted

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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