CODEWITHSUNDEEP
mysqldatabasecsv
amu03
amu03

Reputation: 361

Load csv file into mysql gives error when the value is in timestamp

Can anyone help?

I have a csv file that has timestamp column, 'created_at' like 1594960002.

Now, I loaded this csv file to the table and it gave me warnings for every rows.

Data truncated fo column 'created_at' at row xx.

Also I could see that loaded value was turned into 0000-00-00 in the table.

To test, I changed 'created_at' value in csv file to datetime value. something like '2020-02-02'.

When I loaded this file, error was gone.

I am not sure why I got the warning in the first case, because 'created_at' column in the table is defined as TIMESTAMP type. Do I still need to add datetime value even if it's defined as timestamp??

Upvotes: 1

Views: 299

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You must convert the number to a timestamp Here a Sample:

The CSV file

root@localhost:~# cat imp.csv
1,"Bernd",61,"DE11",1594960002
2,"Peter",22,"AB12",1594960003
3,"Alex",37,"XX54",1594960053
root@localhost:~#

Truncate table for test

MariaDB [bernd]> truncate users;
Query OK, 0 rows affected (0.02 sec)

MariaDB [bernd]>

Import the CSV an show the table

MariaDB [bernd]> LOAD DATA LOW_PRIORITY LOCAL INFILE '/root/imp.csv' INTO TABLE users
    -> CHARACTER SET utf8
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '"'
    -> (`id`,
    -> `name`,
    -> `age`,
    -> `contact_id`,
    -> @created_at)
    -> SET created_at = FROM_UNIXTIME(@created_at);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [bernd]> select * from users;
+----+-------+------+------------+---------------------+
| id | name  | age  | contact_id | created_at          |
+----+-------+------+------------+---------------------+
|  1 | Bernd |   61 | DE11       | 2020-07-17 04:26:42 |
|  2 | Peter |   22 | AB12       | 2020-07-17 04:26:43 |
|  3 | Alex  |   37 | XX54       | 2020-07-17 04:27:33 |
+----+-------+------+------------+---------------------+
3 rows in set (0.00 sec)

MariaDB [bernd]>

Upvotes: 1

Related Questions