How to Skip Columns During CSV Import to MySQL?

Skipping Columns in CSV Imports to MySQL

When importing a CSV file into a MySQL table using the LOAD DATA INFILE command, it's common to encounter a scenario where the number of columns in the CSV file differs from the columns in the target table. In such cases, skipping specific columns becomes necessary.

Problem:

Suppose you have a CSV file with 11 columns and a MySQL table with only 9 columns. You want to map columns 1-8 of the CSV file directly to columns 1-8 of the table, but skip the next two columns and map column 11 of the CSV file to column 9 of the table.

Solution:

To skip columns during the import process, you can utilize MySQL's feature of assigning values to user variables without inserting them into table columns. This is achieved by assigning the skipped columns to user variables, effectively ignoring their values.

Here's the modified SQL command that accomplishes the desired mapping:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
(column1, @dummy1, column2, @dummy2, column3, column4, column5, column6, column7, column8, @dummy3, column9);

In this modified command, two user variables (@dummy1 and @dummy2) are used to capture the values of the ninth and tenth columns in the CSV file respectively, but these values are not assigned to any table columns. As a result, they are effectively skipped, allowing column 11 of the CSV file to be correctly mapped to column 9 of the table.

The above is the detailed content of How to Skip Columns During CSV Import to MySQL?. For more information, please follow other related articles on the PHP Chinese website!