Loading a CSV into mariadb errs out as "invalid date format"

Question

I am trying to load CSV into a MariaDB . Having issues in transforming date .

Here is an example file and two tables ( emp , where dob is declared as a date column and emp_varchar , where dob is declared as varchar).

What am I doing wrong here?

Jane@dbserver1:~$ cat empdata.csv
100,John ,02/14/1955
200,Jane ,08/22/1980
MariaDB [emptest]>  LOAD DATA LOCAL INFILE 'empdata.csv'
    ->     INTO TABLE   emp
    ->     FIELDS TERMINATED BY ','
    ->     OPTIONALLY  ENCLOSED BY '&quot;'
    ->     LINES TERMINATED BY '\n'
    ->     IGNORE 0 ROWS
    ->     (
    ->                 eid,
    ->                 ename,
    ->                 dob
    ->  )
    ->      SET dob =  STR_TO_DATE(dob, '%m/%d/%Y');
Query OK, 2 rows affected, 4 warnings (0.001 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 4
MariaDB [emptest]>             show warnings;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'dob' at row 1                        |
| Warning | 1411 | Incorrect datetime value: '0000-00-00' for function str_to_date |
| Warning | 1265 | Data truncated for column 'dob' at row 2                        |
| Warning | 1411 | Incorrect datetime value: '0000-00-00' for function str_to_date |
+---------+------+-----------------------------------------------------------------+
4 rows in set (0.000 sec)
MariaDB [emptest]> select * from emp;
+------+-----------+------+
| eid  | ename     | dob  |
+------+-----------+------+
|  100 | John      | NULL |
|  200 | Jane      | NULL |
+------+-----------+------+
2 rows in set (0.000 sec)
MariaDB [emptest]>  LOAD DATA LOCAL INFILE 'empdata.csv'
    ->     INTO TABLE   emp_varchar
    ->     FIELDS TERMINATED BY ','
    ->     OPTIONALLY  ENCLOSED BY '&quot;'
    ->     LINES TERMINATED BY '\n'
    ->     IGNORE 0 ROWS
    ->     (
    ->                 eid,
    ->                 ename,
    ->                 dob
    ->  )
    ->      SET dob =  STR_TO_DATE(dob, '%m/%d/%Y');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [emptest]> select * from  emp_varchar
    -> ;
+------+-----------+------------+
| eid  | ename     | dob        |
+------+-----------+------------+
|  100 | John      | 1955-02-14 |
|  200 | Jane      | 1980-08-22 |
+------+-----------+------------+
2 rows in set (0.000 sec)
MariaDB [emptest]>

Ergest Basha · Answer 1 · 2024-11-21T20:30:28.797

From LOAD DATA INFILE

In the case of a variable, the SET statement can be used to preprocess the value before loading into the table.

If no variable is used during the LOAD DATA process, first the data is loaded into the table and then applies the SET clause.

Use,

LOAD DATA LOCAL INFILE '/var/lib/mysql-files/empdata.csv'
INTO TABLE   emp
FIELDS TERMINATED BY ','
OPTIONALLY  ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 0 ROWS
(eid, ename, @dob) 
SET dob = STR_TO_DATE(@dob, '%m/%d/%Y');

Tested on 10.6.15-MariaDB

MariaDB [crm_gesti]> LOAD DATA LOCAL INFILE '/var/lib/mysql-files/empdata.csv'
    -> INTO TABLE   emp
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY  ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 0 ROWS
    -> (eid, ename, @dob)
    -> SET dob = STR_TO_DATE(@dob, '%m/%d/%Y');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [crm_gesti]> select * from emp;
+------+-------+------------+
| eid  | ename | dob        |
+------+-------+------------+
|  100 | John  | 1955-02-14 |
|  200 | Jane  | 1980-08-22 |
+------+-------+------------+
2 rows in set (0.000 sec)
MariaDB [crm_gesti]> show create table emp;
CREATE TABLE emp (
  eid int(11) DEFAULT NULL,
  ename varchar(15) DEFAULT NULL,
  dob date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Loading a CSV into mariadb errs out as "invalid date format"

1 Answers1