How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
MysqlCsvLoad Data-InfileMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
> 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);
Solution 2 - Mysql
step1.deal with awk.
cat file.txt |awk '{print $1,$2,$5...}'>new_file.txt
step2.load into mysql.
load data local infile 'new_file' into table t1(...)
the method below is simple,but not allowed in lower version of mysql.
LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
Solution 3 - Mysql
@deemi:
The only way to ignore the @dummy is by setting the field's Default to AUTO INCREMENT.
So you can skip the field and just code like this,
LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column2, column3, column4, column5);
//assumes that the fieldname column1 is set to AUTO INCREMENT by default.
Solution 4 - Mysql
I think there is one more change in the code:
The following SQL command:
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
-will probably result in an data truncation error.
So it is better to use LINES TERMINATED BY '\r\n' instead of LINES TERMINATED BY '\n'
SO the code will be:
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\r\n'