I was given access to an existing instance of MySQL via phpMyAdmin. My task was to import a CSV file using the import feature on phpMyAdmin. First, I went to the the main phpMyAdmin page. Next, I clicked on the database instance that I am interested. Then, I click on the table that I want to import my data and click on Import button. On the import form, I click Browse button to choose the CSV file on my local hard drive. Since my CSV file uses “|” to separate each fields and “\n” to separate each records, I chose the following options:
- Chose CSV using LOAD DATA
- Fields terminated by: |
- Fields enclosed by: [remove default value and leave it blank]
- Fields escaped by: [remove default value and leave it blank]
- Leave everything else to default value.
Finally, I clicked the Go button. This resulted the following SQL query. Note, I renamed values that are specific to my environment. So this is just for your reference only.
LOAD DATA LOCAL INFILE 'input_file' INTO TABLE `tablename` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'# Affected rows: 1
James said
Hey Dave. Busy doing this myself and having trouble… tis pain…
Dave said
If you prefer not to use phpmyadmin, you can check out other methods such as this one. Hope this helps.
Jp said
Your post helped get me to a solution. The flaw in my approach was not looking at my cvs file in notepad after I had exported it from excel. Once I did that I could see what was after every field and line in the text document and put the same thing in the “fields terminated by” and “lines terminated by” box in the CSV using LOAD DATA option of phpmyadmin (import) and it worked. I also had left the field names in the first row which probably caused another error. I haven’t figured out if there is a way to let the auto increment funtion of my primary key field populate but I just added the numbers in excel (starting from x+1, where x is that last number in the auto increment field) beforehand. Seems obvious now! Thanks.
Dave said
Hi Jp,
Good to know that this blog post helped you and thanks for sharing your experience.
Richard said
This post save me. Thank you for sharing information.
Devich » Blog Archive » Import a CSV file to MySQL said
[...] Import a CSV file to MySQL via phpMyAdmin [...]
Dave said
Hi Richard,
You welcome. Good to know that this post was helpful to you.
saad said
i have an issue regarding this.actually i have two filed in my CSV file i.e album_id and genre _id which have values 8 5 9 8 respectively in their respective fileds.
when i map that data on my table[having already two fileds name album_id,genre_id] its not mapping properly and not showing exactly those values[8 5 9 8] which are in CSV file.
ANY ONE HELP ME PLZ
REGARDS
Dave said
The example you provided appears to only have a single deliminator instead of using 2, one for separating records and one for separating fields. If it is the case, I would guess it might be a starting point to see if the issue can be resolved. Check out a few sample CSV files online.
joel said
thanks so much for your instruction. if i had found this yesterday it would have saved me hours of time. as it stands, you still saved me loads of work. One thing people might want to watch for is that if you have text strings that have a “,” in the cell it will enclose the field, in my case, with quotes, so watch for that.
Cheers
Dave said
Hi joel,
You welcome and thanks for sharing your tip.
gal said
you my friend, are wonderful.
it wasnt working at all for me – i was scared i woudl have to manually add everything, argg!
so, thank you!
Dave said
Hi gal,
You welcome. Good to see that this note is also useful to you.
Koushik said
Hi Dave, I had to do the same and made a post about that in my blog. Now I search the net and find that you had already posted about it. However, as I rechecked, doing exactly what you said didn’t work in my case. I had to fill up the ‘column names’ field too. The details with an example is at import csv to sql using phpmyadmin.
Thanks for the nice effort you are putting to help the internet community.
Dave said
Hi Koushik,
Welcome and thanks for sharing your notes.
A Hassan said
Don’t forget to auto increment your ID field if you have an ID field.
Steven Vance said
Thank you. This was helpful. I had forgotten what I did in the past when I had to load CSV files.
Rhonda said
On the auto-increment field, when uploading a csv worksheet done in excel how do we allow for the auto-increment ID fields and the auto-date field?
For example in SQL I have a table that asks for
ID (auto incremented), Date (posts date and time of record creation automatically), name, etc…
in the CSV file it begins with name, and leaves out of course the auto incremented ID and the date….
Do we then precede the data with a 0,0, so that we at least have a field with 0 in it, so that the field is then anticipated? I mean, I’m guessing this is what we do… anyone have any ideas?
Import a CSV file to MySQL via phpMyAdmin " Dave’s Blogs « DevEzine said
[...] Read more: Import a CSV file to MySQL via phpMyAdmin " Dave’s Blogs [...]
Villan said
Just thought I’d add a tip that I found following your instructions. After trying to import, and having it only do the first line, I figured that the “AUTO” setting for newline character must not like Mac branded newlines. I opened TextWrangler and changed the CSV to Windows new lines, and the import went through without any problems.
Arnab Maji said
i have a csv file, field count and type of field is correct, but the only problem is that my first field in the table is an auto generated no where as in the csv it an integer. Is is possible to import without removing this auto generated field?
or is there any way to overwrite this feild.
Plz help
Ed Mhlanga said
Thanks a lot, you really helped.
Aaron said
Auto increment is simply using the value
NULL
Nice and simple!
Ajay said
I have been able to import the csv file to my table but the table has a password column and when I try to login using the password mentioned in the imported csv file it says wrong password. When I see the existing password in the mysql table they are in encypted format, but the imported password is in normal format.
Any help.
tunde said
its cool. find it helpful