Menu
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?
832
One of the most common CSV import errors is that the file is simply too large. That can be caused by too many fields or records in the file, too many columns, or too many rows. The import error can be caused by limits set by the program using the file or the amount of available memory on the system.
The code is like this: LOAD DATA INFILE '/path/filename. csv' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (column_name3, column_name5); Here you go with adding data to only two columns(you can choose them with the name of the column) to the table.
The IGNORE number LINES clause can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip an initial header line containing column names: LOAD DATA INFILE '/tmp/test.
Answer: No, the order of the columns in the . csv file does not matter.
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
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
