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.
Pingback: Format of Numerical Values Matters to Your MySQL Database | computeraxe.com
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?