How to use UTC_TIMESTAMP() in csv file

Question

I need a way to use the UTC_TIMESTAMP() function in a CSV file.

Currently I'm using the following Syntax;

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE my_table FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

I'm not quite sure what to do to keep the UTC_TIMESTAMP() from being enclosed in quotes. When entered in the database, I get the following result

"0000-00-00 00:00:00"

This was the only example I could find on stack overflow or on Google for converting a string to a MYSQL value.

MySQL CSV import: datetime value

Miguel Coder · Accepted Answer · 2017-06-11 23:12:36Z

I solved the problem by following the MySQL documentation on this page. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

About halfway down the page there is a section that shows you how to create variables for rows and then set table rows equal to native mysql functions or values assigned to those variables(Whichever you choose).

The example in the documentation that I'm referring to looks like this.

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

I fixed my problem by restructuring my code like this...

 LOAD DATA LOCAL INFILE '/path/to/file.csv'
 INTO TABLE veh_icodes FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 (id, vcode, year, make, model, body_style, tran_type, engine_cap, drive_train, doors, trim, created_by, updated_by, @created_at, @updated_at, active)
 SET created_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP;"

I hope this helps someone. :)

Collectives™ on Stack Overflow

How to use UTC_TIMESTAMP() in csv file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related