SQL Data Import Methods

This article presents the most common ways of importing data to MySQL.

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:

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.

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.

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:

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.

Now the process is the same as importing a text file, as we did in the last section.