Format date field, when loading text file to mysql

Question

Format date field, when loading text file to mysql

Navigation

#1 by (4 votes)

1

I try to insert massive records from a text file, I try to have the id as autoincremental, when entering the other fields, and to format the date fields of YY / MM / DD at the moment of insertion in the table. Since the format of the date fields of the file is DD / MM / YY.

Text file contains;

44 fields (date fields, format day / month / year)

Table to insert:

autonumeric_id, + (when inserting the 44 fields, including date fields formatted to year / month / day)

load data local infile 'c:\ventas\vtascopia.txt' 
into table ventas306 fields terminated by '¶' lines terminated by 'Þ' 

(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) 

(@fecha_contable, @fecha_apertura, @fecha_salida, @fecha_venta, @fecha_cierre)

SET id = null,
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")

Running the script like this: marks error in the next line

#1064 - Algo está equivocado en su sintax cerca '(@fecha_contable, @fecha_apertura, @fecha_salida, @fecha_venta, @fecha_cierre)

mysql

asked by Armando Arellano 04.03.2018 в 07:12

source

1 answer

How to convert a JSON file to a table with dynamic columns in HTML Linq C # Outer Join Show the values that are not in the fields to compare

score 4 · Accepted Answer

There are several problems in your code:

It seems that there is an error of understanding in your sentence, wanting to put aside those columns that will be submitted to calculation before being inserted.

It is what can be deduced when you see that for example the second column that appears in your sentence is fecha_contable and then the same column appears, apart, in parentheses (@fecha_contable, @fecha_apertura, ...

In the documentation the following is said about cases similar to yours:

The user variables in the SET clause can be used for several ways The following example uses the first input column directly for the value of t1.column1 , and assign the second input column to a user variable that is subject to a division operation before being used for the value of t1.column2 :
LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

That is, the columns that fall within the SET do not have to go separately, but in the place that would correspond , indicating with @ , that there would be a column submitted to some calculation or operation.

You do not have to repeat SET for each column, but only at the beginning, then put each column separated by a comma.

If you say that your dates in the file have this format DD/MM/YY , consider that when using STR_TO_DATE , what the function needs to know is how the data is formatted, not how you want them to be , therefore, the usage should be like this: STR_TO_DATE(@columna, "%d/%M/%YY") .

Taking into account what has been said, your sentence should look something like this:

load data local infile 'c:\ventas\vtascopia.txt' 
into table ventas306 
fields terminated by '¶' 
lines terminated by 'Þ' (
    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) 

SET 
id = null,
fecha_contable = STR_TO_DATE(@fecha_contable, "%d/%M/%YY"),
fecha_apertura = STR_TO_DATE(@fecha_apertura, "%d/%M/%YY"),
fecha_salida = STR_TO_DATE(@fecha_salida, "%d/%M/%YY"),
fecha_venta = STR_TO_DATE(@fecha_venta, "%d/%M/%YY"),
fecha_cierre = STR_TO_DATE(@fecha_cierre, "%d/%M/%YY")