Selective Import from CSV to MySQL

How would I import certain rows from a large csv file into a MySQL table. I know how to import all the data but my problem is that my csv file is too large and I don't need all the data that it has.

I only want to import rows where the "COLUMN_X"开发者_运维百科 has either of these values [VALID1, VALID2, VALID3] All other rows with invalid values for column_x should be ignored.

Can anyone help me do that? Thanks a lot.

You can't filter out rows with the LOAD DATA INFILE, so either filter these out by pre-processing the CSV file or load the data into a temporary table and insert the relevant rows into your main table, something like;

CREATE TEMP TABLE import LIKE my_main_table;
LOAD DATA LOCAL INFILE 'myfile.csv' into import;
INSERT INTO my_main_table SELECT * FROM import
                          WHERE column_x IN(VALID1, VALID2, VALID3)

You can use something like:

LOAD DATA INFILE 'myfile.csv'
INTO mytable
 (column1, @dummy, @dummy, column2, column3)

which would load only the 1st, 4th, and 5th columns into the database. But you'd need to know which position your "good" columns are ahead of time.

Solution from comments to mysql documentation:

CREATE TABLE your_table ( .....) 
PARTITION BY LIST (COLUMN_X)
(
   PARTITION main VALUE IN (VALID1, VALID2, VALID3)
);
LOAD DATA INFILE 'your_file.csv' IGNORE INTO your_table .....

If you need to append data to an existing table, you can create a new temporary table and import data into it as described above, and then INSERT INTO old_table SELECT * FROM your_table;

LOAD DATA INFILE 'filename' IGNORE
INTO TABLE `table` (field1, field2, @pk, field3, @columnx)
SET pk = IF (@columnx IN ('VALID1', 'VALID2', 'VALID3'), NULL, 'key'),
    COLUMN_X = @columnx

• IGNORE ignores rows if a duplicate key is found in the CSV.
• (field1, field2, @pk, field3, @columnx) is the mapping of CSV columns to values. Specifically: the first field of the CVS goes into column field1, the second field of the CVS goes into column field2, the thirs field of the CSV goes into variable @pk, etc.
• pk = Sets the column named pk to whatever the result of the expression following the = is. In this case, it sets the column pk to NULL if one of the valid values are encountered in the fifth column of the CSV. Otherwise it sets that column to 'key'

This works if pk is the column that holds the primary key, the table already has a record with 'key' as primary key and the column with the primary key is set to auto_increment.