如何使用LOAD DATA INFILE将CSV文件中的选定列插入到MySQL数据库中

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列的CSV文件。我只想从该文件中选择一些列，并使用load DATA INFILE命令将它们加载到MySQL数据库中。

4 个解决方案

#1

Load data into a table in MySQL and specify columns:

将数据加载到MySQL中的表中，并指定列:

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.

@col1、2、3、4是保存csv文件列的变量(假设4)名称，id是表列。

#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.

只需替换column1, column2，等等。使用您的列名，并将@dummy anwhere放置在您想要忽略的CSV中。

Full details here.

全部细节。

#3

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

在load data infile语句中指定CSV中的列的名称。

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.

您需要注意的是，您有一个CSV文件(filename.csv)，每行包含两个值。否则请提及。我有不同的解决方案。

Thank you.

谢谢你！

#4

Example:

例子:

contents of the ae.csv file:

ae的内容。csv文件:

"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:

只适用于第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

#1