MySQL import CSV data - ignore some csv columns

Question

Hi guys,

I have a couple of CSV files I want to load into my database, but the CSV file contains many many more columns than in my database. How do I import only selected columns from the CSV file into my database?

For arguments sake, let's say the CSV contains a header row with the column titles A to Z, and then two million rows with values for columns A to Z. Let's say my table myTest contains B, N and S, so I only want to import column B, N and S from the CSV file into myTest.

I was planning to do:

mysqlimport --local --columns=B,N,S --ignore-lines=1 --delete --default-character-set=latin1 --fields-optionally-enclosed-by=\" --fields-terminated-by=\, --lines-terminated-by=\r\n myDb myTest.csv

But that fills row B,N and S with the values of column A, B and C, not with the values of column B, N and S like I wanted.

Any suggestions how I can make it import only B, N and S?

Cheers

Nik

Answer 1

You need to alter the --columns=B,N,S and add parameters in order to skip all the columns you do not need.

For instande, in order to use 1st, 4th and 7th column use:

--columns=B,@x,@x,N,@x,@x,S

This will send the 2nd, 3rd, 5th and 6th column to parameter @x.

Ref: http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html