MariaDB does not recognize a field name in a table when using load data infile

Question

MariaDB does not recognize a field name in a table when using load data infile

Navigation

#1 by (1 votes)

0

I have a small problem with MariaDB:

I have a table with several columns, one of which is called fecha_hora . When trying to insert data with load data infile , MariaDB tells me that it does not recognize this column. I deleted and created the table several times, and yet I still see the same error.

The code I am running is the following:

drop table if exists mi_tabla
create table mi_tabla (
    id              int unsigned not null auto_increment primary key,
    nombre_archivo  varchar(255),
    res             varchar(100),
    tipo            varchar(10),
    fecha_hora      datetime,
    unidades        int unsigned,
    fecha           date,
    hora            date,
);
-- La tabla se crea correctamente

delimiter $$
create or replace trigger trig_mi_tabla_bi before insert on mi_tabla
for each row
begin
    set new.fecha   = date(fecha_hora);
    set new.hora    = time(fecha_hora);
end $$
delimiter ;
-- La creación del trigger no muestra errores

load data infile 'c:/ruta/a/mi/archivo/mi_archivo.csv'
into table mi_tabla
fields terminated by ',' optionally enclosed by '"' 
lines terminated by '\r\n' ignore 1 lines
(res, tipo, fecha_hora, unidades);
-- Aquí es donde aparece el error:
-- ERROR 1054 (42S22): Unknown column 'fecha_hora' in 'field list'

As far as I can see, there is nothing wrong written ...

Here is a sample of the file that I am uploading:

Res,Tipo,Fecha,Unid
res0010,VX,2016-10-07 21:29:56,8
res0010,VX,2016-10-07 21:38:01,60
res0010,VX,2016-10-07 22:30:30,60
res0010,VY,2016-10-09 17:05:56,60
res0010,DD,2016-10-10 08:52:17,159212
res0010,DD,2016-10-10 08:53:37,7465

mysql mariadb

asked by Barranka 25.11.2016 в 23:44

source

1 answer

Integrate Unity screens with activities in Java React animations error # 119

score 1 · Accepted Answer

Try the following change in the trigger (Trigger):

delimiter $$

create or replace trigger trig_mi_tabla_bi before insert on mi_tabla
for each row
begin
    -- set new.fecha   = date(fecha_hora);
    -- set new.hora    = time(fecha_hora);
    SET NEW.'fecha' := DATE(NEW.'fecha_hora');
    SET NEW.'hora'  := TIME(NEW.'fecha_hora');
end$$

delimiter ;

UPDATE

Complete example:

File: ruta/a/mi/archivo/mi_archivo.csv

Res,Tipo,Fecha,Unid
res0010,VX,2016-10-07 21:29:56,8
res0010,VX,2016-10-07 21:38:01,60
res0010,VX,2016-10-07 22:30:30,60
res0010,VY,2016-10-09 17:05:56,60
res0010,DD,2016-10-10 08:52:17,159212
res0010,DD,2016-10-10 08:53:37,7465

mysql Command-line:

MariaDB [test]> SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 10.1.19-MariaDB-1~xenial |
+--------------------------+
1 row in set (0.00 sec)

MariaDB [test]> DROP TABLE IF EXISTS 'mi_tabla';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE IF NOT EXISTS 'mi_tabla' (
    -> 'id'             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> 'nombre_archivo' VARCHAR(255),
    -> 'res'            VARCHAR(100),
    -> 'tipo'           VARCHAR(10),
    -> 'fecha_hora'     DATETIME,
    -> 'unidades'       INT UNSIGNED,
    -> 'fecha'          DATE,
    -> 'hora'           TIME
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> DELIMITER $$

MariaDB [test]> CREATE OR REPLACE TRIGGER 'trig_mi_tabla_bi' BEFORE INSERT ON 'mi_tabla'
    -> FOR EACH ROW
    -> BEGIN
    ->     -- SET NEW.'fecha'   = DATE('fecha_hora');
    ->     -- SET NEW.'hora'    = TIME('fecha_hora');
    ->     SET NEW.'fecha' := DATE(NEW.'fecha_hora');
    ->     SET NEW.'hora'  := TIME(NEW.'fecha_hora');
    -> END$$
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> DELIMITER ;

MariaDB [test]> LOAD DATA LOCAL INFILE 'ruta/a/mi/archivo/mi_archivo.csv'
    -> INTO TABLE 'mi_tabla'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n' IGNORE 1 LINES
    -> ('res', 'tipo', 'fecha_hora', 'unidades');
Query OK, 6 rows affected (0.00 sec)                 
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> SELECT
    -> 'id',
    -> 'nombre_archivo',
    -> 'res',
    -> 'tipo',
    -> 'fecha_hora',
    -> 'unidades',
    -> 'fecha',
    -> 'hora'
    -> FROM
    -> 'mi_tabla';
+----+----------------+---------+------+---------------------+----------+------------+----------+
| id | nombre_archivo | res     | tipo | fecha_hora          | unidades | fecha      | hora     |
+----+----------------+---------+------+---------------------+----------+------------+----------+
|  1 | NULL           | res0010 | VX   | 2016-10-07 21:29:56 |        8 | 2016-10-07 | 21:29:56 |
|  2 | NULL           | res0010 | VX   | 2016-10-07 21:38:01 |       60 | 2016-10-07 | 21:38:01 |
|  3 | NULL           | res0010 | VX   | 2016-10-07 22:30:30 |       60 | 2016-10-07 | 22:30:30 |
|  4 | NULL           | res0010 | VY   | 2016-10-09 17:05:56 |       60 | 2016-10-09 | 17:05:56 |
|  5 | NULL           | res0010 | DD   | 2016-10-10 08:52:17 |   159212 | 2016-10-10 | 08:52:17 |
|  6 | NULL           | res0010 | DD   | 2016-10-10 08:53:37 |     7465 | 2016-10-10 | 08:53:37 |
+----+----------------+---------+------+---------------------+----------+------------+----------+
6 rows in set (0.00 sec)