Home / Data Back Up / Import Data / Question No: 173

I want to use LOAD DATA INFILE statement to import my table data. Can you help me to explain, how to use LOAD DATA INFILE statement to import data into MySQL table?

The LOAD DATA INFILE statement is executed much like a query. It is used to import delimited text files into a MySQL table. Its generalized syntax follows:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE table_name
[FIELDS
       [TERMINATED BY 'character'] [[OPTIONALLY] ENCLOSED BY 'character']
       [ESCAPED BY 'character']
]
[LINES [STARTING BY 'character'] [TERMINATED BY 'character'] ]
[IGNORE number lines]
[(column_name, ...)]

Key Command Options of LOAD DATA INFILE

The LOAD DATA INFILE statement has wide array of options that make this feature so powerful. We can introduce each option like below:

• LOW PRIORITY: This option forces execution of the command to be delayed until no other clients are reading from the table.
• CONCURRENT: Used in conjunction with a MyISAM table, this option allows other threads to retrieve data from the target table while the command is executing.
• LOCAL: This option declares that the target INFILE must reside on the client side. If omitted,the target INFILE must reside on the same server hosting the MySQL database. When LOCAL is used, the path to the file can be either absolute or relative according to the present location.When omitted, the path can be absolute, local, or, if not present, assumed to reside in MySQL’s designated database directory or in the presently chosen database directory.
• REPLACE: This option results in the replacement of existing rows with new rows possessing identical primary or unique keys.
• IGNORE: Including this option has the opposite effect of REPLACE. Read-in rows with primary or unique keys matching an existing table row will be ignored.
• FIELDS TERMINATED BY 'character': This option signals how fields will be terminated.Therefore, TERMINATED BY ',' means that each field will end with a comma, like so:123,4567,2008-05-19 01:30:45,11.75,9.00,15.75
  The last field does not end in a comma because it isn’t necessary, as typically this option is used in conjunction with the LINES TERMINATED BY 'character' option. Encountering the character specified by this other option by default also delimits the last field in the file, as well as signals to the command that a new line (row) is about to begin.
• [OPTIONALLY] ENCLOSED BY 'character': This option signals that each field will be enclosed by a particular character. This does not eliminate the need for a terminating character. Revising the previous example, using the option FIELDS TERMINATED BY ','ENCLOSED BY '"' implies that each field is enclosed by a pair of double quotes and delimited by a comma, like so:"123","4567","2008-05-19 01:30:45","11.75","9.00","15.75"
• The optional OPTIONALLY flag denotes that character strings only require enclosure by the specified character pattern. Fields containing only integers, floats, and so on need not be enclosed.
• ESCAPED BY 'character': If the character denoted by the ENCLOSED BY option appears within any of the fields, it must be escaped to ensure that the field is not incorrectly read in. However, this escape character must be defined by ESCAPED BY so that it can be recognized by the command. For example, FIELDS TERMINATED BY ',' ENCLOSED BY '''ESCAPED BY '\\' would allow the following fields to be properly parsed:'user@example.com','Good opportunity! I\'ll join soon!','2008-05-20'
  Note that because the backslash is treated by MySQL as a special character, you need to escape any instance of it by prefixing it with another backslash in the ESCAPED BY clause.
• LINES: The following two options are pertinent to how lines are started and terminated,respectively:
• • STARTING BY 'character': This option defines the character intended to signal the beginning of a line, and thus a new table row. Use of this option is generally skipped in preference to the next option.
  • TERMINATED BY 'character': This option defines the character intended to signal the conclusion of a line, and thus the end of a table row. Although it could conceivably be any thing, this character is most often the newline (\n) character. In many Windows based files, the newline character is often represented as \r\n.
• IGNORE x LINES: This option tells the command to ignore the first x lines. This is useful when the target file contains header information.
• [(column_name,...)]: If the number of fields located in the target file does not match the number of fields in the target table, you need to specify exactly which columns are to be filled in by the file data. For example, if the target file containing sales information consists of only four fields, order_id, buyer_id, order_date, and order_amount whereas database table has more columns than this, the command would have to be written like so:
  

  LOAD DATA LOCAL INFILE "orders.txt" INTO TABLE orders (order_id, buyer_id, order_date, order_amount);