SQL Data Import Methods

written by: George Ionescu; article published: year 2007, month 12;

In: Root » Computers and technology » Databases

  Share  
|
  PL  |  NL  |  FR  |  ES  |  PT  |  IT  |  DE  |  DK  |  NO  |  SE  |  FI  |  GR  |  JP  |  CN  |  KR  |  RU  |  AE


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.

Share

Disclaimer

1) E-articles is not responsible for the information contained by this article as well for any and all copyright infringements by authors and writers. E-articles is a free information resource. If you suspect this article for any copyright infringement, please read the terms of service and contact us or use the "Report this article" button on this page to investigate the problem.
2) E-articles is not responsible for inaccuracies, falsehoods, or any other types of misinformation this article may contain and will not be liable for any loss or damage suffered by a user through the user's reliance on the information gained here.