Converting csv to sql – Creating the Database

Continuing on from my previous post, we are now going to start the conversion process. The first step is to create the database and the table that we need. You can do this using PHPMyAdmin, but I generally prefer to use HeidiSQL. The process is basically the same in each case, but I find HeidiSQL to have a better interface.

I am working with the database that I referred to in my last post, the US Street Suffixes database.

The first step is to fire up HeidiSQL, and log into your server. You get an interface that has all your databases listed and all that good stuff.
Next, we want to create the database that we will store our data in. Click on Tools -> Create Database. Let’s call this database streetsuffix. HeidiSQL immediately creates and selects the database. You can tell what database is selected by checking the left panel to see which little cylinder is yellow.
The next step is to create the table that will hold our data. Again, Tools -> Create Table. Let’s name our table suffix. You can also name it streetsuffix if you want. Because this is a simple project, we are only creating one table, but you may have more than one in some instances. At the table creation stage in HeidiSQL you can go ahead and create all your fields and set their properties right there. Or you can create the table, add at least one field, and then go back and create, edit, delete, rename fields.
To create the fields, you need to know what you want to name each field, what kind of data it will hold, and what fields you want to use as indexed. This information requires that you know a lot more about databases than I want to cover in this post, and you can learn a lot about this by looking at the Heidi Help file and using google. For now, we are going to go ahead and create our fields for the US Street Suffix file. Open the csv file in Excel so that it’s easier to see the field names and the data.

Create a field named ID, set it as Primary, NOT NULL, and auto-increment. Its data type should be INT with a size of 3. These parameters will vary depending on your data, and you’ll learn to play with them as you go. It’s always advisable to have a field like this as the primary field. In our case, the csv file already has an ID field and so we will import it directly into this field. In some cases the csv file does not have an ID column. You would still create one in Heidi SQL with auto-increment turned on, and then deselect it during data import. Don’t worry about this for now, it will make more sense in the next post.
Create the other fields in similar fashion, selecting the appropriate data type (mostly text), except that none of the rest should be set as primary, nor should they have auto-increment selected. Remember that the names should not have any spaces or special characters, and you should match the field names in your csv file to your table field names.
Once this is done, you’ve created your database and its table. Remember if you forget a field, or mislabel it, or need to change it’s properties, you can.

After creating the table, go back to HeidiSQL and look at the table structure to make sure it matches what you entered. You can also do this using PHPMyAdmin. Check all your fields thoroughly, because once your data is imported you really don’t want to mess with your fields unless you know what you’re doing.

You now have your database ready, and your table in it. Remember that you can do the same thing we have done above using PHPMyAdmin, and you can experiment with both in creating databases and tables until you find the one you prefer.

Next, we are going to import our data from the csv file into the database that we just created.

A little more reading for you:
http://onlinetutorials.net/tutorials/SQL/mysql_for_beginners.shtml
http://www.phpmyadmin.net/documentation/#faqusing
http://www.php-editors.com/articles/sql_phpmyadmin.php

Happy Coding!