Author: mary

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!!