Cargar Txt a BD Mysql

[gnzsoloyo]

Personalmente, usaría una tabla intermedia que te permita cargar los valores tal y como están en el archivo y luego, con otra sentencia hacer un volcado masivo de datos a la tabla final

Código MySQL:

DROP TABLE IF EXISTS `test`.`tabla1`;
CREATE TABLE  `test`.`tabla1` (
  `HORA1` time NOT NULL,
  `HORA2` time NOT NULL,
  `HORA3` time NOT NULL,
  `HORA4` time NOT NULL,
  `HORA5` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB';
 

Código MySQL:

DROP TABLE IF EXISTS `test`.`tabla2`;
CREATE TABLE  `test`.`tabla2` (
  `HORA1` varchar(10)NOT NULL,
  `HORA2` varchar(10)NOT NULL,
  `HORA3` varchar(10)NOT NULL,
  `HORA4` varchar(10)NOT NULL,
  `HORA5` varchar(10)NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 7168 kB';
 

Código MySQL:

LOAD DATA INFILE 'C:\\HORAS.TXT'
  INTO TABLE tabla2
  FIELDS TERMINATED BY ', '
  ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n';
 

Código MySQL:

mysql> SELECT * FROM tabla2 t;
+-------+-------+-------+-------+-------+
| HORA1 | HORA2 | HORA3 | HORA4 | HORA5 |
+-------+-------+-------+-------+-------+
| 00:01 | 00:01 | 00:01 | 00:01 | 00:01 |
| 00:01 | 00:01 | 00:01 | 00:01 | 00:01 |
| 00:01 | 00:01 | 00:01 | 00:01 | 00:01 |
| 00:01 | 00:01 | 00:01 | 00:01 | 00:01 |
| 00:01 | 00:01 | 00:01 | 00:01 | 00:01 |
+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)
 

Código MySQL:

INSERT INTO tabla1(HORA1, HORA2, HORA3, HORA4, HORA5)
SELECT
  TIME(CONCAT('00:',HORA1)),
  TIME(CONCAT('00:',HORA2)),
  TIME(CONCAT('00:',HORA3)),
  TIME(CONCAT('00:',HORA4)),
  TIME(CONCAT('00:',HORA5))
FROM tabla2 t;
mysql> SELECT * FROM tabla1 t;
+----------+----------+----------+----------+----------+
| HORA1    | HORA2    | HORA3    | HORA4    | HORA5    |
+----------+----------+----------+----------+----------+
| 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 |
| 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 |
| 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 |
| 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 |
| 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 | 00:00:01 |
+----------+----------+----------+----------+----------+
5 rows in set (0.02 sec)