CODEWITHSUNDEEP
mysqlcsvmysql-loadfile
Alexandru
Alexandru

Reputation: 971

Why isn't MySQL inserting rows from CSV file? I've been using LOAD DATA INFILE wrong?

I have a CSV file containing rows just like the following one:

2,1,abc123,1,2,"Hello World"
2,1,abc123,1,2,"Hello World2"
2,1,abc123,1,2,"Hello World3"

I'm running the following query:

LOAD DATA LOCAL INFILE :path INTO TABLE errors
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(import_id, type, code, row, cell, message);

It does not insert any of my rows into the database. Here's the structure for the errors table:

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| import_id | int(10) unsigned | NO   | MUL | NULL    |                |
| type      | int(10) unsigned | NO   |     | NULL    |                |
| code      | varchar(128)     | YES  |     | NULL    |                |
| row       | int(10) unsigned | YES  |     | NULL    |                |
| cell      | varchar(32)      | YES  |     | NULL    |                |
| message   | varchar(128)     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

I've noticed that if I change the order of the columns, it works.

For example, in my CSV file

1,abc123,1,2,"Hello World",2
1,abc123,1,2,"Hello World2",2
1,abc123,1,2,"Hello World3",2

Also changed the query:

LOAD DATA LOCAL INFILE :path INTO TABLE errors
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(type, code, row, cell, message, import_id);

Why is it working with a different order for columns?

Upvotes: 0

Views: 208

Answers (1)

SadmirD
SadmirD

Reputation: 662

Please verify the version of your mysql and whether this option (load data infile local) is enabled by your tool that you are using, along with it being enabled on server side. It is considered a security risk and is disabled by default by newer versions of mysql server.

Here is more info on security issues about utilizing load infile local MYSQL OFFICIAL DOCS

Also pay attention that you probably need to have local_infile=1 and you can check it with the following command:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

To enable it, use the following command:

SET GLOBAL local_infile = 1;

Also verify that the lines are terminated by '\n' and not by '\r\n' (this is for windows environment)

Hope that helps!

Upvotes: 1

Related Questions