Import . csv files to Mysql Workbench

Question

I have a. csv file in column A:

1. Product1
2. Product2
3. ...

In column B I have:

1. 1
2. 2
3. ...

if you want to put in the column The row 1 written Produ1 how do I proceed in the script? And what type of variable to define 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'
;

P.S. - even without the accent change I want to make, the result of the table disordered as I do to sort?

Answer 1

Define a table with a structure that fits the contents of your file. The Product can be stored in a VARCHAR column.

Here is an example 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 line IGNORE 1 ROWS must be included if your input file has a header. If not, remove this statement.

Adjust the instruction REPLACE according to your needs.

As for the ordering, even if you do not find the information explicitly in the documentation, it is a safe bet that the data will be inserted in the table sequentially, line by line.

However, remember that if you do not apply ORDER BY when you do a SELECT to a table, there is no certainty as to the order in which the records will be returned. Sometimes rows are allocated in a certain order and when selected from the table they come in a different order.