How to Import CSV Data into MySQL with Selective Column Mapping and Skipped Columns?

Importing CSV Data into MySQL Table with Selective Column Mapping

When importing a CSV file with columns that exceed or do not match the table structure, it may be necessary to skip certain columns during the import process. In this case, we aim to skip columns 9 and 10 from a CSV file and map column 11 to column 9 in the MySQL table.

To skip columns during the LOAD DATA INFILE operation, MySQL provides a mechanism to assign unwanted values to a user-defined variable and then ignore that variable altogether in the column mapping.

The following modified example demonstrates this technique:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
(col1, @dummy1, col2, @dummy2, col3, col4, col5, col6, col7, col8, @dummy3, col11)

In this example, the @dummy1, @dummy2, and @dummy3 user-defined variables are created to store unwanted data from columns 9, 10, and 11, respectively. However, column 11 is then explicitly assigned to column 9 in the table definition.

By utilizing this approach, the system skips columns 9 and 10 from the CSV file and successfully maps columns 1-8 and 11 to the corresponding columns in the MySQL table.

The above is the detailed content of How to Import CSV Data into MySQL with Selective Column Mapping and Skipped Columns?. For more information, please follow other related articles on the PHP Chinese website!