Import Large File Into MySQL Table Using Terminal In Ubuntu

By Budyks    Linux

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


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


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


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


    Follow Us