Video Game Database Tutorial Part 1: How to Import a Spreadsheet into a MySQL Database Table.

In Part 1 of this tutorial, I will show you how to convert a spreadsheet file into a Comma-Separated Values (CSV) file and ultimately import the values into a MySQL database table. While there are more direct software options for achieving this, I feel that this method is the most flexible in terms of troubleshooting and customization.

For this tutorial, I will be using OpenOffice Calc 4.1.1 to create my spreadsheet file. However, the tutorial should work exactly the same for Microsoft Excel, and any other spreadsheet software that can save to CVS format. I am also using WAMP 2.5 64 bit through Windows 7 to build and test this database. Your end results may vary slightly depending on your system's configuration. Please refer to the WAMP website for the latest version and install instructions.

You can begin by creating a sample spreadsheet. You can choose however many columns and rows as you wish. For this example I created an spreadsheet cataloging my xbox 360 game collection. The spreadsheet contains 55 rows, one of which is a header, and 5 columns of data. The columns contain the name of the game, a link to a screenshot for each game in a sub-directory, the year the game was published, its genre, and if I own it as a Digital Download or actual DVD.

1. First step, save your spreadsheet file as a CSV text file using the File > Save As In OpenOffice Calc, a export option window will pop-up asking you what character set to use (see Figure 1). For this example, I kept the default Western Europe Windows 1252 character set option. (PLEASE NOTE that your spreadsheet program will only save the currently visible spreadsheet to CSV format. For a workbook with multiple spreadsheets, you will have to save each spreadsheet individually.)

   
   
2. Open your CSV file in any text editor such as Windows Notepad to check it.
   
   
   
3. Open your MySQL console.
4. 
   First we will have to create a new database. For my example, I'll name my database xboxGames and then select it for use.
   
   
5. Once selected, it is time to create a table for your database. In this case, I will create one called xbox360 which will contain 5 columns keyed in as “Game”, “Screenshot”, “Year”, “Genre”, and “Media”. As you can see, in the code below, all of the columns are character fields (CHAR) with the Year being strictly and integer (INT). In the command, I also set the ID to be an auto increment integer that will act as a primary key.
   
   
6. We will now import the CSV using the load local data command in the MySQL console. Note that the command is set to ignore the first line of the CSV file which contains the Header row that was present in my original Spreadsheet.
   
   
7. If you receive no errors, you should be able to enter the following command to successfully view your newly populated table within the MySQL console window.
   

In part 2 of my tutorial, I will show you how to create a PHP file to display the database on a web page and how to format it using CSS3

Video Game Database Tutorial Part 2: Displaying your Database in HTML using PHP and CSS