Convert csv to sql – Importing the csv file

We are going step by step through the process of converting a csv file to a sql file. In the first part, we discussed what tools we need to accomplish this task, as well as where to find a sample csv file to play with. We then covered creating the sql database in the second part.

In this third section, I am going to explain how to import your csv data into the MySQL database that you created, and to make sure that the data is all in the database and looks like you expect it to.

From the last tutorial, you now have in your server a database named “streetsuffix”, and that database contains a table named “suffix” or whatever you decided to name it. Your table has fields corresponding to your csv file, with the right properties set to them as discussed in the last tutorial.

Now we want to import our data from the US Street Suffixes csv file to the streetsuffix database. This, for me, is where HeidiSQL does a beautiful job.

1. Fire up HeidiSQL and log into your server with root access.
2. Select the database “streetsuffix” and open the table “suffix”.
3. On the main menu, click on Import -> Import CSV file.
4. A dialog opens up that allows you to select the file you want to import from (Filename), and the specify delimiters and all that other good stuff on the left side. On the right side of that dialog box you should the name of the database you’re importing the data into, the table, and all the fields that you created. Check that this data is correct before proceeding.
5. Recall that last time I mentioned that in some instances your csv file does not have an ID field, but that it was a good idea to create one and set it to auto-increment? Well in this case our csv file does have one, so we can leave the ID field checked. If our file did not have one, we would uncheck the ID field, so that MySQL would populate that field instead of looking at our csv file for an ID column.
6. Once you’ve made sure that everything on the left side is correct, look at the right side. The first thing is to select the csv file from your computer so that it appears under filename.
7. Then set your delimiters. In our case, our fields are separated by commas (,) so we need to enter that into the “fields terminated by” field. Click on the check box to activate the field.
8. The “enclosed by” field is useful when your fields have long sentences or other data that are enclosed in “”, or ”, so if these are present, select this now and enter what the enclosing marks are. You can tell this by looking at your file. I find that it helps to check optionally because some entries might be and others might not. In this case, do select it just for practice.
9. As you deal with more complex files you will see the use of the other options. The best option is usually to have your csv file open in Notepad++ so that you can see all delimiters and markings clearly as you make your selections.
10. Once you’re done, hit Import, and let HeidiSQL do it’s thing!!

You now want to look at your database to make sure all the data was imported and looks like you envisioned it. You can do this in either HeidiSQL or PHPMyAdmin.

1. In HeidiSQL, while you have your table selected, click on the tab right above the fields that’s labeled “Data”, this should allow you to see your data. Sometimes in HeidiSQL you don’t see the actual information, but you can still verify the import by clicking on the database (or on the Database tab to see the total number of entries and confirming that they correspond to your entries.
2. PHPMyAdmin is actually what I prefer for this verification part. In your browser, navigate to localhost and open up your PHPMyAdmin. Log in with root access and select your database “streetsuffix”. On the left side, select the table “suffix” and on the main part of the browser, select Browse. Your database should open up showing you all the fields and the entries that you just imported. I recommend that you keep PHPMyAdmin open as we are now going to go to the final step – creating the sql dump file!

The major part of the work is done, we now have everything we need for the final part, creating the sql dump file, which I will discuss shortly in the last post of this series.

Remember if you have any problems so far, any comments, suggestions, or corrections, you can leave a comment here. I welcome all comments and will respond to them as fast as I can.

Happy Coding!