How can I import a TSV file when the numbers use comma as a decimal separator?
LOAD DATA INFILE '$filename' INTO TABLE dados_meteo IGNORE 3 LINES
($fields[0], $fields[1], $fields[2], $fields[3], $fields[4], $fields[5])
SET POM='$pom'
;
The local_infile system variable controls server-side LOCAL capability. Depending on the local_infile setting, the server refuses or permits local data loading by clients that request local data loading. By default, local_infile is disabled. (This is a change from previous versions of MySQL.)
The code is like this: LOAD DATA INFILE '/path/filename. csv' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (column_name3, column_name5); Here you go with adding data to only two columns(you can choose them with the name of the column) to the table.
mysql> LOAD DATA LOCAL INFILE '/path/pet. txt' INTO TABLE pet; If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead: mysql> LOAD DATA LOCAL INFILE '/path/pet.
Try to replace ',' to '.' when loading.
For example -
LOAD DATA INFILE 'file.csv' INTO TABLE dados_meteo
(@var1, @var2)
SET column1 = REPLACE(@var1, ',', '.'), column2 = REPLACE(@var2, ',', '.')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With