FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
Home / Data Back Up / Import Data / Question No: 173

How to use LOAD DATA INFILE statement?

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?

Answer No: 173

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:

INTO TABLE table_name
[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:'','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);

Related MySQL FAQs to the Above FAQ

How-to-use-mysqlimport-client How to use mysqlimport client?

How-to-import-data-from-Microsoft-Excel-file-into-table-with-LOAD-DATA-INFILE-statement How to import data from Microsoft Excel file into table with LOAD DATA INFILE statement?

How-to-import-data-from-text-file-into-table-with-LOAD-DATA-INFILE-statement How to import data from text file into table with LOAD DATA INFILE statement?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: | | | |

© 2021
All rights reserved.