B
There are several problems in your code:There seems to be an error of understanding in your sentence, wanting to put aside those columns that will be subject to calculation before being inserted.It is what can be deduced to see that for example the second column that appears in your sentence is fecha_contable and then appears the same column, apart, in brackets (@fecha_contable, @fecha_apertura, ...In the https://dev.mysql.com/doc/refman/5.7/en/load-data.html the following is said about cases similar to yours:User variables in the clause SET can be used several
ways. The following example uses the first entry column
directly for the value of t1.column1and assigns 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;
I mean, the columns that enter into the SET they don't have to go apart, but in the place where they'd belong., indicating @that there would go a column under some calculation or operation.You don't have to repeat SET for each column, but only at first, then putting each column separated by a comma.If you say your dates in the file have this format DD/MM/YY, consider using STR_TO_DATE, what the function needs to know is how the data is formatted, not how you want them to be, therefore, use should be like this: STR_TO_DATE(@columna, "%d/%M/%YY"). Taking into account the above, your sentence should be more or less so: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")
