MySQL import CSV data - ignore some csv columns

Question

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?

Anax · Accepted Answer · 2011-01-10 06:39:02Z

16

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

For instance, 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

edited Jan 10, 2011 at 6:39

answered Jul 13, 2010 at 7:47

Anax

9,4225 gold badges37 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

niklassaers Over a year ago

Thank you very much, that did the trick! I'd been reading that link all morning but hadn't thought of doing it this way :-)

Collectives™ on Stack Overflow

MySQL import CSV data - ignore some csv columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related