LOAD DATA INFILE - MySQL

Overview

LOAD DATA INFILE is a powerful MySQL command that enables the efficient ingestion of data from delimited text files, known as input files, into MySQL tables. It is a highly effective method for importing large datasets and automating data loading processes.

Syntax

LOAD DATA INFILE '<input_file_path>'
INTO TABLE <table_name>
[FIELDS TERMINATED BY <character>]
[LINES TERMINATED BY <character>]
[OPTIONALLY ENCLOSED BY <character>]
[ESCAPED BY <character>]
[STARTING BY <rows>]
[IGNORE <rows>]
[CHARACTER SET <charset>]
[COLUMNS TERMINATED BY <character>]
[COLUMN <column_index> <datatype>] [AS <column_name>]

Options/Flags

| Option/Flag | Description | Default Value |
|—|—|—|
| FIELDS TERMINATED BY | Specifies the delimiter separating data fields. | None |
| LINES TERMINATED BY | Specifies the line terminator character. | ‘\n’ (newline) |
| OPTIONALLY ENCLOSED BY | Optionally enclose data fields with a specific character. | None |
| ESCAPED BY | Specifies the character used to escape special characters within the input file. | ” (backslash) |
| STARTING BY | Skips processing the first specified number of rows in the input file. | 0 |
| IGNORE | Skips processing the first specified number of rows in the input file as header information. | 0 |
| CHARACTER SET | Specifies the character set of the input file. | utf8mb4 |
| COLUMNS TERMINATED BY | Specifies the delimiter separating columns. | \t (tab) |
| COLUMN | Specifies the data type and optional column name for a specific column. | Inferred from input file |

Examples

Example 1: Importing a CSV file

LOAD DATA INFILE 'customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email, phone)

Example 2: Importing a tab-delimited file with escaped quotes

LOAD DATA INFILE 'products.txt'
INTO TABLE products
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
(id, product_name, price, description)

Common Issues

  • Data type mismatch: Ensure that the specified data types in the COLUMN option match the actual data types in the input file.
  • Malformed input file: Check that the input file is properly formatted and meets the specified delimiters and character set.
  • Insufficient privileges: Verify that the user has the necessary privileges to execute the command on the specified table.

Integration

LOAD DATA INFILE can be combined with other MySQL commands for advanced data import tasks:

  • WITH INTO OUTFILE: Exports data to a file before loading it into a table.
  • CREATE TABLE … SELECT … INTO OUTFILE: Creates a table based on a query and exports the results to a file for later import using LOAD DATA INFILE.
  • Shell scripting: Integrate LOAD DATA INFILE into scripts to automate data loading processes.
  • INSERT
  • SELECT INTO OUTFILE
  • IMPORT