How Can I Import Specific Columns from a CSV to MySQL Using LOAD DATA INFILE?

Importing Specific Columns from CSV to MySQL Using LOAD DATA INFILE

Problem:

You have a CSV file with numerous columns and need to selectively import only specific ones into a MySQL database using the LOAD DATA INFILE command.

Solution:

To load only selected columns into MySQL, follow these steps:

1. Use FIELDS TERMINATED BY and LINES TERMINATED BY: Specify the delimiter character used in the CSV file for separating columns and the end-of-line character for rows, respectively.
2. Declare Variables: Declare variables using the @ syntax to represent the selected columns and their corresponding column names in the target table.
3. Use SET clause: After importing the data, use a SET clause to assign values from the variables to the desired columns in the table.

Example:

Run this command to import selected columns from a CSV file named file.csv into a table named t1:

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 representing the selected CSV file columns, while name and id are the corresponding column names in the t1 table.

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