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.

Legal Disclaimer

Our website is not responsible for the information contained by this article. Webworldarticles.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.

This article was sent to us by: George Ionescu at 12062007

1. Joining SQL Tables
When accessing information within database tables, we may sometimes need to look at data from two or more different tables, as we saw in the previous example. There is ...

2. Basic SQL syntax: Creating and Modifying tables and databases
This tutorial shows basic syntax and commands and of the SQL language. Creating and Dropping Databases First, let's see what databases already exist ...

3. Introduction to SQL
SQL is an acronym for Structured Query Language and is the standard language for interaction with databases. SQL is both an ISO (International Organization for Standard...

4. What Is a Database
A database is simply an organized collection of information. It allows many different types of data to be stored and retrieved in a highly efficient manner. Info...

5. Installing MySQL Server
Installing MySQL is relatively painless. First, you need the installation program for MySQL, which is available on the companion CD. You can also download the latest ve...

6. Data Entry and the Various Industries that use this Service
Group of people flooding Excel sheets with alpha and numeric characters... spontaneous sound of pressing keyboards... these are what you will find in data entry service. Fr...

7. Different Forms and Variations of Data Entry
Data entry is the procedure of feeding data into spreadsheets and database. It can either be done automatically by a machine that keeps on adding data in the database, or m...