Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied examples on how to import data from a file to MySQL table by means of both mysqlimport and “load data local infile” method. As a substitute of importing data, if you want to backup and restore MySQL database, please use mysqldump or mysqlhotcopy.
Create an employee table and employee.txt datafile
For the instances stated in this blog, let us create a very simple employee table with three columns–employee number, employee name and job.
Make a test datafile employee.txt with fields delimited by tab as revealed under.
Upload tab delimited datafile to MySQL table
Use mysqlimport to import the employee.txt datafile to employee table in test database, as exposed under:
Confirm that the records got uploaded effectively.
In mysqlimport, the name of the datafile should competition the name of the table. The extension of the datafile can be no matter what. In the above sample, only employee. * datafile can be used to upload data to employee table. You’ll get the subsequent error message when the filename is not same as tablename:
Import multiple datafiles into multiple MySQL tables
The subsequent sample uploads data from two unlike datafiles to two different tables. i.e It uploads employee.txt to employee table and manager.txt to manager table.
Use LOAD DATA LOCAL INFILE to upload data to MySQL tables
The mysqlimport client is only a command-line interface to the LOAD DATA LOCAL INFILE SQL declaration. Most options to mysqlimport resemble directly to sections of “load data local infile” syntax. You can perform the same upload described in example#1 using “load data local infile” in its place of mysqlimport as described under:
Most frequently used mysqlimport options
The most regularly used mysqlimport options are exposed in the sample below. Most of these options are self-explanatory.
compress: Compress all info sent between the client and the server
delete: This choice is very handy when you need to empty the table earlier importing the text file
local: Read input files nearby from the client host
lock-tables: Lock all tables for writing already processing any text files. This guarantees that all tables are synchronized on the server.
Output of the directly above mysqlimport command: