Support Start > Web Hosting Technical Information > MySQL Databases > MySQL Import > Importing a Text File

You can use the phpMyAdmin Web pages we provide to import a text file into a table of your MySQL database. You might need to do this, for example, if you have data in Excel that you need to import into MySQL.

(If you have a normal SQL "dump" file, see our "MySQL Import" page instead.)

On this page:

• Preparing the text file
• Connecting to phpMyAdmin
• Importing the data
• Are there other ways to import data?
• Can I use "LOAD DATA INFILE" or "LOAD DATA LOCAL INFILE"?
• What about large imports?

Preparing the text file

You first need to prepare the text file so that it's in the right format. The simplest way is to use a CSV (comma separated values) file exported from Excel, making sure that the order of the fields in the text file is exactly the same as the order of the fields in your MySQL table.

Hint: If you use any dates in the Excel file, set the field to use the custom "yyyy-mm-dd" Excel data format before you export. This will make sure that the field matches MySQL's date format and imports correctly.

Connecting to phpMyAdmin

When you've exported the Excel data to a CSV file on your hard disk, go to https://www.tigertech.net/phpmyadmin/ and login. The username is your MySQL database name, and the password is the password for that database (which may be different from your main account password).

Importing the data

After logging in, navigate to the phpMyAdmin import screen by doing this:

• Look in the left-hand column and click on the name of the table into which you want to import the data.
• Click the Import tab that appears at the top of the page.
• Change the "Format of imported file" to CSV. (Do not choose "CSV using LOAD DATA"; it will not work.)

You'll then see a screen that allows you to choose the import options:

• Next to "Location of the text file", choose the file from your hard disk that you want to import.
• Change the Fields terminated by option to be a comma instead of the default semicolon.
• Leave the other options unchanged unless you know what you're doing.
• Press Go.

You should see something like Inserted rows: 1209 (Query took 0.00 sec) at the top of the screen. Click the Browse tab to review the imported data.

Are there other ways to import data?

Yes, definitely. The method suggested above is only one way of doing it.

Another option for advanced users is to write a script in Perl or PHP that reads your text file, splits up each line into fields, and then performs the appropriate INSERT commands. Each script will be different, but an experienced programmer should be able to write such a script in a dozen lines of code or so, and it gives you the greatest flexibility.

You can also enable remote access to your database, allowing you to use a program such as MySQL Workbench on your own computer to manage it.

Can I use "LOAD DATA INFILE" or "LOAD DATA LOCAL INFILE"?

You can use MySQL's "LOAD DATA LOCAL INFILE" feature from the mysql command line utility, but not "LOAD DATA INFILE" without the "LOCAL".

This is because allowing the non-LOCAL option would require granting each customer the MySQL FILE privilege, which would allow any customer to view another customer's files and databases. For that reason, the MySQL documentation recommends that the FILE privilege not be used on a shared server, and we follow that advice.

If you're using the phpMyAdmin “CSV using LOAD DATA” import option, be sure to check the Use LOCAL keyword box to avoid an error that starts with “#1045 — Access denied”.

If you're using the MySQL command line, you have to explicitly tell MySQL that you intend to use "LOAD DATA LOCAL INFILE" during this connection. You do this by passing the --local-infile=1 flag if you're using the shell, like this:

mysql --local-infile=1 -u databasename -p databasename

In PHP 5.3 and later, you’ll need to enable the MYSQLI_OPT_LOCAL_INFILE mysqli option, like this:

$db = mysqli_init(); 
$db->options(MYSQLI_OPT_LOCAL_INFILE, 1);
$db->real_connect("localhost", "databasename", "password", "databasename"); 

What about large imports?

If you're adding more than a few thousand rows, be sure to disable MySQL indexes during the import, as described on our “Large MySQL Imports” page.