Search code examples
mysqlcsvmysqlimport

How to import a csv file into a mysql table and auto increment

I'm trying to import a csv file into mysql with the following command:

mysqlimport --columns=name,amount,desc --ignore-lines=1 --fields-terminated-by=, --verbose --local -u muser -p mydb file.csv

The file contains fields but not a primary key. It looks like this:

name, amount, desc

My mysql table looks like this:

CREATE TABLE `organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `amount` varchar(128) DEFAULT NULL,
  `desc` varchar(128) DEFAULT NULL,

How can I use mysqlimport in order to import the csv file and generate the auto-incremented Ids?

When I run this, I get the following error:

mysqlimport: Error: 1467, Failed to read auto-increment value from storage engine, when using table: organization

Solution

  • Use the --columns parameter to specify mapping of the fields. The order of the column names indicates how to match data file columns with table columns.

    mysqlimport --columns=name,amount,desc...