Mysql 'LOAD DATA LOCAL INFILE' specify column's value depending on file

Question

I am stuck in a situation where I have to read data from 3 separate large CSV files and store it in MySQL database.

csv file columns:

total_rev,monthly_rev,day_rev

Database table's columns:

total_rev, monthly_rev, day_rev, from

I am able to insert data in my table by using following query:

LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'  
INTO TABLE revenue_table  
FIELDS TERMINATED BY ','   
LINES TERMINATED BY '\n'  
(total_rev,monthly_rev,day_rev);

But I am stuck in a situation where I want to add hard coded parameter in the "Load Data Local" query to add from's value depending upon the file.

so at the end my table will contain records like:

total_rev, monthly_rev, day_rev,  from
  11,       222,          333,    file1
  22,        32,          343,    file1
  11,        22,          333,    file1
  11,        22,           33,    file22
  11,        22,           33,    file22

How can I specify this file1, file22 value's in above query ?

spencer7593 · Accepted Answer · 2016-10-07 13:51:02Z

2

Add a SET clause to the LOAD DATA statement, to assign a value to the (unfortunately named) from column:

 LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'  
 INTO TABLE revenue_table  
 FIELDS TERMINATED BY ','   
 LINES TERMINATED BY '\n'  
 (total_rev,monthly_rev,day_rev)

 SET `from` = 'file1'
 ;

Note that it's also possible to load the fields from the file into user-defined variables and/or reference user-defined variables in the SET clause.

 SET @file_name = 'myfilename'
 ;

 LOAD DATA LOCAL INFILE '/home/datauser/Desktop/working/revenue_2016_10_06.csv'  
 INTO TABLE revenue_table  
 FIELDS TERMINATED BY ','   
 LINES TERMINATED BY '\n'  
 ( @total_rev
 , @monthly_rev
 , @day_rev
 )
 SET `total_rev`   = @total_rev
   , `monthly_rev` = @monthly_rev
   , `day_rev`     = @day_rev
   , `from`        = @file_name
 ;

If we had fields in the file we want to skip, not load into the table, we can use a user-defined variable as a placeholder. We can also use expressions in the SET clause, which allows us to leverage some very useful MySQL functions for some manipulation... IFNULL, NULLIF, STR_TO_DATE, CONCAT, etc.

edited Oct 7, 2016 at 13:51

answered Oct 7, 2016 at 13:45

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hriziya Over a year ago

Is this possible to use LOAD DATA LOCAL INFILE @file_name syntax?

spencer7593 Over a year ago

@hriziya: i don't think that's a valid usage of a user-defined variable; a udv can supply a a value in a SQL statement; a udv can not supply n identifier (like a table name or column name) or a keyword (like FROM or ASC). We could give it a whirl and see what MySQL does if we use a user-defined variable in place of the file location. (I suspect we'll get an error, but maybe it will work.)

hriziya Over a year ago

Thanks, Yes, I tried that already, and got an error when I tried to supply variable as filename

Collectives™ on Stack Overflow

Mysql 'LOAD DATA LOCAL INFILE' specify column's value depending on file

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related