How to Load Text Files into MySQL

MySQL databases are often populated by loading text files directly into tables. MySQL makes this very easy to do with the LOAD DATA INFILE statements. For example:


LOAD DATA INFILE statements can read data into MySQL tables at very high speeds. This will be much faster than running many single insert statements. In order to use the LOAD DATA INFILE statement you must provide:

1) The location of the file being loaded. This can be a absolute or relative path. If the LOCAL keyword is used (i.e. LOAD DATA LOCAL INFILE) MySQL will expect the file is located on the same machine as the MySQL Client, otherwise the file is expected to be on the same machine as the MySQL Server.

2) The database table to insert the file into.

3) Optionally specifying characters that indicate things such as field delimiters and line endings.

Statement Defaults

As mentioned above, it is possible to use LINES and FIELDS clauses to specify how to parse fields and lines for insertion into the database. If these clauses are not provided, MySQL will use defaults. Using defaults is equivalent to writing:

Line Endings on Mac and Windows Files

Files created in Mac and Windows environments often have different line endings than those expected by default. For Windows files you will often need to use LINES TERMINATED BY '\n\r' . For files created on Mac, you will often have to use LINES TERMINATED BY '\r'.

Handling Duplicates

If you have a unique key on your database table and inserting data will introduce duplicates, you can use the REPLACE or IGNORE keywords to specify how to handle these records. For example:

Using the REPLACE keyword will replace existing records with the same (unique) key with the new record being inserted. Using the IGNORE keyword will “ignore” new records if the key already exists in the database table.

Loading Nulls

To load values into the database as a NULL, the value must exist in the text file as '\N'.

Example Loading Text File into MySQL

The text file we would like to load is named sample_data_1.txt, and can be found here. The fields in this file are delimited by tabs (“\t”) and have Unix/Linux line endings (“\n”). Here is a sample of what our data looks like:

id first_name last_name email country ip_address
1 Barbara Day \N
2 Jennifer Roberts China
3 Helen Gray China \N

First we need a table to load data into. We will create a MySQL table to hold this data with this following command:

Next, from the MySQL shell, we execute the following command to load the data in our file into the database.

At this point data from our file should now be in our database table and ready to query.

More details on LOAD DATA INFILE statements in MySQL can be found here.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">