Wwwebber's Blog

How to import CSV into MySQL table: Step by Step Instructions with Pictures

Posted in MySql, phpMyAdmin, tutoring services by wwwebber on January 1, 2009

Just show me the steps dude

I recently had to create a zip code locator program for someone’s website that I was designing. This site was for parents/guardians/students to be able to find a tutor. Basically, hubalub.com is a tutoring service, and one that is, I believe, unique from most of the other tutoring services out there. That aside, never having written a script like that before, I soon found out that you get the latitude and longitude of a given zip code and find all zip codes with a given radius. In another post, I’ll describe some optimizations that you definitely want to pursue to implement this–let’s put it this way: the database of zip codes has about 42,000 rows! And you don’t want to query 42,000 rows ! Anyway, to get the zip codes and their associated data like county name etc.. you ‘re going to need to download a zip code database. There are several free and commercial ones available. Just do a google search for them. If like me, you get a humongous CSV text file with all of this data, you’ll want to know how to get all the data from this CSV into a MySQL table. And PhpMyAdmin comes to the rescue:

Steps to Import CSV into MySQL table

  • Step 1) Create a table that has the necessary number of columns. If the CSV has 10 types of data, you’ll need 10 columns.
  • Step 2) After having created you MySQL table, log into phpMyAdmin and click on this table.I had a table called `morris_zipcodes`.
  • Step 3) Click on the ‘import ‘ (For this step you can refer to the picture below
  • Step 4) You should now be looking at something like the next picture. I circled in red the important parts of the form.

  • Step 5) Click on the ‘csv’ radio button. You will then see a fields that have to do with your CSV is formatted. Since my CSV’s fields were not separated by tabs I entered ‘\t’ as you can see above (no quotes). My fields were not enclosed by quotes so I removed the default quotation marks for ‘Fields enclosed by’. I left the fields escaped by in its default form (didn’t need to worry about escaping). And lastly, since my lines were terminated by a new line I entered the ‘\n’ for the ‘lines terminated by’ field.
  • Step 6) I browsed to the massive text file, uploaded it and was soon happily greated by this message : “Import has been successfully finished, 79948 queries executed.” WOw that’s a lot of queries. I think it took about a minute or two, for MySQL to create all of those rows, not bad!

Comments Off on How to import CSV into MySQL table: Step by Step Instructions with Pictures