mySQL - LOAD DATA INFILE + format variables

Question

mySQL - LOAD DATA INFILE + format variables

Navigation

#1 by (0 votes)

0

Good morning,

I am loading information from a txt file and, in doing so, I want to format all the variables in a column.

In particular, I am in charge of DATETIME information, which comes as a string, and I want to give it the correct format so that it is saved well.

I try to do it with a STR_TO_DATE, but it does not load the information well.

Full mySQL code (variable in column 5):

LOAD DATA LOCAL INFILE 
'in/trazas.txt'
INTO TABLE vn_gmstrazas
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(@col1,@col2,@col3,@col4,@col5,@col6,@col7)
SET num = @col1, destino = @col2, referencia = @col3, estado = @col4, fecha = STR_TO_DATE(@col5, '%Y-%m-%d %H:%i:%s'), intento = @col6, refweb = @col7;

File that is loaded (example of a line):

1;99987984651;;Confirmado;22/12/2017 10:36:00;1;171222W02E2497_1.txt

How can I do it?

Thank you very much !!

mysql

asked by Nicolau Roca 27.12.2017 в 12:42

source

1 answer

How to delete row in tableView with CoreData data How to send a res.json () that is inside a for loop, using node (express) and ajax

score 0 · Answer 1

Good Nicolau,

I have been reporting on the subject since it sounded to me something that MySQL did not accept to format dates when you make the insertions, and I have verified that it is so.

If you look at the manual (DATE, DATETIME, and TIMESTAMP Types) in the first paragraphs explain that MySQL only works with the formats 'YYYY-MM-DD HH:MM:SS' . Knowing this, any format you try to put will always change it to the format that I have indicated.

The only solution that I see to your problem would be to do the inverse process, instead of inserting them with the format you want you should get them with that format.