Category: Data and Databases

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!

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!

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.

1. 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.
2. 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.
3. 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.
4. 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!

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.

1. 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.
2. PHPMyAdmin – Again, we discussed this before as a good way to manage your databases.
3. 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!

Carrying on with the theme of data files and databases, I am going to talk a bit about .csv files. “csv” stands for Comma Separated Variables, and is a popular format that is supported by many spreadsheet and database applications. I work a lot with csv files, opening them in Excel to manipulate them, or saving my Excel or Access files into csv to import them into other programs, but recently I ran into a little trouble.

I obtained a large csv file that I needed to open in Excel. The file had a total of 300,000 rows, so every time I tried to open it in Excel I would get the error: “File not loaded completely”. Excel has a limit of being able to handle only 65,536 rows in each sheet (what a strange number!). So if you have a csv file that has more rows than that, you’re in a bit of trouble. This is the help message that Excel gives you:

This error is usually encountered when an attempt to open a file with more than 65,536 rows or 256 columns is made. Excel is limited to 65,536 rows of data and 256 columns per worksheet. You can have many worksheets with this number of rows and columns, but they are usually capable of fitting into one workbook (file). The number of worksheets you can have per workbook is limited only by the amount of available memory your system has. By default, Excel can manage 3 worksheets, more if there is available memory to support the quantity of data.
Truncation of rows or columns in excess of the limit is automatic and is not configurable. This issue can usually be remedied by opening the source file with a text editor, such as Microsoft Office Word, and then saving the file off into multiple files with row or column counts within the limits of an Excel worksheet. These files can then be opened or imported into Excel worksheets.
If you are using a data format that does not support use of a text editor, it may be easier to import the data into Microsoft Office Access and then use the export feature of Access to import the data to an Excel format. Other methods of importing large source material into multiple worksheets are available, but may be more complex than using either a text editor or Access.

I was not in the mood to open up my csv file and start copying and pasting, so off I went to my old friend Google, and after some searching I found a program that was written for the express purpose of splitting large csv files into smaller, more manageable ones, and it worked perfectly for me. I was able to split my csv file of 300,000 rows into 5 files of 60,000 rows each in a flash and then open each of these files individually in Excel and play with them to my heart’s content.

The program is easy to run. You click on the exe file, named, ofcourse, csvsplitter.exe; it opens a little dialog box asking you how many rows you want in the individual pieces. When making your selection, remember to keep it under 65,536 if you’re going to be working in Excel! The default value is 50,000. You can also specify the maximum number of pieces you want. Hit Browse to select your file, check your settings, and click on Split Now! and voila, out come your csv files split into manageable pieces!

It’s a handy little utility to have in your collection, and you can get it at the creator’s website.

To download the file, you will need to register an account in the forums (it’s free to register), and then navigate to the tools and utilities thread. Click here for the thread where you can download the file .

The creator of this tool has been kind enough to share it freely, so please respect his request and don’t mirror this download on any other sites or offer it for download anywhere else without the owner’s express permission. If anyone wants to download it please direct them to the FXFisherman website.

OK, that’s it for now… I will be talking about my PHP progress soon!!