How to import CSV with 1 column file to DB table with 2 columns?

Question

I have this table:

CREATE TABLE `search`.`location` (
                 `id` INT NOT NULL AUTO_INCREMENT ,
                 `location` VARCHAR CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
                  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;

I have a CSV file with one column with some cities:

New York
Boston
Las Vegas
..

When I try to import that file to the table I get this error:

Invalid column count in CSV input on line 1.

I think that's because I have 2 columns in the table (id , location) , And just one column in the CSV file , The ID is auto incremented.

How to import the CSV to that table with 2 columns one of them is auto incremented?

@GordonLinoff , I don't use SQL to import , I'm using the manual import method — jack, Mar 20 '18 at 18:49
@JNevill , From phpmyadmin there is a section for importing files — jack, Mar 20 '18 at 19:27
Possible duplicate of [How to import text file to table with primary key as auto-increment](https://stackoverflow.com/questions/2463542/how-to-import-text-file-to-table-with-primary-key-as-auto-increment) — JNevill, Mar 20 '18 at 19:31
@JNevill That question covers the same problem but focuses on using phpmyadmin to solve it. If you read between the lines you could probably work out how the answers would work for LOAD DATA INFILE but I think you'd have to know the syntax already to see how it translates. — Paul Campbell, Mar 20 '18 at 23:27

Paul Campbell · Answer 1 · 2018-03-20T23:19:24.993

You can define a column list to specify the column(s) that the csv contains and (optionally) SET any other column to a value outside of your csv file.

Add a column list to the end of your LOAD DATA INFILE statement to define the column location the data you have should load into. You can optionally set id to NULL to auto_increment the value but it will do this anyway.

(`location`)
SET `id` = NULL;

Here's a worked example:

mysql> CREATE TABLE `location` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `location` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`)
);

mysql> LOAD DATA INFILE '/var/lib/mysql-files/idtest.csv' 
  INTO TABLE`location` 
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY '\n' 
  (location) 
  SET id = NULL;
Query OK, 4 rows affected (0.17 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from `location`;
+----+----------+
| id | location |
+----+----------+
|  1 | "London" |
|  2 | "Paris"  |
|  3 | "Rome"   |
|  4 | "Berlin" |
+----+----------+
4 rows in set (0.00 sec)

Documentation here

How to import CSV with 1 column file to DB table with 2 columns?

1 Answers1