Convert csv to sql – Creating the sql file

This is the last part in a series of tutorials on how to create a sql file from a csv file. First, we determined what was needed for the process, then we created the MySQL database and tables, and finally we imported the csv file into the MySQL database.

In this last step, we are going to generate a sql file from our database that we can then export to another user, or use for any other purpose that we need a sql dump file for. Sql dump files are a common format used in selling or buying databases and database products.

To create the sql file, we can use either PHPMyAdmin or HeidiSQL. Both work equally well, and I will describe the process for each:

Creating a sql file using PHPMyAdmin

1. Fire up PHPMyAdmin, log in as root, and select the database and table that you’re working with. In our case this would be the database “streetsuffix” and the table “suffix”.
2. Click on “Export” in the menu, and make sure that SQL is the selected format in the file that opens.
3. The thing that is important now is to select the correct SQL options. I am assuming that this is the first time you are creating a sql dump file for this database, and so you want the query to create and populate the database on the other end. So you would make sure Structure was selected, and then select the options that you want. If you want the query to delete any existing tables by the same name and create a new table, then select “Add DROP TABLE”. If you want the query to check if the table exists and add it if not, then select “Add IF NOT EXISTS”. When you look through the generated sql file this will make more sense.
4. The maximal length of the created query can be left at the default value unless you have an extremely large database, in which case you may need to increase it, or if you have size restrictions you may want to reduce it.
5. The Export type is determined by what your query is going to do. In our case, we want the query to insert. If you were issuing an update to a database then you would want to select update, and so on.
6. Make sure that you have the right number of rows for your database selected under Dump.
7. Finally, click Go. PHPMyAdmin will generate the query and open it in a new page.
8. You can then copy this query and paste it in Notepad++ or your favorite editor and save it as yourfilename.sql, in our case streetsuffix.sql.
9. You now have a sql dump file!

Creating a sql file using HeidiSQL

1. Fire up HeidiSQL and log in as root, select the database and the table you want to work with.
2. On the main menu, select Export -> Export tables as sql
3. A dialog will open that has two tabs: Source and Destination.
4. In the Source tab, select the table you want to export, in our case “suffix”.
5. In the Destination tab, there are two sides: The Options, and the Output.
6. Under Options, you will need to select what you want your query to do, similar to what we did in PHPMyAdmin. Do you want your query to create the database and table, replace them if they exist, or delete them and start afresh? Do you want your query to insert data, or replace/update data in the existing table? These are things that are dictated by your application and usage of your database. For this tutorial, it’s a fresh install so we want to create database and table and insert data.
7. On the Output side, select the folder where you want to save the file and what you want to call it. Unlike PHPMyAdmin, HeidiSQL creates and saves the file for you.
8. You can also use HeidiSQL to output the data to another database 0n your host or on another database on another host.
9. You now have a sql dump file. You can examine this file using Notepad++ or your favorite editor.

You probably noticed that there are other options in PHPMyAdmin and HeidiSQL that I did not touch on. This is because this is intended to be a newbie/basic tutorial, so I didn’t want to go into too much detail on all the possible options.

This completes our four-part tutorial on converting a csv file to a sql dump file without any coding or scripting knowledge, and without spending a dime. I hope you find this helpful, and if you do, please leave me a comment. If you notice any errors, or want to add something, or know a better way to do it, please let us know also by leaving a comment.

All questions, suggestions, comments, and corrections are welcome!