How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?

csv, load-data-infile, mysql

Solution

From Mysql docs:

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

Problem

I've got a CSV file with 11 columns and I have a MySQL table with 9 columns. The CSV file looks like: ``` col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 ``` and the MySQL table looks like: ``` col1, col2, col3, col4, col5, col6, col7, col8, col9 ``` I need to map the columns 1-8 of CSV file directly to the first 8 columns of the MySQL table. I then need to skip the next two columns in the CSV file and then map column 11 of CSV file to column 9 of MySQL table. At the moment I am using the following SQL command: ``` LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n' ``` But the above code maps the first 9 columns of CSV file to the 9 columns in the MySQL table.

Original source