Additional languages
« 13.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
13.2.6 REPLACE Syntax »
Section Navigation      [Toggle]

13.2.5. LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The filename must be given as a literal string.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.7, “SELECT Syntax”.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both statements. Both clauses are optional, but FIELDS must precede LINES if both are specified.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 7.2.17, “Speed of INSERT Statements”.

The character set indicated by the character_set_database system variable is used to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. Beginning with MySQL 5.1.17, if the contents of the input file use a character set that differs from the default, it is possible (and usually preferable) to use the CHARACTER SET clause to specify the character set of the file.

Note that it is currently not possible to load data files that use the ucs2 character set.

As of MySQL 5.1.6, the character_set_filesystem system variable controls the interpretation of the filename.

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See Section 8.15, “mysqlimport — A Data Import Program”.

If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.

If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:

  • If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.

  • If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

    • If the filename is an absolute pathname, the server uses it as given.

    • If the filename is a relative pathname with one or more leading components, the server searches for the file relative to the server's data directory.

    • If a filename with no leading components is given, the server looks for the file in the database directory of the default database.

Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database: 

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

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

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 5.7.3, “Privileges Provided by MySQL”.

Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.

LOCAL works only if your server and your client both have been enabled to allow it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 5.6.4, “Security Issues with LOAD DATA LOCAL”.

On Unix, if you need LOAD DATA to read from a pipe, you can use the following technique (here we load the listing of the / directory into a table): 

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
find / -ls > /mysql/db/x/x &
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Note that you must run the command that generates the data to be loaded and the mysql commands either on separate terminals, or run the data generation process in the background (as shown in the preceding example). If you do not do this, the pipe will block until data is read by the mysql process.

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

  • If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 13.2.6, “REPLACE Syntax”.

  • If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

If you want to ignore foreign key constraints during the load operation, you can issue a SET FOREIGN_KEY_CHECKS=0 statement before executing LOAD DATA.

If you use LOAD DATA INFILE on an empty MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). Normally, this makes LOAD DATA INFILE much faster when you have many indexes. In some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE ... DISABLE KEYS before loading the file into the table and using ALTER TABLE ... ENABLE KEYS to re-create the indexes after loading the file. See Section 7.2.17, “Speed of INSERT Statements”.

For both the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements, the syntax of the FIELDS and LINES clauses is the same. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you specify no FIELDS clause, the defaults are the same as if you had written this: 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

If you specify no LINES clause, the defaults are the same as if you had written this: 

LINES TERMINATED BY '\n' STARTING BY ''

In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:

  • Look for line boundaries at newlines.

  • Do not skip over any line prefix.

  • Break lines into fields at tabs.

  • Do not expect fields to be enclosed within any quoting characters.

  • Interpret occurrences of tab, newline, or ‘\’ preceded by ‘\’ as literal characters that are part of field values.

Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

  • Write tabs between fields.

  • Do not enclose fields within any quoting characters.

  • Use ‘\’ to escape instances of tab, newline, or ‘\’ that occur within field values.

  • Write newlines at the ends of lines.

Backslash is the MySQL escape character within strings, so to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be interpreted as a single backslash.

Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix, and anything before it. If a line does not include the prefix, the entire line is skipped. Suppose that you issue the following statement: 

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

If the data file looks like this: 

xxx"abc",1
something xxx"def",2
"ghi",3

The resulting rows will be ("abc",1) and ("def",2). The third row in the file is skipped because it does not contain the prefix.

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names: 

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

When you use SELECT ... INTO OUTFILE in tandem with LOAD DATA INFILE to write data from a database into a file and then read the file back into the database later, the field- and line-handling options for both statements must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose that you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas: 

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

To read the comma-delimited file back in, the correct statement would be: 

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',';

If instead you tried to read in the file with the statement shown following, it wouldn't work because it instructs LOAD DATA INFILE to look for tabs between fields: 

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

The likely result is that each input line would be interpreted as a single field.

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotes. If lines in such a file are terminated by newlines, the statement shown here illustrates the field- and line-handling options you would use to load the file: 

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Any of the field- or line-handling options can specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

To read a file containing jokes that are separated by lines consisting of %%, you can do this 

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here: 

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM): 

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Note that occurrences of the ENCLOSED BY character within a field value are escaped by prefixing them with the ESCAPED BY character. Also note that if you specify an empty ESCAPED BY value, it is possible to inadvertently generate output that cannot be read properly by LOAD DATA INFILE. For example, the preceding output just shown would appear as follows if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote, which (erroneously) appears to terminate the field: 

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true regardless of whether OPTIONALLY is specified; OPTIONALLY has no effect on input interpretation.) Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and are interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotes are handled as shown here: 

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY controls how to write or read special characters. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on output:

  • The FIELDS ESCAPED BY character

  • The FIELDS [OPTIONALLY] ENCLOSED BY character

  • The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

  • ASCII 0 (what is actually written following the escape character is ASCII ‘0’, not a zero-valued byte)

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. The exceptions are an escaped ‘0’ or ‘N’ (for example, \0 or \N if the escape character is ‘\’). These sequences are interpreted as ASCII NUL (a zero-valued byte) and NULL. The rules for NULL handling are described later in this section.

For more information about ‘\’-escape syntax, see Section 9.1, “Literal Values”.

In certain cases, field- and line-handling options interact:

  • If LINES TERMINATED BY is an empty string and FIELDS TERMINATED BY is non-empty, lines are also terminated with FIELDS TERMINATED BY.

  • If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are read and written using a field width wide enough to hold all values in the field. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4, 6, 8, 11, and 20, respectively, no matter what the declared display width is.

    LINES TERMINATED BY is still used to separate lines. If a line does not contain all fields, the rest of the columns are set to their default values. If you do not have a line terminator, you should set this to ''. In this case, the text file must contain all fields for each row.

    Fixed-row format also affects handling of NULL values, as described later. Note that fixed-size format does not work if you are using a multi-byte character set.

Handling of NULL values varies according to the FIELDS and LINES options in use:

  • For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is ‘\’).

  • If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

  • If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  • With fixed-row format (which is used when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string. Note that this causes both NULL values and empty strings in the table to be indistinguishable when written to the file because both are written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format.

An attempt to load NULL into a NOT NULL column causes assignment of the implicit default value for the column's data type and a warning, or an error in strict SQL mode. Implicit default values are discussed in Section 11.1.4, “Data Type Default Values”.

Some cases are not supported by LOAD DATA INFILE:

  • Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both empty) and BLOB or TEXT columns.

  • If you specify one separator that is the same as or a prefix of another, LOAD DATA INFILE cannot interpret the input properly. For example, the following FIELDS clause would cause problems: 

    FIELDS TERMINATED BY '"' ENCLOSED BY '"'

  • If FIELDS ESCAPED BY is empty, a field value that contains an occurrence of FIELDS ENCLOSED BY or LINES TERMINATED BY followed by the FIELDS TERMINATED BY value causes LOAD DATA INFILE to stop reading a field or line too early. This happens because LOAD DATA INFILE cannot properly determine where the field or line value ends.

The following example loads all columns of the persondata table: 

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: 

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2: 

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time: 

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column: 

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Use of the column/variable list and SET clause is subject to the following restrictions:

  • Assignments in the SET clause should have only column names on the left hand side of assignment operators.

  • You can use subqueries in the right hand side of SET assignments. A subquery that returns a value to be assigned to a column may be a scalar subquery only. Also, you cannot use a subquery to select from the table that is being loaded.

  • Lines ignored by an IGNORE clause are not processed for the column/variable list or SET clause.

  • User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

When processing an input line, LOAD DATA splits it into fields and uses the values according to the column/variable list and the SET clause, if they are present. Then the resulting row is inserted into the table. If there are BEFORE INSERT or AFTER INSERT triggers for the table, they are activated before or after inserting the row, respectively.

If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.

If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in Section 11.1.4, “Data Type Default Values”.

An empty field value is interpreted differently than if the field value is missing:

  • For string types, the column is set to the empty string.

  • For numeric types, the column is set to 0.

  • For date and time types, the column is set to the appropriate “zero” value for the type. See Section 11.3, “Date and Time Types”.

These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.

TIMESTAMP columns are set to the current date and time only if there is a NULL value for the column (that is, \N), or if the TIMESTAMP column's default value is the current timestamp and it is omitted from the field list when a field list is specified.

LOAD DATA INFILE regards all input as strings, so you cannot use numeric values for ENUM or SET columns the way you can with INSERT statements. All ENUM and SET values must be specified as strings.

BIT values cannot be loaded using binary notation (for example, b'011010'). To work around this, specify the values as regular integers and use the SET clause to convert them so that MySQL performs a numeric type conversion and loads them into the BIT column properly: 

shell> cat /tmp/bit_test.txt
2
127
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
    -> INTO TABLE bit_test (@var1) SET b= CAST(@var1 AS SIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

When the LOAD DATA INFILE statement finishes, it returns an information string in the following format: 

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

If you are using the C API, you can get information about the statement by calling the mysql_info() function. See Section 24.2.3.35, “mysql_info()”.

Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see Section 13.2.4, “INSERT Syntax”), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can be used only as an indication of whether everything went well.

You can use SHOW WARNINGS to get a list of the first max_error_count warnings as information about what went wrong. See Section 13.5.4.31, “SHOW WARNINGS Syntax”.


User Comments

Posted by Ryan Bates on April 6 2005 9:06pm[Delete] [Edit]

LOAD DATA ... REPLACE replaces the entire row (every column) even when specifying to load data into only a number of those columns - this sets every column which you did not specify to its default value. I think a more useful feature would be to replace only the specified columns. Here is a work around:

1. Duplicate the table you want to change:
CREATE TEMPORARY TABLE duplicate_table SELECT * FROM original_table

2. LOAD DATA into the duplicate table (with REPLACE option if you want)

3. Update the specific columns in the first table with the new data from the duplicate table:
UPDATE duplicate_table, original_table SET original_table.column=duplicate_table.column WHERE duplicate_table.ID=original_table.ID

Note: This requires MySQL 4.0 or higher to use UPDATE with multiple tables.

This is the best way I've found so far. If someone knows of an easier way to do this, please let me know at: rbates at artbeats dot com.

Posted by Arcady Glush on May 20 2003 3:22am[Delete] [Edit]

Transfer data from MS Access into MySQL. Easy way:

If your MySQL table structure is identical to MS Access one, then create ODBC DSN pointing to MySQL: (Use MySQL ODBC 3.51 Driver DSN, if you don't have one download it from www.mysql.com). After that open Access Database, attach MySQL table using File/Get External Data/Link Tables. Choose file of type: ODBC Databases (last option) then follow the rules for attaching ODBC tables. After that it is quite simple, you can use Access query to insert data into the attached table(if you wish to skip some fileds), or, if you want to transfer entire table - simply highlight all the records in the opened Access source table, copy them to clipboard (Ctrl+C), then open attached MySQL table and select paste append option. The entire process can be done even quicker then reading the above instructions.

If your attached MySQL table has all the data marked #Deleted - just ignore that.

I have tested it with MySQL 4.0 and MS Access97, but I am pretty sure it will work with Access2000/2002

If you have questions or problems, just drop me an email.

glush@optushome.com.au or arcady_glush@national.com.au (Australia)


Posted by Raymond Michiels on November 11 2003 2:32am[Delete] [Edit]

To load binary objects (e.g. images) see "load_file". There is a good example there.

Posted by Juergen Fink on November 15 2003 7:34pm[Delete] [Edit]

Thanks so much for your tip:
“Transfer data from MS Access into MySQL. Easy way:”

It worked perfectly well.
To answer your question: Yes it workes also with Access 2000.
I did it using MySQL 4.0.16-nt on Windows 2000 Professional, Access 2000 and ODBC 3.51 Driver and it worked out nicely, as you assumed.

Again, thanks :-)

Posted by Christian Roche on November 24 2003 11:19pm[Delete] [Edit]

MySQL server 4.0.15 supports the "LOAD DATA LOCAL INFILE" feature, as does the MySQL ODBC Driver 3.15. However, in order for this to work, one must:

1 - Have a c:/my.cnf file on the server host containing a section
[mysqld]
...
local-infile=1

2 - Have a c:/my.cnf file on the client host containing a section
[odbc] (or [client])
local-infile=1

3 - Use the 65536 ODBC option ("Read options from C:\my.cnf") on the client side. Hence if you use Access you would configure your DSN with OPTION = 1 + 2 + 65536 = 65539.

Thanks to E. Warren for indicating this to me through the MyODBC mailing list.

C. Roche

Posted by Gordon Piggott on January 9 2004 2:28am[Delete] [Edit]

LOAD command is easiest done without any fancy script writing or code. Comma (or someother) delimited file needed

Just call up phpMySql
- select your database and table
- press the SQL button along top of screen
- then select - enter data from a textfile into table - at bottom of screen
- Then fill in the boxes

- Location of source file - this will be on your own machine - just track through and select it
- Fields terminated by - change to commas if required
- column names - if the order in which the source file differs to your table list - list the order in which the source file should load into the table, by named field seperated by commas. like ... fieldname1,fieldname2 etc
I find it easist to write this to a simple NotePad file. Then copy and past to the Column Names

I am just a newbie at this and was amazed at how straightforward it was

Gordon

Posted by Geoffrey Hoffman on December 20 2004 1:55pm[Delete] [Edit]

Use the lesser than symbol < 'pipe-data-in flag?' on the command line to bulk-insert SQL-statement text files (like the ones exported from phpMyAdmin, NOT CSV).

This is covered in a little more detail on http://www.mysql.com/doc/en/mysql.html, but kind of skipped over. It's really more powerful than LOAD DATA INFILE into db.table, yet not even mentioned here.

e.g.
shell> mysql
mysql> create database freshdb;
mysql> exit;
shell> mysql freshdb < /path/to/your_sql_file.txt

Load data infile is great for delimited text, but use the less-than as a command line argument when your text files are well-formed SQL/exported by phpMyAdmin. You can move a reasonble-size database from one box to another in a matter of seconds doing it this way.

One additional note: On Windows, you have to escape back slashes with a backslash, thus
C:\>mysql -u root -p freshdb < D:\\path\\to\\data\\file.sql.txt

Posted by [name withheld] on April 20 2004 11:44am[Delete] [Edit]

Whereas INSERT has a useful DEFAULT keyword, LOAD DATA INFILE has not. So for my dates, i sometimes have to allow NULL values, and use \N (NULL).

Posted by [name withheld] on May 23 2004 4:02pm[Delete] [Edit]

As a comment to the previous comment:

Instead of NULL, how about supplying the default value yourself? If you control the INFILE, you might put the default date value in, instead of NULL, right?

Posted by Michal Piasecki on June 8 2004 4:14pm[Delete] [Edit]

How load data infile shoud behave in that case?
(5.0.0-alpha-nt on w2k sp6)

There's no line separator in the textfile to load, and field data are populated one by one in the same sequential order.

Currently load data infile loads only one row because
there's no character(s) that separates lines (rows).

What do You think, how load data infile should behave:
1. load file sequentially trying to fit data read after first set of fields into next record?

2. statement sytax shoud allow to specify such case in more "aware" way?


Posted by Norman Santos on September 14 2004 6:53am[Delete] [Edit]

When uploading a CSV file exported from MS excel use this setup:

'LOAD DATA INFILE "filename.csv" INTO TABLE your_table FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED BY "\\r\\n";'

The { OPTIONALLY ENCLOSED BY """" } part will put fields, with comas in the right place in the table, instead of moving to the next field in the set and generally making a mess of things.

Posted by Antonio Angelo on October 20 2004 5:50pm[Delete] [Edit]

To avoid potential problems when loading data from Windows file, use a specific character to mark end-of-line such as pipe instead of the ambiguous \n or \r.
For example, let the file be:
1;"pelle"|
2;"carta"|

loading SQL will be:
LOAD DATA
INFILE 'C:/temp/data.csv'
INTO TABLE AN_RILEGATURE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '|'
;

Posted by [name withheld] on October 21 2004 11:43am[Delete] [Edit]

For Fixed-Row input, FIELDS TERMINATED BY and FIELDS ENCLOSED BY being null means they must be explicitly set to '', otherwise the defaults will be assumed and only the first field will be read. (Obvious perhaps, but overlooked by some.)

Posted by jparm56 on January 29 2005 9:49pm[Delete] [Edit]

To eliminate ERROR 13: Can't get stat of "filename.txt"
add local: mysql> load data local infile "filename.txt" into table tblname;

Posted by [name withheld] on March 3 2005 11:16pm[Delete] [Edit]

The syntax text above: "FIELD [TERMINATED BY '\t']" appears to disallow multi-character field terminators, such as "|<ft>|". However, multi-character field terminators are indeed allowed by LOAD DATA INFILE.

Posted by Roel Broersma (www.Gigaweb.nl) on March 31 2005 2:49pm[Delete] [Edit]

Quick checks after you've got some warnings (in MySQL 3.x it is not possible to see warning descriptions)

1) Find out if there are records Skipped.

2) Mostly when you got a warning and the record is Inserted correctly it is because of one of the following reasons:

A) Integers in the load_data_file.txt are nothing, i.e: 2 commas ,, (nothing) on a Integer place will give a warning, correct these to: ,0,
Even when setting a default value of 0 AND Allow_NULL for the Integer field will still give you warnings when inserting ,, (nothing). However, the data is imported

B) A carriage return at the end in the load_data_file.txt will give you as many warnings as columns. I.e.: When you have a carriage return at the end of your file and you want to use 'load data' to insert it in a table of 34 columns, you will get 34 warnings!

C) When you have a PRIMARY Key (i.e. Integer) in your table (even with Auto-increment!) and insert a NULL value, it will give you a warning. This is because a primary key (Auto-Increment) field can never be ALLOW_NULL. However the data is imported.

One other important thing to know: Warnings are on a PER field/record basis, NOT max. 1 per line.
So even if you have a text file with 1 line in it, you can still have 10 warnings when you do a 'load data'.

Note: For example for exporting/importing FROM DBII. Old DBII systems don't know the NULL Value, so you can only export a "NULL" instead (with quotes). However MySQL will insert NULL as well as "NULL" for both Integer,Character and Date fields. It won't even show you a warning :)

Posted by Paul Makepeace on April 4 2005 5:30pm[Delete] [Edit]

Here's how to replace a table from a CSV file. Typically CSV files come with a header row and frequently don't put quotes or whatever around fields where they're not needed; I've included what's necessary to deal with both.

load data infile '/full/path/your_table_data.csv' replace into table your_table fields terminated by ',' optionally enclosed by '"' ignore 1 lines;

In case it's not readable, that '"' is single-quote double-quote single-quote.

Posted by Are you mortal? Then prepare to die. on July 7 2005 5:41pm[Delete] [Edit]

When using LOAD DATA INFILE into an empty table, the query has two stages. The first dumps the data into the table, and the second creates the indexes.

If you do a 'SHOW PROCESSLIST' during the second stage you get the message (query state) 'Repair by sorting'. What this means really is 'CREATING INDEXES'.

This can be a bit confusing, and you could think that some how MySQL has got confused, and needs to recover your table.

It would be nice if the 'SHOW PROCESSLIST' state had clearer labels for the two stages of LOAD DATA INFILE.


Posted by Nathan Huebner on November 16 2005 6:56pm[Delete] [Edit]

I just recently had to figure out how to get a database with rows that look like this, into MySQL:

0001GB000020000300050006700070008000100020040050060007008

Believe it or not, there's like 9 columns in that row. The data from our vendor comes like this.

I am hoping someone finds this post useful. HOW I GOT IT INTO MYSQL:

1. When you setup your MySQL table, make sure it's a compatible format. If your LOAD DATA INFILE is spitting back 'too long for column', then there's something wrong. First check to make sure your Fixed-Width is correct, then check your Table Format. I had to use latin1_general_ci to get the data to load. It would not accept my LOAD DATA INFILE with utf8_general_ci. So that should save some hairs on your head when you pull them out from the frustration.

Here is the code to load the data:

LOAD DATA INFILE C:/bobsfile.txt' INTO TABLE mydatabase.mytable
FIELDS TERMINATED BY ''
FIELDS ENCLOSED BY '';

I hope this helps someone. It could have helped me, the documentation was poor on mentioning WILL NOT WORK IF YOU USE SPECIFIC TABLE FORMATS. a nice list of acceptable or prohibited formats would have been nice.

! :)

Nathan

Posted by Robert Lee on December 7 2005 2:33pm[Delete] [Edit]

I tried to import the ISO/DIS 639-3 code table (This lists languages) into MySQL. I got a data to long error, because the character set in the ISO table ISO, ISO-8859-1,
which should be latin1, did not work. By changing the character set to binary. I did get the table to laod.

CREATE TABLE ISO_639_3
(
ID CHAR(3) NOT NULL, # The three-letter 639-3 identifier
Part2 CHAR(3) NULL, # Equivalent 639-2/T identifier, if there is one
Part1 CHAR(2) NULL, # Equivalent 639-1 identifier, if there is one
Scope CHAR(1) NOT NULL, # I(ndividual), M(acrolanguage)
Type CHAR(1) NOT NULL, # A(ncient), C(onstructed),
# E(xtinct), H(istorical), L(iving)
Name VARCHAR(255) NOT NULL) # Language name (inverted form)
DEFAULT CHARACTER SET binary;
TRUNCATE ISO_639_3;
LOAD DATA INFILE 'ISO_639_3x.tab' INTO TABLE ISO_639_3
IGNORE 1 LINES;

Posted by Alex Fihman on January 10 2006 8:55pm[Delete] [Edit]

for windows this works fine:

lines terminated by 0x0d0a

Posted by Dori B on January 23 2006 4:57am[Delete] [Edit]

When exporting data to a tab-delimited text file generated from Microsoft Excel, the primary key (record number 1, 2, 3 and so on) is not automatically included. This might be a no-brainer to some, but kept tripping me up.

Trying to update a MySQL table with fields ID, SongTitle, Artist, Genre using a text file that only has SongTitle, Artist, Genre (no ID field) does not work unless you specify the Column names you're updating. Otherwise it looks for an ID field that doesn't exist in the text file and your data will end up a mess.

Posted by Jeff Orrok on April 6 2006 11:15pm[Delete] [Edit]

If LOAD DATA INFILE gives errcode 13 for a file that you know you are correctly specifying the path to and is other readable, then perhaps SELinux is blocking access because it does not have the correct type. Fix this with:

chcon -t mysqld_db_t /path/to/filename

Posted by Remco Wendt on June 28 2006 10:00pm[Delete] [Edit]

If you want to import a csv file containing a date field which is in european (%d-%m-%Y) notation. Here is what you can use:

load data infile '[path/to/csv]' into table [database].[table] FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n' (field1, @varDateField, field3) SET field2 = STR_TO_DATE(@varDateField, '%d-%m-%Y');

Field2 in the csv source (or the 2nd field) is in this case the field containing dates like 29-06-2006.

Posted by Javier Perez on October 20 2006 10:41pm[Delete] [Edit]

I want to share something about CSV format.
If you want to load a file in CSV format

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

make sure that there are no spaces between the commas and the double quotes. Maybe it is somewhere in there but I just spent 4 days debugging why not all of my fields where loading and that was the reason: There were spaces between the double quotes and the commas.

So, make sure that there are no spaces between the fields and you should be fine

Posted by Klas Karlsson on November 30 2006 7:07am[Delete] [Edit]

Note that LOAD DATA INFILE is not supported in Stored procedures or prepared statements (as of Mysql 5.0.x anyway) :(

This makes restoring a previously saved OUTFILE a bit complicated, if one wishes to do so without shellscripting that is.

/K

Posted by Ian Crossfield on December 7 2006 1:54pm[Delete] [Edit]

When importing from a fixed file format (FIELDS ESCAPED BY '' ENCLOSED BY '') tiny int, small int, medium int and int are assumed to be 4, 6, 8 and 11 characters in length (including one for the sign). Unsigned numbers are each one character smaller, i.e. 3, 5, 7 and 10 digits as there is no sign character.

Posted by Derrick Foong on December 22 2006 2:13am[Delete] [Edit]

The part about using FIELDS TERMINATED BY ',' and ENCLOSED BY '"' for CSV files doesn't seem to work when there are numeric fields in the file with , as the thousand separator.

Only way I got it to work was to format the fields to ####0.00 in Excel to remove the thousand separator.

There are posts in the forum that mentions that the MySQL numeric data types don't like , as thousand separators.

Posted by Paul Wehle on January 13 2007 12:00pm[Delete] [Edit]

LOAD DATA INFILE .... set field2='something' is only supported from MySQL 5.x

I had problems importing only a single row with LOAD DATA LOCAL INFILE, it resulted to corrupted and missing characters in console output (but looked fine in phpmyadmin). Solved the problem with generating a second row which is ignored on import but helps it.

/edit: @Remco - maybe you can edit your post stating that

Posted by Remco Wendt on January 9 2007 1:01pm[Delete] [Edit]

@Paul Wehle:

Sorry I posted this comment on the mysql 5.x documentation page, not knowing that comments are shared between the different documentation pages of the mysql versions.

Add your own comment.