Home / Questions / 36052
Accepted
Barranka
Asked: 2020-11-26 14:44:00 +0800 CST 2020-11-26 14:44:00 +0800 CST

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

  • 772

I have a little 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 doesn't recognize this column. I've dropped and created the table several times, and yet I still get the same error.

The code I am executing 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 misspelled...

Here's a sample of the file I'm 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

1 Answers

  1. Best 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

    Full example:

    File, Archive: 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)
    • 1