How to change the format of a direct column in Mysql’s LOAD INFILE?

Asked

Viewed 282 times

2

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

It turns out I need to import data in csv. I do this using LOAD INFILE.

In these Csvs, the value is in comma format, for example, "1938.20".

To import in this case, I care how to scan and then transform with REPLACE(VALUE, ',', '.').

I wanted to know if it is already possible to make the transformation when importing the data.

mysql

1 answer

1

To make the change is quite 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, ',', '.');

Determines which separator FIELDS TERMINATED BY;

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

Determines the end of the lines LINES TERMINATED BY;

The columns are named (codigo, produto, @var1);

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.