How to Import Specific Columns from a CSV File into a MySQL Database?

Importing Specific Columns from CSV File to MySQL Database Using LOAD DATA INFILE

When faced with a CSV file containing multiple columns and the need to selectively load only specific ones into a MySQL database, the LOAD DATA INFILE command proves invaluable. Let's delve into how to utilize this command effectively.

Specific Column Loading with LOAD DATA INFILE

To load only selected columns from the CSV file, specify the desired columns within the FIELDS TERMINATED BY and LINES TERMINATED BY clauses. For instance, suppose you have a CSV file with 10 columns, and you wish to import only columns 1, 2, 3, and 4. The following syntax would accomplish this:

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 example, @col1, @col2, @col3, and @col4 are variables used to store the corresponding columns from the CSV file. The subsequent set clause maps @col4 to the name column and @col2 to the id column in the destination table.

By utilizing this technique, you can selectively load only the necessary columns from your CSV file into your MySQL database, saving time and ensuring data integrity.

The above is the detailed content of How to Import Specific Columns from a CSV File into a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!