How to Efficiently Import Tab-Delimited Text Files into MySQL Using LOAD DATA INFILE?

Importing Data from Text Files into MySQL with LOAD DATA INFILE

Importing large datasets into MySQL from text files can be a common task when dealing with data analysis or integration scenarios. One of the most efficient ways to achieve this is by using the LOAD DATA INFILE command.

Command Structure

The LOAD DATA INFILE command follows a specific syntax:

LOAD DATA INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY 'delimiter'
LINES TERMINATED BY 'line_delimiter'
(column_list)

Importing Tab-Delimited Data

As mentioned in your question, your text file contains tab-delimited data. By default, LOAD DATA INFILE assumes tab as the field delimiter. Hence, the following command should import your data:

LOAD DATA INFILE '/tmp/text_file.txt'
INTO TABLE PerformanceReport;

Specifying Delimiters

If your file uses a different delimiter, you can specify it using the FIELDS TERMINATED BY clause. For example, if your data is comma-separated:

LOAD DATA INFILE '/tmp/text_file.txt'
INTO TABLE PerformanceReport
FIELDS TERMINATED BY ','

Setting Line Delimiter

The LINES TERMINATED BY clause specifies the end-of-line character. By default, it's 'n' for new line. If your file uses a different character, adjust accordingly.

LOAD DATA INFILE '/tmp/text_file.txt'
INTO TABLE PerformanceReport
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';  # If your file uses CRLF as line delimiter

Column Mapping

The (column_list) part allows you to specify the mapping between the columns in the text file and the columns in the database table. If the order and number of columns match, you can omit this part.

Example with Column Mapping

Suppose your text file has additional columns not present in the PerformanceReport table. You can specify only the relevant columns in your command:

LOAD DATA INFILE '/tmp/text_file.txt'
INTO TABLE PerformanceReport (id, date, value, keyword, cost, clicks)
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

Other Considerations

• Ensure that the user running the command has the necessary privileges to import data.
• The text file should be accessible to the database server.
• Large data imports may require adjusting memory settings for optimal performance.

The above is the detailed content of How to Efficiently Import Tab-Delimited Text Files into MySQL Using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!