Additional languages
MySQL Query Browser :: 12 Migrating a Spreadsheet to MySQL :: 12.5 Loading the Data into a MySQL Database Table
12.6 Creating A Temporary Table of Members »
Section Navigation      [Toggle]

12.5. Loading the Data into a MySQL Database Table

To import the spreadsheet data we'll use the LOAD DATA INFILE syntax. Security considerations can sometimes make this a frustrating exercise, so as we go, we'll try to anticipate any problems that may arise.

Click on the Resultset1 tab and enter the following statement into the query text box (using a path appropriate to your circumstances): 

LOAD DATA INFILE “/home/peter/Documents/spreadsheet/data.tsv”
  INTO TABLE alldata;

Windows pathnames are also specified using forward slashes rather than backslashes. If you do use backslashes, you must double them.

To import a comma separated file on the Windows platform use the following syntax: 

LOAD DATA INFILE “C:/Documents and Settings/peter/My Documents/spreadsheet/data.csv” 
  INTO TABLE alldata 
  FIELDS TERMINATED BY “,”;

The default field terminator is a tab character so if you use a different terminator you must specify it as shown in the preceding statement.

There are other possible pitfalls when executing a LOAD DATA INFILE statement. The rules for using a relative path are a bit tricky so always specify the complete path to the file. Also, a data file must be readable by all. This is usually not an issue under Windows; on Unix operating systems, if you need to adjust the file permissions, you can readily do this using the GUI. To make a file world-readable from the command prompt type: 

shell> chmod 755 data.tsv

Finally, the user who is executing the LOAD DATA statement must have the FILE privilege. If you need to grant this privilege, log in as root and execute the command: 

GRANT FILE ON *.* 
  TO 'user'@'hostname'
  IDENTIFIED BY 'password';

You can do this from the command line or from within Query Browser.

Note

The FILE privilege is a global privilege and cannot be restricted to a specific database.

So far so good, but the syntax shown to this point only works if the text file is located on the same system as the server. If your MySQL server is remote, you must add the keyword LOCAL to the LOAD DATA INFILE syntax as in the following example: 

LOAD DATA LOCAL INFILE “/home/peter/Documents/spreadsheet/data.tsv”
  INTO TABLE alldata;

Using LOCAL is not much different syntactically but servers are sometimes started up with the ability to LOAD DATA LOCAL disabled. If the server supports LOCAL, you can start up the MySQL client with the --local-infile option. Another approach is to copy the text file to the server before executing the LOAD DATA statement.

Note

Further complications can ensue. For files created on a Windows system, you might have to add LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use these two characters as a line terminator. If you need to add this clause, it follows immediately after the table name or, if a FIELD TERMINATED BY clause is present, immediately after this clause.

If you run into problems and require more information about LOAD DATA INFILE refer to the manual http://dev.mysql.com/doc/refman5.0/en/sql-syntax.html.

Before you continue, paste the appropriate version of the LOAD DATA INFILE statement into the script window below the alldata table definition.

After executing this statement and loading the data you can check that it has been copied to the alldata table using Query Browser. To inspect the data, double click the alldata table and find the following statement in the query text box: 

SELECT * FROM alldata LIMIT 0,1000

Note

A LIMIT clause may not appear when using Query Browser under Windows.

Click the Execute button and you should be able to view the data in the query window.

You might want to review the integrity of the data again at this point. A visual inspection is fine but you might also want to automate the process with an SQL statement such as the following: 

SELECT * FROM alldata PROCEDURE ANALYSE();

(Note the spelling of ANALYSE.)

Among other things, this query shows actual minimum and maximum values for data in the various fields. If any of the maximum field length values equal the field length, then you have probably truncated data. Empty or NULL values in some fields may also indicate problems.