I have been playing more with databases and less with PHP these last few days out of necessity, and I’ve had to convert a bunch of files over from .csv to .sql (MySQL). Now I generally don’t like to use a method if I don’t understand it, and I try to find a way that works for me, use it, and then try to polish it, and continue looking for alternative methods until I find the best and fastest one for the task. If you read my post about working with a sql dump file, you’ll see what I mean.
This is the first of three (or maybe four) posts that will walk you through the process of converting a csv file to a MySQL file, or a sql dump file as it is commonly called, which you can then export to other users or to another database, whatever your need may be.
What I am going to describe here is probably not the easiest, fastest, or most efficient way to do this, but it’s free and works perfectly for me, and once I find a better, easier one, I’ll let you know.
To do this little task, you need several programs, thankfully all free.
- Apache, MySQL, PHP – I am assuming that by now you have an Apache Server installed on your local machine, with PHP and MySQL running smoothly, as we talked about before, and you are able to get root access to your server.
- PHPMyAdmin – Again, we discussed this before as a good way to manage your databases.
- HeidiSQL – another program that I discussed before.
In truth, you probably don’t need both phpMyAdmin and HeidiSQL, but I find that each has it’s strengths in functionality that complement each other, and for this project we’ll use both.
If you want to work through this little tutorial and don’t have a csv file to play with, you can download one in the free downloads section at myDataMaster.com. All free databases there have a zipped file that contains a csv file of the data as well as a sql dump file, which is what we will be creating. So go on and pick one of your choice, and extract the csv file to your hard drive. For ease of use, I am going to work with the US Street Suffixes database, so you can download that one or any other, or use one of your own.
In the next part I will cover the first step – creating the database and tables that you need, and how to make sure that you table has been created correctly.
After that, I will talk about the process of actually importing the csv file into sql format, and how to select the right delimiters to make sure that all your data is imported correctly.
Finally, I will discuss how to create the sql export file and what options you need to select, depending on your needs.
So on with you… make sure you have all the tools ready, so that we can begin.
…and as always, Happy Coding!