convert decimal and date in LOAD DATA INFILE

Question

convert decimal and date in LOAD DATA INFILE

Navigation

#1 by (1 votes)

0

I am importing a CVS file for MySQL. I have two columns: one quantidade and the other as data . I am trying to convert to the MySQL default. In the database this column is as decimal and date . It is importing all the log, except that these two fields are being saved with 0 in quantidade and null as date. I'm wrong about the conversion and not what.

CSV file

nome do material;03B;BC 03;800000235213364;14,000;22/06/2017
nome do material;03B;BC 03;800000235213355;14,000;22/06/2017
nome do material;03B;BC 03;800000235213358;14,000;22/06/2017
nome do material;03B;BC 03;800000235213353;14,000;22/06/2017

Code

mysql_query("LOAD DATA LOCAL INFILE 'C:/wamp/www/transbordo-sap/estoque/Estoque.csv' 
     INTO TABLE deposito FIELDS TERMINATED BY ';' 
     OPTIONALLY ENCLOSED BY '\"' 
     LINES TERMINATED BY '\n'
     (Material,Tipo,Posicao,HU,@Qtdd,@Data)  
     SET Qtdd = REPLACE(REPLACE('@Qtdd', '.', ''), ',', '.'),
         Data = STR_TO_DATE(@Data,'%Y-%m-%d')")or die (mysql_error());

php mysql csv

asked by anonymous 09.08.2017 / 03:26

1 answer

What does ns3 and ns4 mean in an XML file? The wordpress code is modified, but the blog does not update. What will it be?

score 1 · Accepted Answer

Try this, see if it works:

mysql_query("LOAD DATA LOCAL INFILE 'C:/wamp/www/transbordo-sap/estoque/Estoque.csv' 
     INTO TABLE deposito FIELDS TERMINATED BY ';' 
     OPTIONALLY ENCLOSED BY '\"' 
     LINES TERMINATED BY '\n'
     (Material,Tipo,Posicao,HU,@Qtdd,@Data)  
     SET Qtdd = CAST(@Qtdd as DECIMAL(9,2)),
         Data = STR_TO_DATE(@Data, '%d/%m/%Y')")or die (mysql_error());

I tested and entered normally: