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.
Thank you so much for this info!!! Saved me.
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
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!
THANKS! I see it now!
Trying to figure out why I cant import classified ads through phpmyadmin and to get the pictures as well, this is scraped data
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!
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.
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!
Thank you! you’re such a great help for me.. Thanks a lot. 🙂
You’re very welcome, nandy!
helped me alot
thanks for sharing
You’re welcome, Shoaib!
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?
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!
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!
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?
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!
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!
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)
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 🙂
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.
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.
Nice one….
how to import zip file to my sql by phpadmin
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.
I get the error: “File could not be read” !!
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.
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!
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.
Yeah! And specially after you get crazy for few days trying to figure out what could be the issue!
Have a lovely day too 🙂
Thanks for the script , works great.
Sure thing, Simon. Nice design on your site, BTW!
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?
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!
how to import multiple csv files  into phpmyadmin by automatically
Hey Rejesh,
Not really sure what you mean by “automatically”. Can you explain further what you’re trying to do?