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

Question

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.

Bushy

Answer 1

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);

Tomas

FYI: I had to add the (column1, @dummy, column2... field statement to the end of my example SQL. Initially I had added it directly after the table name as shown the above example but I kept getting a MySQL error. – Bushy

can any one tell me how can i ignore the @dummy – Kummerbund

You need at least FIELDS TERMINATED BY ',' to avoid "Row 1 doesn't contain data for all columns" – Allocate

Also add IGNORE 1 LINES if your CSV file has a header row. – Allocate

@StevePitchers what about what this suggests serverfault.com/questions/96401/… which is round brackets after IGNORE 1 LINES where within the round brackets all the columns are listed? – Bagby

Answer 2

step1.deal with awk.

cat file.txt |awk -F',' '{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);

Towandatoward

I like the use of awk here in showing the beauty of chaining multiple tools together. – Keister

can any one tell me how can i ignore the @dummy – Kummerbund

Need -F ',' switch for awk command for comma delimiter. – Reversible

Answer 3

@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.

Grange

Answer 4

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'

Grange