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

Question

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.

ArK · Answer 1 · 2015-09-24T10:30:12.207

134

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.

edited Sep 24 '15 at 10:30

answered Nov 17 '10 at 08:39

ArK

20,698
67
109
136

2

I run your answer in mysql and appear an error: `ERROR 1148 (42000): The used command is not allowed with this MySQL version`. – shgnInc Feb 02 '16 at 11:13
@shgnInc refere this http://stackoverflow.com/questions/16285864/how-can-i-correct-mysql-load-error – ArK Feb 02 '16 at 12:21
10

What 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? – Dharma Dec 24 '16 at 04:56
@Dharma A python 3 program to print 100 column names`for i in range(1,100): print("@column",i,",",end="",sep="")` – Agnel Vishal May 15 '18 at 15:38
2

Note: add "IGNORE 1 LINES" to ignore the headers – ThomasRones Jan 12 '21 at 15:37

score 40 · Answer 2 · answered Nov 13 '14 at 13:53

40

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.

answered Nov 13 '14 at 13:53

Ataboy Josef

2,087
3
22
27

2

probably the other answers are a bit outdated. this one worked for me with MySQL 5.6 – Fabio Napodano Jan 21 '15 at 13:55
Thank you for putting the full path to the file. For a moment, I was searching for the MySQL directory in which I have to put the csv file ! – Shrinath Oct 29 '15 at 20:34
1

This one works the best although the path has to be from the allowed secure areas. Run this command SHOW VARIABLES LIKE "secure_file_priv"; and then drop the csv file in this location – Ganesh Krishnan Jul 14 '17 at 00:44
1

Agree with @FabioNapodano. For my v5.7, I needed to put the column names at the end of the statement. – Anne Gunn Aug 23 '18 at 14:47
2

This doesn't answer the question, unfortunately. This requires there to be two columns in the .csv, and inserts them into two particular columns in the DB. The poster is requesting pulling 2 columns from a csv of 10. – Jeremy L. Mar 03 '19 at 08:05
Yes it worked for after adding the columns name at last. – prashant thakre Jul 07 '19 at 10:36
v5.7:It worked for me by adding the column name in the end of the code. Also I had to use LOAD DATA LOCAL INFILE – Sri436 Jan 16 '20 at 14:16

score 40 · Answer 3 · answered Nov 17 '10 at 08:23

40

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.

answered Nov 17 '10 at 08:23

Marc B

356,200
43
426
500

10

I get a syntax error when I run something like that. I have to put the `(field,names)` just before the semicolon at the end to get it to work. – Stephen Ostermiller Jan 22 '15 at 10:42
1

This doesn't scale unless you know all the csv columns coming in. – Charles Harmon Apr 17 '15 at 17:27
8

how the F is this +38 votes ? It's wrong SQL syntax. – John Jul 03 '18 at 13:51
add "IGNORE 1 LINES" to ignore the headers – Nuwan Abeynayake Jan 24 '22 at 09:03

score 19 · Answer 4 · edited Nov 08 '17 at 01:09

19

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

edited Nov 08 '17 at 01:09

elPastor

8,435
11
53
81

answered Apr 06 '16 at 16:17

Roberto Góes

778
1
13
20

1

Examples are always better – Pierre de LESPINAY Mar 20 '20 at 10:21

score 3 · Answer 5 · answered Feb 14 '19 at 10:44

if you have number of columns in your database table more than number of columns in your csv you can proceed like this:

LOAD DATA LOCAL INFILE 'pathOfFile.csv'
INTO TABLE youTable 
CHARACTER SET latin1 FIELDS TERMINATED BY ';' #you can use ',' if you have comma separated
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n'
(yourcolumn,yourcolumn2,yourcolumn3,yourcolumn4,...);

score 0 · Answer 6 · answered Jul 31 '18 at 15:24

For those who have the following error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

You can simply run this command to see which folder can load files from:

SHOW VARIABLES LIKE "secure_file_priv";

After that, you have to copy the files in that folder and run the query with LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

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

6 Answers6

Linked

Related