I have a CSV file which contains 10 columns. I want to select only some columns from that file and load them into a MySQL database using the LOAD DATA INFILE command.
10.5k 8 8 gold badges 59 59 silver badges 111 111 bronze badges asked Nov 17, 2010 at 8:14 Ugesh Gali Ugesh Gali 2,132 4 4 gold badges 20 20 silver badges 24 24 bronze badgesLoad 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.
answered Nov 17, 2010 at 8:39 21k 67 67 gold badges 111 111 silver badges 136 136 bronze badgesI run your answer in mysql and appear an error: ERROR 1148 (42000): The used command is not allowed with this MySQL version .
Commented Feb 2, 2016 at 11:13 @shgnInc refere this stackoverflow.com/questions/16285864/… Commented Feb 2, 2016 at 12:21What if i have 100 columns and I just want to import 2 columns, then should I write (@col1, @col2, . @col100) set name=@col4, id-@col2; or there is an easy way?
Commented Dec 24, 2016 at 4:56@Dharma A python 3 program to print 100 column names for i in range(1,100): print("@column",i,",",end="",sep="")
Commented May 15, 2018 at 15:38 Note: add "IGNORE 1 LINES" to ignore the headers Commented Jan 12, 2021 at 15:37Specify 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.