How to import data from a .csv file which has 1.88 million rows into MySQL

Question

I am trying to load the data from the below link into MYSQL database scheme

Click here

But it got time out after 30 seconds

I have used the code to set the time to 1000s. but it still does not work

 show variables like "net_read_timeout"

set net_read_timeout = 1000;

how should i load 1.88 million row to my schema

Should probably add more info on how you're reading and uploading the csv. As that is key... — AER
– AER, Commented Nov 6, 2019 at 0:21
Also look at the CSV parallel bulk loader in the new MySQL Shell (mysqlsh) — Dave Stokes
– Dave Stokes, Commented Nov 7, 2019 at 19:43

Jigar Patel · Accepted Answer · 2019-11-06 00:55:37Z

2

LOAD DATA INFILE is your solution. You can read the documentation from the MySQL website and generate the LOAD DATA query for your need. Make sure you put the file in a place where MySQL process can read. It can only load files from certain location. Again it is part of the documentation.

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';

answered Nov 6, 2019 at 0:55

Jigar Patel

4,6151 gold badge20 silver badges20 bronze badges

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

2 Comments

Gaurav Bansal Over a year ago

this works for 20k rows but it is not working for 1.88 m rows. can you please suggest another solution

Jigar Patel Over a year ago

How big is the record/row size? I can easily export millions of rows without a problem. The tool is exactly designed to do such things. What is the DB machine instance size? How much resources that system have for the MySQL to leverage?

Collectives™ on Stack Overflow

How to import data from a .csv file which has 1.88 million rows into MySQL

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