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!
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();
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With