How do I import a CSV file into a MySQL database using phpMyAdmin?
Writing sql code is not required. Just use the phpMyAdmin interface for uploading the csv file.
- I assume that you have created
- a mysql database and within it a table with appropriate fields
- a csv file with entries arranged in the same order as the fields in the mysql database table (eg Suppose you have a table with fields name, age, city. Your csv file might look like -
smith,12,washinton
willy,13,chicago
maria,11,tokyo) assuming you use ',' as field separator and '\n' as row separator - Go to phpMyAdmin (It should be located in your host account control panel under database section)
- Click on the mysql database instance you are interested in.
- Click on the table to which you want to import the data. (Remember this step, just clicking on the database is not good enough.)
- Click on the 'import' tab.
- This will open an interface as shown below -
- Click 'Browse' button to select the csv file on your system which you want to upload
- Choose 'CSV using LOAD DATA'
- Fields terminated by: ,
- Fields enclosed by: [delete default value, leave blank]
- Fields escaped by: [delete default value, leave blank]
- Lines terminated by: [leave default value]
- Column names: name,age,city
- Click the 'GO' button and you are done. phpMyAdmin will show you exactly what sql query was executed to upload the csv file into your mysql database table. You can now browse your database to check proper insertion of the csv file.
Posts
17 comments:
Hi Kb,
thanks so much. You saved many hours of my time =D
I was trying with just csv option and i was having headache. Thanks to you, else I was planning to transfer 10 thousand rows by hand (ah! scary)
Thanks again man. Good work.
Avijit
Hello Avijit,
Glad to know that the post helped you. You are most welcome.
Hi - I'm getting an access denied (1044) error, even though I'm connected to the database. Any ideas?
This blog post is what finally allowed me to figure out what was going on with my csv import. Thank you! I wasted a good 3-5 hours trying to figure this out correctly before stumbling upon this page. I appreciate the post.
I have an excel CSV file populated with lots of data. I have a question. my data is currently in excel without any commas after the text. Do I need to manually add a comma to each cell of text (hope not, over 30,000 lines) or can I insert a new column and add the comma there?
@David - I wish I could be of more help. But no ideas right now.
@Mat - You are most welcome.
@Ted - If it is already CSV, it should have the commas. Open it in a text editor, you should see the commas.
Kb thanks a lot...
TED, save your excel file as .csv then open notepad to look at the file. Thereafter all above applies.
My records are all getting out of order somehow- I think due to empty fields in the record, which look like two double quotes ""
What can I do to correctly import a csv with hundreds of empty fields? Thank You.
I'm having the same problem as David, I get #1045 - Access denied. Anyone figure out a solution for that? thanks
wait I figured it out, it worked when I changed "lines terminated by" from "auto" to "\n"
This is a bit old.. but still looking for replies..
I'm importing a CVS file with portuguese chars like "António"
the only part that gets imported is "Ant"
I need to import several files and i'm spending a lot of time fixing these errors because there are multiple entries with these kinds of chars
á à í ê ç ...
Any way around this?
I changes the Char Map to latin, ascci, utf8 (default) and still nothing.
Any help?
Thank you, man. Excellent post just saved me from some serious frustrations in the last hour. Good job!
thanks man...
thank you very much man
Thank you very much... your post was very helpful. Before reading your post I tried doing this but with no success. So, I've learned a new thing..
KB - Thanks, I have been using the phpMyAdmin interface for uploading the csv file, but the problem is there is 2meg limit on file size using this method. Can you suggest an alternative approach for csv files of up to 100megs and more?
The CSV files have both required and optional fields, so the schema changes from table to table. What I like about the phpMyAdmin interface on the cPanel is it peeks into the csv file and figures out the schema each time you import a csv file into the database as a table. The first line always contains the titles.
I need a similar process for much larger files.
I followed your instruction to import .csv file to mySql database using phpMyAdmin but got this return: This plugin does not support compressed imports!. Do you have any susgession to fix it? Thanks in advance.
Post a Comment