How to change the format of a direct column in MySQL LOAD INFILE?

Question

How to change the format of a direct column in MySQL LOAD INFILE?

Navigation

#1 by (1 votes)

2

Hello, I have an 'x' table, with the Value column, in DECIMAL, in a MySQL 9 database.

I need to import data into csv. I do this using LOAD INFILE.

In these CSVs, the value is in a comma-like format, for example, "1938.20".

To import in this case, I import as varchar and then transform with REPLACE (VALUE, ',', '.').

I wanted to know if it is possible to do the transformation at the time of importing the data.

mysql

asked by anonymous 01.06.2015 / 22:40

1 answer

How to put a MouseCliked event on a table using JavaFX How to interconnect two arrays?

score 1 · Answer 1

To make the change is very simple

LOAD DATA LOCAL INFILE 'planilha.cvs'
INTO TABLE tabela_preco_produtos 
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(codigo, produto, @var1)
set preco = REPLACE(@var1, ',', '.');

Determine the FIELDS TERMINATED BY ;

Determines the connection (Put multiple columns in a) ENCLOSED BY , in the example if using double quotation joins the values in a column;

Determines the end of lines LINES TERMINATED BY ;

The name of the columns (codigo, produto, @var1) ;

We made a set with replace to change the format of the entry.