How to import large csv files to mysql?

Question

I just want to ask a question about importing a csv files to mysql tables. Because i have a csv file that contains 43762 rows but when i loaded it im my table it only inserted 2181 rows. I just wondering if there's a bug in my mysql.

Here's the output of my insert code:

mysql> load data local infile 'c:/database/cities_table2.csv'
-> into table ref_draft
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> (municipality,name);
Query OK, 2181 rows affected, 2 warnings (0.28 sec)
Records: 2181  Deleted: 0  Skipped: 0  Warnings: 2

mysql> select count(*) from ref_draft;
+----------+
| count(*) |
+----------+
|     2181 |
+----------+
1 row in set (0.00 sec)

Is there other way to import large files?

Did you show the warnings to see what they say? I'd be curious. — lurker
– lurker, Commented Jul 30, 2013 at 1:13

Pamela Hazelton · Accepted Answer · 2013-07-30 01:13:32Z

2

Did you turn off indexing? Also, make sure there's not a lot of incoming activity so you don't "hog" the server resources.

answered Jul 30, 2013 at 1:13

Pamela Hazelton

662 bronze badges

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

Comments

rochellecanale · Accepted Answer · 2013-07-30 01:14:57Z

2

Ok i solved my problem I just need to replace

lines terminated by '\n'

to

lines terminated by '\r\n'

Thanks for the immediate response sir.

answered Jul 30, 2013 at 1:14

rochellecanale

2052 gold badges8 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to import large csv files to mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related