Converting csv to sql – Part I

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!

Learning PHP and MySQL – Good Books to Have

If you are serious about learning PHP and MySQL, you need good resources. There are many online tutorials and websites, and I have listed some really useful resources in a previous post. If you are like me and prefer to not read off a screen, and instead like to use a book, there are many excellent books out there that range from novice to expert level. I have collected what I believe to be some of the best books to have and added them my Programmers Store, so that they are in one easy place for you to find. So go ahead and check it out.

You can also find some on your library bookshelf for free, which is always better… free is good. The one that I am using is Beginning PHP5, Apache, and MySQL Web Development (Programmer to Programmer), and I love it. It starts you from scratch, although it does help to have some HTML/CSS knowledge. The Appendix has instructions on installing Apache, PHP, and MySQL, which came in really handy for me.

So check out the store!!

CMS’s – Another reason to master PHP

If you are interested in web development, then you’ve probably at some point discovered, dealt with, or cursed at a Content Management System (CMS). This is actually one of the main avenues through which I discovered, and decided to learn PHP. Installing a CMS and modding it to get it to look like you want, let alone work like you want it to, can be painful. It can also be fun… it’s all a matter of perspective. Using a CMS can save you a lot of time and effort in putting a website up. Depending on the application that you choose, using a CMS can also add a lot of functionality to your website with minimal coding effort and time on your part.

Many CMS frameworks are built on PHP, and having PHP skills goes a long way in helping you install, secure, and modify files so that you get the look, feel, and functionality that you are seeking.

There are many PHP-based Content Management Systems out there, and picking out one that works is often a matter of trial and error. I don’t want to dwell on the different CMSs in this post since they are too many to count, but a good place to look at and compare them is http://www.opensourcecms.com. You can test-drive a lot of them there, look at the admin panel, and determine if the CMS is what you’re looking for.

My recommendation is that you get yourself a free, or relatively cheap hosting account that offers PHP and mySQL. A good feature to have would be the ability to create subdomains. You can then use this account to test and play with CMS applications to your heart’s content, without any risk to your primary website. This is the approach that I use when testing and picking content management systems.

Hosting

For cheap hosting, I recommend Hasty Host because they have a great capacity plan that will allow you lots of space and bandwidth to set up different systems and test them out. If you want to use a free account, then I would recommend going with Byet Host. They offer you cpanel hosting, with 3 MySQL databases, and 5 additional subdomains.

Bear in mind that you’re going to need a MySQL database for each system you install, so the more the better. I prefer to shell out the $2.95 a month for Hasty Host because I get an unlimited number of MySQL databases, and unlimited subdomains. With the free hosting plans you will have to contend with ads on your page. However, with a plan like the one I have at Hasty Host, you are in full control, and if one of your CMS experiments takes off and you like what you’ve created, you don’t have to worry about stripping everything down, and starting again on an ad-free hosting account. You’ll already have you site set up and ready to go!

Domain Names

The other thing you may or may not need is a domain name. If you go with a free hosting plan, you won’t need a domain name unless you decide to use one, and you free hosting account allows you to. In most cases your account is a subdomain of their account, for example: myexperiments.myfreehost.com, or myfreehost.com/myexperiments. If you do go with a cheap hosting account such as Hasty Host, then you will need to purchase a domain name. I recommend always buying your domain names from established registrars, such as GoDaddy (www.GoDaddy.com).
Once you have your hosting account set up, you can start playing with the different content management systems to see what they have to offer.

Some Tips

  • If you want to test the applications side by side, create subdomains for each, so that you can compare features and functionality without the hassle of installing and uninstalling.
  • Name each subdomain with a meaningful name in case your experiment takes off and you decide to keep the website.
  • See if your hosting offers a script installer such as Fantastico, since this makes installing different CMSs easier.
  • You can use your account to test blogs, bulletin board scripts, and other scripts too!
  • Most CMS apps have user forums, which are always a good place to go for tips and help when you get stuck.

Have fun! And happy coding.


www.godaddy.com

PHP Resources

I am still working on the starting stages of PHP, with real life and other obligations interfering frequently, so progress has been a tad slow. However, I have been spending a lot of time on different websites and forums learning, and getting motivated. In this post I will share some of these useful links and resources, and will be adding them as I go, so keep an eye on the Useful Links and Resources category. The ordering of these links is pretty random and does not reflect any kind of ranking.

So here goes:

  • PHP Official website – This is the home of PHP, and your best source for information on bugs, updates, and PHP documentation. There is also an extensive faq, mailing lists, and a page of useful links. Definitely your best first stop for all things PHP.
  • Digital Point Forums – Webmaster forums with a section for PHP. This is an excellent site to join and ask questions about any coding problems, and to learn about web design and webmaster issues in general. Excellent responses from members.
  • Sitepoint Forums – Another excellent webmaster forum and great resource for anyone venturing into PHP. Very helpful place.
  • HypertextFever Forums – This is a new forum that’s really cool and shows great promise for being a great resource. It has a lot more of a personal feel to it. While the bigger more established forums are awesome, sometimes when you’re a novice you need a smaller setting so that you don’t feel too intimidated… I know I do. Great quality responses, and the owner of the forum is a PHP/MySQL guru, and responds fast and extensively to all questions, so this is a great place for anyone who wants to learn and grow.
  • Joe Watkins – PHP Programmer – This website is a bit more advanced but has useful PHP scripts and interesting tips and code. You can register and subscribe to the feed to get the latest posts.

There are numerous more and I will be adding them as I go, after I evaluate them. I may eventually build a link page to list them all. If you know any exceptional ones that should absolutely not be left out please feel free to leave a comment and I will add them to my growing list!

For now… happy coding!

Splitting large csv files – the CSV Splitter

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