importing a csv file into mysql from the command line using mysqlimport command

Question

How to import a csv file into mysql from the command line using mysqlimport command, so thats without going into mysql.?

I am trying to use this answer here

to import a csv file into mysql from the command line

root@678cf3cd1587:/home# mysqlimport --columns='head -n 1 discounts.csv' --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home#

But I cannot get it to work can anyone advise what I am doing wrong?

this is what my file looks like

root@678cf3cd1587:/var/lib/mysql-files# cat discounts.csv
id,title,expired_date,amount
1,"1Spring Break ",20140401,20
2,"2Spring Break ",20140401,20
3,"3Spring Break ",20140401,20
4,"3Spring Break ",20140401,20
5,"3Spring Break ",20140401,20
6,"3Spring Break ",20140401,20
7,"3Spring Break ",20140401,20

And this is what my mysql looks like:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| temp               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use temp
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

NOTE I am running mysql in a docker container which I can give more details on if required There is duplicates of this question but I think mine is specific.

EDIT1

I had tried the different single-quote ' double-quote " and backtick `. although the back tick gave a different error. does that help?

root@678cf3cd1587:/home# mysqlimport --columns='head -n 1 discounts.csv' --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home# mysqlimport --columns="head -n 1 discounts.csv" --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-n 1 discounts.csv)' at line 1, when using table: discounts
root@678cf3cd1587:/home# mysqlimport --columns=`head -n 1 discounts.csv` --ignore-lines=1 temp discounts.csv -u root -p
Enter password:
mysqlimport: Error: 1049 Unknown database 'date,amount'

EDIT2

If i do it without a table created I get Error: 1146, Table 'temp.discounts' doesn't exist, when using table: discounts

root@678cf3cd1587:/var/lib/mysql-files# mysqlimport temp /var/lib/mysql-files/discounts.csv -u root -p --columns=`head -n 1 /var/lib/mysql-files/discounts.csv` --ignore-lines=1
Enter password:
mysqlimport: Error: 1146, Table 'temp.discounts' doesn't exist, when using table: discounts

If i then create the table and then try again

mysql> CREATE TABLE discounts (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   title VARCHAR(255) NOT NULL,
    ->   expired_date DATE NOT NULL,
    ->   amount DECIMAL(10,2) NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql>
mysql> show tables;
+----------------+
| Tables_in_temp |
+----------------+
| discounts      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from discounts;
Empty set (0.00 sec)

I get the following error Error: 1265, Data truncated for column 'id' at row 1, when using table: discounts

root@678cf3cd1587:/var/lib/mysql-files# mysqlimport temp /var/lib/mysql-files/discounts.csv -u root -p --columns=`head -n 1 /var/lib/mysql-files/discounts.csv` --ignore-lines=1
Enter password:
mysqlimport: Error: 1265, Data truncated for column 'id' at row 1, when using table: discounts
root@678cf3cd1587:/var/lib/mysql-files#

I am using /var/lib/mysql-files/discounts.csv explicitly because, if i understand correctly, this is the only directory i can import files from with secure_file_priv

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

mysql>

EDIT3

I got this to work mysqlimport --columns="`head -n 1 /var/lib/mysql-files/discounts.csv`" --ignore-lines=1 temp /var/lib/mysql-files/discounts.csv -u root -p --fields-terminated-by=',' --fields-optionally-enclosed-by='"'

root@678cf3cd1587:/home# mysqlimport --columns="`head -n 1 /var/lib/mysql-files/discounts.csv`" --ignore-lines=1 temp /var/lib/mysql-files/discounts.csv -u root -p --fields-terminated-by=','  --fields-optionally-enclosed-by='"'
Enter password:
temp.discounts: Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
root@678cf3cd1587:/home#
root@678cf3cd1587:/home# cat /var/lib/mysql-files/discounts.csv
id,title,expired_date,amount
1,"1Spring Break ",20140401,20
2,"2Spring Break ",20140401,20
3,"3Spring Break ",20140401,20
4,"3Spring Break ",20140401,20
5,"3Spring Break ",20140401,20
6,"3Spring Break ",20140401,20
7,"3Spring Break ",20140401,20

But I had to create the table first in my sql, as above.

CREATE TABLE discounts (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  expired_date DATE NOT NULL,
  amount DECIMAL(10,2) NULL,
  PRIMARY KEY (id)
);

I also had to use this file /var/lib/mysql-files/discounts.csv as secure_file_priv is set so that I can only load files from this directory, as I understand it.

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

tks see Edit1 I had tried that but still could not work it out. — HattrickNZ, Nov 03 '15 at 00:24

JRD · Accepted Answer · 2015-11-03T04:17:12.907

1

It looks like the space in "expired date" may be tripping you up. On the command line you'll need to quote the --columns argument, while keeping the backticks.

mysqlimport --columns="`head -n 1 discounts.csv`" \
--ignore-lines=1 temp discounts.csv -u root -p

Is the table column name actually "expired date" with the space? If so you'll need to account for that in your csv file with backticks.

id,title,`expired date`,amount

Also, ensure you specify the format of the file if it differs from the default of

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\' LINES TERMINATED BY '\n' STARTING BY ''

In your case,

--fields-terminated-by=','  --fields-optionally-enclosed-by='"'

edited Nov 03 '15 at 04:17

answered Nov 03 '15 at 01:31

JRD

1,957
1
12
17

tks and sorry but i had spotted that, I updated the contents of discounts.csv to be `expired_date` which should not trip me up I have updated my question, but still having issues, see edit3. – HattrickNZ Nov 03 '15 at 02:39
tks, got it to work, see edit 3 in my question. to get it to work I had to create the table called `discounts` with the headers, in mysql first. is there away around this? I was hoping to be able to do it all from the command line. – HattrickNZ Nov 03 '15 at 19:51
1

The table will need to exist before you can load data into it. mysqlimport is just a data loading tool. If you put the table creation statement in a file called discounts.sql, you could run that from the command line to create the table. `mysql -u root -p temp < discounts.sql` You would only run that once though, as the table only needs to be created one time. – JRD Nov 03 '15 at 20:22

importing a csv file into mysql from the command line using mysqlimport command

1 Answers1