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,genderJames,092818212,,43,malenadya,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