How can I Skip Columns When Importing a CSV file into MySQL using LOAD DATA INFILE?

Skipping Columns in CSV Import with MySQL's LOAD DATA INFILE

When importing data from a CSV file into a MySQL table, it's not always necessary to map all the columns. Sometimes it's desirable to skip irrelevant columns or avoid mapping columns where data mismatch exists.

Problem:

Consider a CSV file with 11 columns:

col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

And a MySQL table with only 9 columns:

col1, col2, col3, col4, col5, col6, col7, col8, col9

The goal is to import columns 1 through 8 from the CSV into the first 8 columns of the MySQL table, but skip columns 9 and 10 and then map column 11 of the CSV to column 9 of the table.

Solution:

To skip columns in the CSV during import, use a @dummy variable in the LOAD DATA INFILE command:

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

In this command:

• @dummy1, @dummy2, @dummy3, and @dummy4 are user variables that will hold the values of the skipped columns.
• Columns 9 and 10 of the CSV file will be assigned to @dummy3 and @dummy4, respectively, so they'll effectively be discarded.

This solution allows for the selective mapping of CSV columns to table columns, even when they differ in number.

The above is the detailed content of How can I Skip Columns When Importing a CSV file into MySQL using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!