How to Import Specific Columns from a CSV File into MySQL Using LOAD DATA INFILE?

Inserting Selective Columns from a CSV File into MySQL Using LOAD DATA INFILE

In this scenario, you have a CSV file with numerous columns, and you need to selectively load specific columns into a MySQL database using the LOAD DATA INFILE command. Here's how you can accomplish this:

Solution:

Utilize the LOAD DATA INFILE command, specifying the target columns as variables while excluding the columns you do not wish to load. The following syntax will guide you:

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 command:

• @col1,2,3,4 represent variables that will hold the values from the selected CSV file columns. You can customize these variables to match the columns you wish to load.
• name and id are examples of the columns in your table t1 where the data will be inserted. Adjust them to match your table schema.
• LOCAL specifies that the CSV file is located on the local path.
• file.csv is the name of your CSV file.
• FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' indicates that commas separate the fields and newlines separate the rows in your CSV file. Adjust these delimiters if necessary.

By using this command, you can selectively insert the desired columns from your CSV file into the MySQL table t1.

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