top of page

Upload Data to MySQL tables using mysqlimport

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:

176 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Backup Databases With mysqldump Tool

In this blog, you will study how to use mysqldump tool back up MySQL databases. MySQL GUI tools such as phpMyAdmin, SQLyog and etc, normally deliver features for backup MySQL databases with ease. But,


bottom of page