Sometimes we wanted to Import a large file into a table in MySQL and failed or corrupted when we use phpmyadmin etc. MySQL has this load data function, where we can insert data to a table from external files like csv, txt etc trough terminal for Linux.

The process is quite simple, open your terminal/console and type :

mysql -u root -p

Enter your MySQL password if you have it or just hit Enter. if you're using another username just replace root with your username. We'll see mysql syntax like this

mysql>

Navigate to the Database where the table we wanted to insert exist.

mysql> use mydatabse


Its now time to import. Suppose we have a big .txt file but the content separated by comma

Name,phone,email,age,gender
James,092818212,,43,male
nadya,0291843,,23,female
.....
....
....

Or maybe you have your own txt,or csv files separated by semicolons.

mysql> LOAD DATA LOCAL INFILE ‘/home/ubuntu/mybigfiles.txt’
        -> INTO TABLE my_table FIELD
        -> TERMINATED BY ‘,’
        -> ENCLOSED BY ‘”‘
         -> LINES TERMINATED BY ‘\n’
         -> IGNORE 1 LINES;

You'll see '->' sign, it means that we can still continue to another command after hitting ENTER. Dont forget to add ';' (semmicolon) and the end of the commands. like this

 -> IGNORE 1 LINES;

Enter to run the query. If nothing's wrong, You will see this message:

Query OK, 45570 rows affected (0.00 sec)
Records: 45570  Deleted: 0  Skipped: 0  Warnings: 0

That's it