Geek Answers Handbook

MySQL imports CSV data - ignores some csv columns

I have several CSV files that I want to upload to my database, but the CSV file contains many more columns than in my database. How to import only selected columns from a CSV file into my database?

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

I planned 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 this fills rows B, N, and S with the values ​​of columns A, B, and C, and not with the values ​​of columns B, N, and S as I wanted.

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

+7
import mysql csv
source share
1 answer

You need to change --columns=B,N,S and add options to skip all columns that you don't need.

For example, to use the 1st, 4th, and 7th columns, use:

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

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

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

+14
source share

More articles:


All Articles