Load text file to mysql, using sql script

Question

Load text file to mysql, using sql script

Navigation

#1 by (2 votes)

1

I'm trying to load a text file with the following format, to mysql:

Clave Area¶Area de Venta¶F. Contable¶Sesión¶Fecha de Apertura¶Hora de Apertura¶AV/P¶Transacción¶Operación¶ID DE VTA¶Tipo¶Origen¶Destino¶Fecha Salida¶Hora Salida¶Folio Boleto¶Asiento¶Nombre Pasajero¶Tipo¶Formas de Pago¶EF¶TB¶PU¶TR¶DO¶GP¶CV¶Importe¶Formato/Salto¶Fecha Venta¶Hora Venta¶Corrida¶Empresa Corrida¶Tipo de Boleto¶Numero de Referencia¶Clave de Autorizacion¶Voucher¶Tipo de Servicio¶F. Cierre¶Razon Social¶R.F.C.¶DOCUMENTO ¶ EMPRESA¶

006¶ACAPULCO CENTRO¶01/01/2016¶231658¶01/01/2016¶06:44:22¶ACAEE67892¶5155690¶7978798¶¶CS¶ACAC¶OMET¶01/01/2016¶12:15¶T-A-04131772¶13¶GRISELDA BARAJAS GUERRERO¶¶EF¶146.00¶0.00¶0.00¶0.00¶0.00¶0.00¶0.00¶146.00¶¶01/01/2016¶07:49:40¶028376¶AUTOTRANSPORTES COSTEñOS¶ADULTO¶00000000000000000000¶¶0¶ECONOMICO¶01/01/2016¶¶¶0¶¶Þ
006¶ACAPULCO CENTRO¶01/01/2016¶231658¶01/01/2016¶06:44:22¶ACAEE67892¶5155690¶7978799¶¶CS¶ACAC¶OMET¶01/01/2016¶12:15¶T-A-04131773¶14¶MARGARITA CARVAJAL NAVA¶¶EF¶146.00¶0.00¶0.00¶0.00¶0.00¶0.00¶0.00¶146.00¶¶01/01/2016¶07:49:40¶028376¶AUTOTRANSPORTES COSTEñOS¶ADULTO¶00000000000000000000¶¶0¶ECONOMICO¶01/01/2016¶¶¶0¶¶Þ
006¶ACAPULCO CENTRO¶01/01/2016¶231658¶01/01/2016¶06:44:22¶ACAEE67892¶5155690¶7978800¶¶CS¶ACAC¶OMET¶01/01/2016¶12:15¶T-A-04131774¶17¶JULIA YARIN CASTILLO BARAJAS¶¶EF¶146.00¶0.00¶0.00¶0.00¶0.00¶0.00¶0.00¶146.00¶¶01/01/2016¶07:49:40¶028376¶AUTOTRANSPORTES COSTEñOS¶ADULTO¶00000000000000000000¶¶0¶ECONOMICO¶01/01/2016¶¶¶0¶¶Þ

The structure of the table has 45 fields, in the file fields = 44 the table has an additional field, which is an auto-incremental id, which does not come as a column in the file. Then I try to insert the 44 fields of the text file and the id will increment. As normally happens when entering records.

But you can not insert the rows, I think that because I need to indicate the form of insertion and / or exception or jump of that auto-incremental id field.

What should be the correct form of syntax in the script, if I need to format: YY / MONTH / DAY to the date field, and write the rows in the table?

SQL Script

load data local infile 'c:\ventas\ventas.txt' into table ventas306

        fields terminated by '¶' lines terminated by 'Þ' IGNORE 1 LINES 


        (@fecha_contable, @fecha_apertura, @fecha_salida, @fecha_venta, @fecha_cierre)
        SET fecha_contable = STR_TO_DATE(@fecha_contable, "%Y %M %d"),
        SET fecha_apertura = STR_TO_DATE(@fecha_apertura, "%Y %M %d"),
        SET fecha_salida = STR_TO_DATE(@fecha_salida, "%Y %M %d"),
        SET fecha_venta = STR_TO_DATE(@fecha_venta, "%Y %M %d"),
        SET fecha_cierre = STR_TO_DATE(@fecha_cierre, "%Y %M %d")

        (clave_area,area_vta,fecha_contable,sesion,fecha_apertura,hora_apertura,av_p,transaccion,operacion,
        id_vta,tipo,origen,destino,fecha_salida,hra_salida,f_boleto,asiento,pasajero,campo19,f_pago,ef,
        tb,pu,tr,doo,gp,cv,importe,formato_salto,fecha_venta,hra_venta,corrida,empresa_corrida,t_boleto,
        referencia,autorizacion,voucher,t_servicio,fecha_cierre,razon_social,rfc,documento,empresa,fin)

mysql

asked by Armando Arellano 01.03.2018 в 21:35

source

1 answer

how to show a ui-icon in primefaces when the value of a variable is true or false? Error WindowLeaked when displaying a Dialog

score 2 · Accepted Answer

Try the following (not all columns are included in the example):

MySQL:

DROP TABLE IF EXISTS 'ventas306';

CREATE TABLE IF NOT EXISTS 'ventas306' (
  'id' BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  'clave_area' CHAR(3) NOT NULL,
  'area_vta' VARCHAR(25) NOT NULL,
  'fecha_contable' DATE NOT NULL
);

File: /path/to/file/ventas.txt

Clave Area¶Area de Venta¶F. ContableÞ
006¶ACAPULCO CENTRO¶01/01/2016Þ
006¶ACAPULCO CENTRO¶02/01/2016Þ
006¶ACAPULCO CENTRO¶03/01/2016Þ

MySQL:

mysql> LOAD DATA LOCAL INFILE '/path/to/file/ventas.txt'
    ->   INTO TABLE 'ventas306'
    ->   FIELDS TERMINATED BY '¶'
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY 'Þ'
    ->   IGNORE 1 LINES
    ->   ('clave_area', 'area_vta', @'fecha_contable')
    ->   SET 'fecha_contable' = STR_TO_DATE(@'fecha_contable', '%d/%m/%Y');
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1638 | Non-ASCII separator arguments are not fully supported |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   'id',
    ->   'clave_area',
    ->   'area_vta',
    ->   'fecha_contable'
    -> FROM
    ->   'ventas306';
Empty set (0.00 sec)

The recommendation would be to change the line termination character ( LINES TERMINATED BY ) in the file ventas.txt and in LOAD DATA .

File: /path/to/file/ventas.txt

Clave Area¶Area de Venta¶F. Contable
006¶ACAPULCO CENTRO¶01/01/2016
006¶ACAPULCO CENTRO¶02/01/2016
006¶ACAPULCO CENTRO¶03/01/2016

MySQL:

mysql> LOAD DATA LOCAL INFILE '/path/to/file/ventas.txt'
    ->   INTO TABLE 'ventas306'
    ->   FIELDS TERMINATED BY '¶'
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\n'
    ->   IGNORE 1 LINES
    ->   ('clave_area', 'area_vta', @'fecha_contable')
    ->   SET 'fecha_contable' = STR_TO_DATE(@'fecha_contable', '%d/%m/%Y');
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1638 | Non-ASCII separator arguments are not fully supported |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   'id',
    ->   'clave_area',
    ->   'area_vta',
    ->   'fecha_contable'
    -> FROM
    ->   'ventas306';
+----+------------+------ ----------+-----------------+
| id | clave_area | area_vta        | fecha_contable  |
+----+------------+-----------------+-----------------+
|  1 | 006        | ACAPULCO CENTRO | 2016-01-01      |
|  2 | 006        | ACAPULCO CENTRO | 2016-01-02      |
|  3 | 006        | ACAPULCO CENTRO | 2016-01-03      |
+----+------------+-----------------+-----------------+
3 rows in set (0.00 sec)