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.

Example character separated values or CSV file.

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.

The Import tab of phpMyAdmin.

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

The Import page in phpMyAdmin.

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.

Select the CSV button to open this part of the import page.

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.

This entry was posted in mysql, php. Bookmark the permalink.

36 Responses to How to Import a Data File into MySQL via phpMyAdmin

  1. Pingback: Format of Numerical Values Matters to Your MySQL Database | computeraxe.com

  2. Alana says:

    Thank you so much for this info!!! Saved me.

  3. angela such says:

    the phpmyadmin that I am using ONLY shows SQL as the ‘format of inported file’.. why? and how can I get around this if I have a .csv file.

    THANKS for your assistance.
    Angela

  4. LizzyFin says:

    Hi Angela,

    When you log in to phpmyadmin, select your database, and click on the Import tab, you will see SQL as the only choice under “Format of imported file”.

    You need to click on the table name that you want to import data into in order to see CSV as a choice for the format of your imported file.

    Good luck!

  5. Angela says:

    THANKS! I see it now!

  6. Yopie says:

    Trying to figure out why I cant import classified ads through phpmyadmin and to get the pictures as well, this is scraped data

  7. LizzyFin says:

    Yopie,
    Perhaps the real authors have some voo-doo protection of their data going on. Can’t blame them, and we can’t help you if you can’t help yourself! Instead of scraping content, take time to make your own content and you’ll get a lot further in life.

    Pitiful. This teacher gives you an F for plagiarism!

  8. RottenElf says:

    LizzyFin,

    Hopefully you can help me. Explaining this may be a pain for ya, but I would really appreciate any assistance that you may be able to give me.

    Here is my scenario.

    -I am trying to import a csv file into phpmyadmin.
    -The MySQL database is for WordPress.
    -The .csv file is individual posts.
    -Each post has multiple custom fields.
    -Some of the fields include content such as (“) and (,)

    My issues are as follows:

    1) I am trying to figure out how to do the import into the wp_post table, even though there is no “field” within THAT table that is “category”, nor are the “custom field” fields. They are apparently within different tables.
    2) I cannot import the posts without the custom fields, as they are extremely valuable to the posts themselves.
    3) Two of the custom fields are hyperlinks that reference specific images within the database
    4) One of the custom fields is a hyperlink to another page within the site.

    Any ideas? Even a point in the right direction would help out a lot. I’m just getting starting with databases, and would love a little nudge in the proper direction. I don’t want to completely mess up the website before I even get it to launch. Thanks in advance.

  9. axe says:

    Hey Elf,
    I can’t help out too much with your query, but this seems like a great place to start: ZAck PReble.

    Let us know how you do. Good luck!

  10. nandy says:

    Thank you! you’re such a great help for me.. Thanks a lot. :)

  11. axe says:

    You’re very welcome, nandy!

  12. Shoaib says:

    helped me alot

    thanks for sharing

  13. axe says:

    You’re welcome, Shoaib!

  14. Helen says:

    Great tutorial but doesn’t quite meet my needs. Is it possible to import from a .csv file into specific fields in an existing table? I have a table with 4 fields and a .csv file with data for only 2 of those fields. I can’t figure out how to tell phpMyAdmin to import only into those 2 fields. Is it possible?

  15. axe says:

    Hi Helen,
    If you want to import only certain columns of the .csv file into your DB table, check out the format-specific options listed in the section for importing CSV files. For “columns” list the names of the columns that you want to import, in a comma-separated list.

    Give it a whirl and let us know how you make out!

  16. How about if I want to import data into specific rows? Is there a way to have the upload search for the row that has particular data in a field and then append the data from the CSV file?

    Thanks!

  17. axe says:

    Hi Rose,
    Functions for importing data are designed for moving large amounts of data into databases or tables, so you basically have to know up front what database or table you want your new data to be in. Extending that idea to row-based data isn’t possible with import functions, but you could always use an HTML form to collect whatever info you want and then insert the data into the DB where it makes sense.
    Make sense?

  18. kienanh.vn says:

    hi!
    I’m Try to insert field: Column names ?
    Fields terminated by : ;
    Fields enclosed by: ”
    Fields escaped by: \
    Lines terminated by: auto
    Column names: ??????????????????
    My table have: 12 Fields: how can i input in the Field: Column names: ?????

    Plz!

  19. axe says:

    Hi Kienanh,
    If you just want to import/export certain fields, enter their column names separated by commas, like field1,field2,field3 and so on. If that’s not what you’re trying to do, explain a little more so we know what it is you need help with.
    Thanks!

  20. dane says:

    Not sure if this was pointed out, but if you already have a table set up and you’re loading in a delimited file that has headings across the top, you can use CSV using LOAD data and have it skip the first line. Just make sure the columns are in the same order as your fields (and the same data type… DD-MM-YYYY 00:00:00 for DATETIME, etc)

  21. dane says:

    Oh, and this does work for files that aren’t CSV, like for tab delimited, use \t for field term and \n for line term :)

  22. axe says:

    Excellent, Dane! Thank you so much for pointing this out.

    Choosing the CSV using LOAD DATA option will allow the data and not the headings to be imported into a table.

  23. axe says:

    Great input, dane! CSV files are more common in my work, so it’s nice to know this import function works with tab-delimited files, too.

  24. q1 says:

    Nice one….

  25. dnesh says:

    how to import zip file to my sql by phpadmin

  26. axe says:

    Hey Dnesh –

    Look on the import screen for phpMyAdmin under the “File to Import” section. The last line states “Imported file compression will automatically be detected…” if you’re still using an older version of phpMyAdmin. With a newer version of phpMyAdmin (at least version 3.4.11.1 or later) the wording here has been updated to read, “File may be compressed (gzip, zip) or uncompressed. A compressed file’s name must end in .[format].[compression]. Example: .sql.zip”.

    So, the short answer is Yes. Just browse to your .zip file, make sure it’s in the right format (filename.sql.zip) and you’re good to go.
    Let us know how it works for you.

  27. Francesco says:

    I get the error: “File could not be read” !!

  28. axe says:

    Hi Francesco,
    Where did you get the file? Was it a file that you’d previously exported from a WordPress site?
    That error message will result from trying to import a .zip file when the system expects a .sql.zip file, in other words a zipped, exported file.

  29. Francesco says:

    Hi Axe,

     

    Thank you for your reply. The file is an .sql file and not zipped. Yes, from a wordpress site. However I fixed by upgrading the phpmyadmin version to the 3.4 which somehow fixed the issue. It could have been a bug with the previous version.

     

    Thanks!

  30. axe says:

    Hey, that’s great Francesco! Don’t you love it when everything turns out alright after an upgrade? :)
    Thanks for writing back.
    Have a happy day.

  31. Francesco says:

    Yeah! And specially after you get crazy for few days trying to figure out what could be the issue!

    Have a lovely day too :)

  32. Simon says:

    Thanks for the script , works great.

  33. axe says:

    Sure thing, Simon. Nice design on your site, BTW!

  34. Pingback: how to skip duplicate records when importing in phpmyadmin - Database Solutions - Developers Q & A

  35. Ande says:

    For importing you say we should select the Format of the file to import.  That option is not available in the  phpMyAdmin provided in our GoDaddy Web Hosting.  The only option we have is SQL.  Is there  a way around that?

  36. axe says:

    Hey Ande,
    I think the best thing to do in your instance is to chat with your hosting provider to get the details on they would handle this event. They may already have a solution for you to use. Sorry I can’t be of more help in this case. Good luck!

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=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">