6

I'm using the following LOAD DATA LOCAL INFILE query to read data from a file and insert it into a table. Everything works great as is. However I need to modify the query to support one additional requirement.

I need to add an extra column to the data. Essentially my raw data does not include a date field and I need to include a column where I can add the date the data was imported. The file will be automatically imported once a day and I need to add that date to the data.

Is there a way to add a column of data when using LOAD DATA LOCAL INFILE? I can easily capture today's date, but I don't know how to add it to the query below.

$insert_query = LOAD DATA LOCAL INFILE 'C:/mydata.csv' 
INSERT INTO TABLE myTable
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(Column1,Column2,Column3,Column4);

$db->setQuery($insert_query);
$res = $db->query();

Any suggestions will be greatly appreciated!!! Thanks!

mb87
  • 119
  • 1
  • 10

2 Answers2

14

You can add a SET clause to the end of the query, e.g.

LOAD DATA LOCAL INFILE 'C:/mydata.csv' 
INSERT INTO TABLE myTable
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES 
(Column1,Column2,Column3,Column4)
SET datetime = NOW();
Brian
  • 15,599
  • 4
  • 46
  • 63
  • Either answer works, but this one works best for me. I'm always super impressed by the quality of the answers in Stackoverflow. Thanks! – mb87 Apr 04 '14 at 18:48
  • Is there any way to modify data present in csv file? e.g. user can upload csv with country like `INDIA` but in database I need to store its id like `91` – RN Kushwaha Apr 04 '16 at 11:38
  • @RNKushwaha I think you can do this by including a variable in the column list, like `...,Column4,@Country` and then doing something with that variable in your SET clause, like `SET datetime = NOW(), Column5 = CASE @Country WHEN 'INDIA' THEN 91 END CASE` – Brian Apr 04 '16 at 16:06
6

Rather than add this field to the query, it would be easier to add it to the table:

ALTER TABLE myTable
    ADD COLUMN datetime_entered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Any insertion will then automatically have a timestamp of when the record was entered.

Tim Burch
  • 1,088
  • 7
  • 9