Load data infile, difference between Windows and Linux

Question

I've a file which I need to import to MySQL table. Here's my command

LOAD DATA LOCAL INFILE 'C:\test.csv'
INTO TABLE logs
fields terminated by '|'
LINES terminated BY '\n'

This looks fine in Windows, but in Linux it inserts only the first row and generates an error in log file

LOAD DATA LOCAL INFILE '/home/myuser/test.csv'
INTO TABLE logs
fields terminated by '|'
LINES terminated BY '\n'

What I need to change in Linux?

Answer 1

I've tested this "LOAD DATA INFILE" in windows 8.1 using mysql 5.6.17. Below is the table format

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+ 
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| charactor | varchar(30) | YES  |     | NULL    |                |
| movie     | varchar(30) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

LOAD DATA LOCAL INFILE 'C:/Users/kaviranga/Desktop/scifi.csv' INTO TABLE scifi FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 0 LINES (charactor,movie);

This worked perfectly and the csv file format I've used as below

"Soldier 2","Pirates of the Carribian 2"
"Soldier 1","Pirates of the Carribian 4"

Don't use like below.It may causes errors.

'C:\Users\kaviranga\Desktop\scifi.csv'

I've included this answer for future reference.