I have a .csv file that in column A I have:
Product1 Product2 ...
In column B I have:
1 2 ...
If you want to, for example, insert column 1 in line 1 of Produt1 as I proceed in the script? And what kind of variable to set in the table?
My script:
use test;
LOAD DATA LOCAL INFILE 'local' INTO TABLE tabela1
CHARACTER SET utf8
fields terminated by ';'
lines terminated by '\r'
;
Q. - Even without the accent change I want to make, the result of the cluttered table how do I sort?
Defines a table with a structure that fits the contents of your file. The Product can be saved in a VARCHAR type column.
Here is an example that you should change according to your specific case.
CREATE TABLE produtos (
id INT NOT NULL AUTO_INCREMENT,
Produto VARCHAR(255) NOT NULL,
Valor INT NOT NULL,
PRIMARY KEY (id)
);
Then to load the data you can do the following:
LOAD DATA INFILE 'c:/tmp/produtos.csv'
INTO TABLE produtos
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r'
IGNORE 1 ROWS
(@produto,valor)
SET produto = REPLACE(@produto, 'Produto1', 'Prodúto1');
The IGNORE 1 ROWS line should be included if your input file has a header. If you do not, remove this statement.
Set the REPLACE statement to your needs.
As for ordering, even if you do not find the information explicitly in the documentation, it is a safe bet that the data will be inserted into the table sequentially, line by line.
However, remember that if you do not apply ORDER BY when you SELECT a table, there is no certainty about the order in which the records will be returned. Sometimes rows are allocated in a particular order and when selected from the table come in a different order.