Data Import Methods

Importing from a Text File

To create a text file that contains several records to be added to our playerdata table, simply denote each column with a tab and each row by a new line. Here is a screen shot of five lines of data to be added to the database in Windows Notepad:

Figure 15-53: The data to import in Windows Notepad

Note how we use \N to specify a field that contains NULL and an extra tab is required after each row of data to signify the end of that row. We have saved this file in the MySQL bin directory (i.e., c:\mysql\bin) with the filename import.txt.

Now go to the MySQL console client and type the following:

mysql> LOAD DATA LOCAL INFILE 'import.txt' INTO TABLE playerdata;

The console will inform us that five rows have been affected or, in this case added to our database. This can be seen in the following screen shot of the console:

Figure 15-54: Importing data from a text file

If we now select all the information from the playerdata table, we can see that our five rows of data have been imported correctly into the database. Here is a screen shot of the client that shows our imported data in the table:

Figure 15-55: The imported data in our playerdata table

Importing from a Native Source

Another method available to use for importing data is using the Microsoft Excel spreadsheet program or any other application that can export data as tab-delimited data.

For this example, however, we will use Microsoft Excel. Let's enter another five rows of data that we wish to add to our playerdata table in Excel. Once this is done, it should look similar to the figure below:

Figure 15-56: Entering the data in Microsoft Excel

Once our data is entered, we need to save the data in a format that MySQL can understand. In this case, we will use tab-delimited values and save them in a text file called excel.txt in the MySQL bin directory.

Figure 15-57: Saving as a tab-delimited text file

Now the process is the same as importing a text file, as we did in the last section. In fact, if you open up the text file in Windows Notepad, you will see that the file format is identical to what we created in the previous section. Here is a screen shot of how the file looks when we open it in Notepad:

Figure 15-58: The excel.txt file in Notepad