MYSQL load simple csv infile not working correctly

Question

MAC OSX MAVRICKS

Hello I have a very CSV file that I am attempting to use LOAD INFILE with mysql

Here is what the CSV looks like....

f1,f2
a,e
b,f
c,g

d,h

Here is my CREATE TABLE statement:

create table test_import_csv (
f1 VARCHAR(20), 
f2 VARCHAR(20)
);

Here is my LOAD INFILE statement;

load data local infile '/Users/name/Downloads/test_import_csv.csv' 
into table test_import_csv
fields terminated by ','
LINES TERMINATED BY '/n'
(f1, f2);

this statement goes through however this is what it looks like...

mysql> select * from test_import_csv;
+------+------+
| f1   | f2   |
+------+------+
a |1   | f2
+------+------+
1 row in set (0.00 sec)

I have tried variations in the loadinfile statement as well as giving it an primary KEY ID... but nothing works...

Anyone know what is going on and why loadinfile is not working for this simple CSV?

replaced that here is what happened output still is wrong, its only importing one row and not even the first one weird... mysql> select * from test_import_csv; +------+------+ | f1 | f2 | +------+------+ b | | e +------+------+ 1 row in set (0.00 sec) — Imran Hassanali
– Imran Hassanali, Commented Nov 21, 2013 at 0:31

akaya · Accepted Answer · 2013-11-21 01:56:48Z

2

If your query can import one row, I suspect the problem is with your line termination. If your csv is created in Windows it may be using '\r\n' as line terminator. Try LINES TERMINATED BY '\r\n'

answered Nov 21, 2013 at 1:56

akaya

1,14610 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Imran Hassanali Over a year ago

you were right as soon as I did that it worked..... Every single place I looked either said '\r' or '\n' or '\rb' but never both r and n. Probably one of the most frustrating things ever

Anupam Over a year ago

spend all the internet and conclude this => THIS WORKS.

Collectives™ on Stack Overflow

MYSQL load simple csv infile not working correctly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related