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

mysql csv mysqlimport
Atma picture Atma · Jul 19, 2017 · Viewed 7.5k times · Source

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

Answer

Shadow picture Shadow · Jul 19, 2017

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...

Related questions

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | …

Read More
How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column …

Read More
MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD …

Read More