How to Import a Data File into MySQL via phpMyAdmin

Sometimes we have a need to read data into an existing database. Knowing how to import a data file into an established table can save you a lot of time.

First, we’ll be working with MySQL and to make things easier we’ll use phpMyAdmin to speak to the database.

Second, obtain a delimited text file, which is basically a listing of table columns where each column of data is separated by, or marked off by, or delimited by a character. Common delimiting characters are the comma, semi-colon and quote marks, but any character may be used as long as you indicate what the delimiters are.

In this example the tilde (~) encloses each field of text and the carat (^) denotes the end of the fields.

In phpMyAdmin select your database and table into which you want to import data and click on the Import tab.

Browse to the location of your delimited text file and select the character set of the file if the default is not correct.

Under “Format of imported file”, click CSV, which stands for comma separated values file or character separated values file. An options form will open when CSV is chosen, but if SQL is checked the options form remains hidden.

Under “Options” you can tick off the boxes for ‘Replace table data with file’ and ‘Ignore duplicate rows’ if either function is desired.

Enter the characters that are appropriate for your CSV under ‘Fields terminated by’ and ‘Fields enclosed by’. In the example file above ‘^’ was used for the field termination character and ‘~’ for the field enclosure character.

Characters for escaping fields or terminating lines can be input here, too. Also, if you want to add column names enter the names separated by commas in the blank provided. When ready, hit “Go”.

If successful, you’ll get a message that X number of queries were executed, which means that X number of records were imported into your table. If not successful, check the number of columns and the characters that you’ve input for the CSV and try again.

Your system may limit the size of file that can be imported. If so, check out this LOAD FILE post for a little help.

As always, verify that the data was read into the database correctly. Check that the data for each column of the table matches up with the source delimited text file.