How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE

Solution 1:

Load data into a table in MySQL and specify columns:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

@col1,2,3,4 are variables to hold the csv file columns (assume 4 ) name,id are table columns.

Solution 2:

LOAD DATA INFILE 'file.csv'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3, ...)
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Just replace the column1, column2, etc.. with your column names, and put @dummy anwhere there's a column in the CSV you want to ignore.

Full details here.

Solution 3:

Specify the name of columns in the CSV in the load data infile statement.

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 only thing you have to take care is that you have a CSV file(filename.csv) with two values per line(row). Otherwise please mention. I have a different solution.

Thank you.

Solution 4:

Example:

contents of the ae.csv file:

"Date, xpto 14"
"code","number","year","C"
"blab","15885","2016","Y"
"aeea","15883","1982","E"
"xpto","15884","1986","B"
"jrgg","15885","1400","A"

CREATE TABLE Tabletmp (  
    rec VARCHAR(9) 
);

For put only column 3:

LOAD DATA INFILE '/local/ae.csv' 
INTO TABLE Tabletmp
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 2 LINES
(@col1, @col2, @col3, @col4, @col5)
set rec = @col3;


select * from Tabletmp;
    2016
    1982
    1986
    1400