How to Import Specific CSV Columns into MySQL using LOAD DATA INFILE?

Inserting Specific Columns from CSV to MySQL via LOAD DATA INFILE

In this scenario, you wish to selectively import specific columns from a CSV file into a MySQL database using the LOAD DATA INFILE command. To achieve this, follow these steps:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

In this statement, the following parameters are used:

• LOAD DATA LOCAL INFILE 'file.csv': Specifies the CSV file named 'file.csv' as the data source.
• INTO TABLE t1: Identifies the target MySQL table, 't1', where the data will be inserted.
• FIELDS TERMINATED BY ',': Indicates that the fields in the CSV file are separated by commas.
• LINES TERMINATED BY 'n': Specifies that each line in the CSV file represents a new row of data.
• (@col1,@col2,@col3,@col4): Defines variables (@col1, @col2, @col3, @col4) to hold the values of the selected columns in the CSV file.
• set name=@col4,id=@col2: Assign the values of @col4 and @col2 to the 'name' and 'id' columns in the target table, respectively.

This query allows you to select only the specified columns from the CSV file and insert them into the corresponding columns in the MySQL table.

The above is the detailed content of How to Import Specific CSV Columns into MySQL using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!