How Can I Skip Columns During a CSV Import into a MySQL Table?

Skipping Columns During CSV Import into MySQL Table

Importing data from a CSV file into a MySQL table is a common task. However, sometimes you may encounter situations where you need to skip certain columns. This article provides a solution to skip the unwanted columns and map the remaining data correctly.

Scenario

Consider a CSV file with 11 columns and a MySQL table with 9 columns. The goal is to map columns 1-8 from the CSV file directly to the first 8 columns of the MySQL table. Columns 9 and 10 in the CSV file need to be skipped, and Column 11 should be mapped to Column 9 in the MySQL table.

Original Command

The initial SQL command used, which is shown below, simply maps the first 9 columns of the CSV file to the 9 columns in the MySQL table.

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'

Solution

To skip columns during the import, you can utilize a technique described in the MySQL documentation. By assigning the unwanted columns to user variables within the LOAD DATA statement, you can effectively discard them. The modified command below demonstrates this technique:

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

In this improved command, the unwanted columns (9 and 10) are assigned to the user variable @dummy and discarded during the import process. As a result, the remaining columns are correctly mapped as desired: columns 1-8 from the CSV file are mapped to the first 8 columns of the MySQL table, whereas column 11 is mapped to column 9 of the MySQL table.

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