Thursday, November 22, 2007

How to import CSV file to MySQL using phpMyAdmin

Here's one way to get your Excel file into MySQL via phpMyAdmin:
(NOTE: make sure your table on MySQL has the same number of columns as the excel file):

1. Export your .xls file to .csv and remove the first line (column names)
2. Launch phpMyAdmin -> select table

Import ->
Location of text file ->
CSV using LOAD DATA ->
Fields terminated by [,] ->
Fields enclosed by [ ] ->
Fields escaped by [ ] ->
Lines terminated by [\r] -> Go
Comments(16) | post a comment
Anonymous SuriG said...

Thank you! Wonderfully simple explanation after hours of surfing through incomprehensible techspeak - I knew I was nearly there and \r finally did it. Thank you again!

Monday, May 26, 2008 2:31:00 PM

 
Blogger admin said...

Your very welcome SuriG -- glad to hear it helped! :-)

Monday, May 26, 2008 2:47:00 PM

 
Anonymous Zebedee said...

Cheers. Perfect.

Wednesday, June 18, 2008 8:16:00 AM

 
Anonymous Anonymous said...

Awesome! I, too, waded through hours of jargon-filled explanations. Thanks for such an easy tip!

Saturday, June 21, 2008 11:43:00 AM

 
Blogger Sarah Ann said...

Thank you! This was very helpful for me as well!

Friday, August 08, 2008 10:18:00 AM

 
Anonymous Anonymous said...

Give that man a medal!
Thank you.

Wednesday, September 10, 2008 5:49:00 PM

 
Anonymous Anonymous said...

Again your awesome. Thanks a ton.

Thursday, November 06, 2008 4:03:00 PM

 
Blogger Jonathan Saw said...

Thank you! This was a BIG help.. I have a small problem now, what if some of my fields already have commas within the entries - how would i go about fixing that?

Sunday, February 01, 2009 8:09:00 AM

 
OpenID orion7x said...

I'm amazed this helped everyone so well. Eveyone shows the command to load the file but no one shows an exaple of what the fiile should look like. Does anyone care to show an example of what the file should look like? (the file that you are importing)

Thursday, March 26, 2009 1:49:00 PM

 
Blogger Sumit said...

You are probably the only man who knows how to import csv data. Thanks a ton. Can u pl. tell me the reason for using /r in the field "Lines terminated by". Thanks a ton again.

Monday, March 30, 2009 9:41:00 AM

 
Anonymous Anonymous said...

Thanks for the post!!! you have saved me lots of time dude!!!!

Friday, June 12, 2009 1:19:00 PM

 
Anonymous Anonymous said...

Beautiful!

Monday, June 29, 2009 11:41:00 AM

 
Anonymous Anonymous said...

thanks! much appreciated

Monday, September 21, 2009 4:47:00 AM

 
Blogger cibernetiko said...

great!!!

thanks a lot for this men...

:D

Tuesday, October 13, 2009 1:01:00 PM

 
Anonymous Aderogba Otunla said...

Seriously, you don't know how many people have been challenged with problem, and you made it look so simple. It's a gift i guess. Thanks, you're the Best...

Sunday, November 15, 2009 9:16:00 AM

 
Anonymous Anonymous said...

Thank you dude. Been looking around on how to solve this. Save me a lot of time

Tuesday, January 05, 2010 12:25:00 AM

 

Post a Comment