3.3.3 Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose your pet records can be described as shown here. (Observe that MySQL expects dates in 'YYYY-MM-DD' format; this may be different from what you are used to.)

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement.

You could create a text file `pet.txt' containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N. For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

To load the text file `pet.txt' into the pet table, use this command:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file `pet.txt' properly.

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose Diane gets a new hamster named Puffball. You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Note that string and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.

From this example, you should be able to see that there would be a lot more typing involved to load your records initially using several INSERT statements rather than a single LOAD DATA statement.

User Comments

for some reason it seems that when using files
to input data into tables, there must always be
a tab after the \N (for null).

this is true even when the null item is the last
item on the row. so, if you're getting 0000-00-
00 for the date in this example, try putting an
extra tab at the end of the line.

load data has been disabled by default. use the
--local-infile option when starting mysql:

shell$ mysql --local-infile -p menagerie

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE
pet;
Query OK, 8 rows affected (0.09 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 2


Now it works.

I was desperately stuck because using files to
input data did not work ("SELECT * FROM ..."
always resulted in a deranged table). Jae K's
hint to put an extra tab after "\N"-entries in
the end of a row helped a bit, so I put an extra
tab in the end of each row, even if the last
entry was not "\N" - and this works! I get
some "warnings" when loading such a file, but the
result of "SELECT * FROM ..." now is as expected.

I fixed error that says used command not supported
by this version and loaded my data file by doing the
following:

mysql> --local-infile (May not be necessary)
mysql> use database_name
Database changed
mysql> LOAD DATA INFILE "tabledata.txt" INTO
TABLE table_name;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Deleted: 0 Skipped: 0 Warnings: 7

Note: do NOT WRITE LOCAL in the previous load
data command as instructed in the sql manual.

Note: Place the data file in the folder with the name
of the database that has the table you want to load
data into; Otherwise, specify the path of the data file
in the command.

For this command:

Load data local infile "pet.txt" into table pet;

didn't work and give an error message saying that the "used command is not allowed in this version of Mysql"

However, it worked when I took out the "local" from my command.

Found you need to add \\ in filenames to get files to load from different paths.
LOAD DATA INFILE "C:\\mypath\\my_data.txt" INTO TABLE my_data;

With Apple OS X: Use the terminal's drag and drop capability to insert the full path of the import file. This cuts down on the amount of typing, if you don't want deal with adding the import file into MySQL's data folder.

example:
%mysql --local-infile -u <username> -p <DatabaseName>
Enter password:<password>
mysql>load data local infile '<drag input file here>' into table <TableName>;

Is it possible to input data using LOAD DATA LOCAL INFILE from any flat file other than .txt?

I tried it for ".dat" and ".h"!! It should work with any file extension (as long as the character format in that file is compatible with mySQL's default char set).

-Kaushik

use ' ' instead of " ", so the command will look like this:
LOAD DATA INFILE 'c:\\pet.txt' INTO TABLE pet;

I could not get the LOAD DATA LOCAL INFILE, working until i removed the LOCAL from the command.

I have tried everything I could think to to load the "local" file. Nothing worked. I was able to load the file if it was the server (and remove the 'LOCAL') but that defeats what I want to do. Maybe it just doesn't work in a Windows environment. Sounds like a bug to me.

The same on Mac OS X, "LOAD DATA LOCAL INFILE ..." won't work, only "LOAD DATA INFILE ..." works.

If you want to load data from a Microsoft Excel file here is what you should do:

Basically you just have to save your excel file as a tab separated text file and then upload that text file to your webserver and do the following:

Linux Example:
LOAD DATA INFILE '/usr/local/apache/htdocs/yourdirectory/nameoffile.txt' INTO TABLE mytable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

Windows Example:
LOAD DATA INFILE 'c:/apache/htdocs/mytextfile.txt' INTO TABLE mytable FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

make sure you use single quotes, always forward slash the file address, just insert data not cell names


-Sami

mysql> LOAD DATA INFILE '/path/to/your/tblpet.txt' INTO TABLE tblpet;
ERROR 13: Can't get stat of '/path/to/your/tblpet.txt' (Errcode: 13)
NOTE:
All directories within the path to the target destination must be flagged 755
or higher, otherwise mysql will not be allowed to access destination file.
So if you run into this (ERROR 13) simply check each directory within the path
and make sure of it's settings.

Add your own comment.