Importar arquivo CSV para tabela MySQL

Resumo: neste tutorial, você aprenderá como importar um arquivo CSV para uma tabela MySQL usando a LOAD DATA INFILEinstrução e o MySQL Workbench.

1) Importando um arquivo CSV no servidor MySQL para uma tabela usando a instrução LOAD DATA INFILE

A LOAD DATA INFILEinstrução permite ler dados de um arquivo CSV em um diretório especificado no servidor MySQL e importar seu conteúdo para uma tabela.

Antes de importar o arquivo, você precisa preparar o seguinte:

• Uma tabela para a qual você deseja importar dados.
• Um arquivo CSV com dados que correspondem ao número de colunas da tabela e ao tipo de dados em cada coluna.
• Uma conta de usuário MySQL que possui privilégios FILE e INSERT.

Suponha que você tenha uma tabela chamada discountscom a seguinte estrutura:

E o  discounts.csvarquivo a seguir contém a primeira linha como cabeçalhos de coluna e as outras três linhas de dados:

id,title,expired date,amount
1,Spring Break,2014-01-01,20
2,Back to School,2014-09-01,25
3,Summer Holiday,2014-08-25,10Linguagem de código:  PHP  ( php )

Para importar o discounts.csvarquivo para a discountstabela, siga estas etapas:

Primeiro, abra o Prompt de Comando no Windows ou o Terminal em sistemas do tipo Unix e conecte-se ao servidor MySQL :

mysql -u root -p

Segundo, altere o banco de dados atual para classicmodels:

use classicmodels;Linguagem de código:  PHP  ( php )

Terceiro, crie uma discountstabela:

CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);Linguagem de código:  PHP  ( php )

Quarto, mostre o valor da @@secure_file_privvariável:

SELECT @@secure_file_priv;Linguagem de código:  CSS  ( css )

Saída:

+------------------------------------------------+
| @@secure_file_priv                             |
+------------------------------------------------+
| C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------------------------------------+
1 row in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

A  secure_file_priv opção indica o diretório onde você pode armazenar o arquivo de entrada e executá-lo com a LOAD DATA INFILEinstrução.

Quinto, copie o discounts.csvarquivo para o diretório especificado pela secure_file_privopção.

Sexto, importe os dados do discounts.csvarquivo para a discountstabela executando a seguinte instrução:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0Linguagem de código:  CSS  ( css )

A saída indica que a instrução carregou o arquivo com três linhas com êxito.

Veja como LOAD DATA INFILEfunciona:

• LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv': especifica o caminho para o arquivo de entrada ( discounts.csv) que contém os dados a serem carregados na tabela.
• INTO TABLE discounts: Especifica a tabela de destino ( discounts) onde você deseja carregar os dados
• FIELDS TERMINATED BY ',': especifica que os campos no arquivo de entrada são separados (terminados) por uma vírgula ( ,). Isso indica que o arquivo é um arquivo CSV (valores separados por vírgula).
• ENCLOSED BY '"': especifica que os campos no arquivo de entrada serão colocados entre aspas duplas ( "). Isso é comum em arquivos CSV para lidar com casos em que um campo pode conter o delimitador ,.
• LINES TERMINATED BY '\n': especifica que cada linha no arquivo de entrada termina com um caractere de nova linha ( \n). Indica o fim de um registro (linha) no arquivo CSV.
• IGNORE 1 ROWS: instrui a instrução a ignorar a primeira linha no arquivo de entrada. Isto é útil quando a primeira linha contém cabeçalhos e não deve ser importada como dados.

Por fim, recupere os dados da discountstabela para verificar a importação:

SELECT * FROM discounts;

Saída:

+----+----------------+--------------+--------+
| id | title          | expired_date | amount |
+----+----------------+--------------+--------+
|  1 | Spring Break   | 2014-01-01   |  20.00 |
|  2 | Back to School | 2014-09-01   |  25.00 |
|  3 | Summer Holiday | 2014-08-25   |  10.00 |
+----+----------------+--------------+--------+
3 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Observe que se você não colocar o arquivo no diretório especificado pela secure_file_privvariável, receberá o seguinte erro:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementLinguagem de código:  JavaScript  ( javascript )

Transformando dados durante a importação

Às vezes, o formato dos dados não corresponde às colunas de destino da tabela. Em casos simples, você pode transformá-lo usando a SETcláusula da  LOAD DATA INFILEinstrução.

Suponha que a coluna de data expirada no  discount2.csvarquivo esteja no  mm/dd/yyyyformato:

id,title,expired date,amount
1,Spring Break,01/01/2014,20
2,Back to School,09/01/2014,25
3,Summer Holiday,08/25/2014,10Linguagem de código:  PHP  ( php )

Ao importar dados para a discountstabela, você pode transformá-los no formato de data MySQL usando a função STR_TO_DATE() :

Primeiro, trunque a discountstabela:

TRUNCATE TABLE discounts;

Segundo, transforme e carregue os dados do discount2.csvarquivo na discountstabela:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, title, @expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) Importando um arquivo CSV de um computador local para uma tabela em um servidor MySQL remoto

O LOAD DATA INFILEpermite importar um arquivo CSV do seu computador local para uma tabela em um servidor MySQL remoto por meio da LOCALopção.

Configuração

O MySQL desabilita o carregamento de um arquivo local no servidor por padrão. Para carregar um arquivo local no servidor MySQL, você precisa habilitar a opção tanto no cliente quanto no servidor.

Controla local_infilese o cliente e o servidor permitem o uso da LOCALopção na LOAD DATA INFILEinstrução.

Primeiro, abra o arquivo de configuração do MySQL ( my.iniou my.cnf) e adicione a seguinte linha para permitir o carregamento do arquivo local no servidor MySQL:

[mysqld]
local_infile=1

Segundo, reinicie o servidor MySQL para aplicar a alteração.

Terceiro, abra o programa cliente MySQL no computador local para conectar-se ao servidor MySQL

mysql -h hostname -u root -p

Você precisa substituir o nome do host pelo seu servidor MySQL remoto.

Quarto, defina a variável global local_infile como 1 (ou ON):

SET GLOBAL local_infile = 1;Linguagem de código:  PHP  ( php )

Configuramos o local_infileno servidor e no cliente.

Carregando o arquivo local

Primeiro, altere o banco de dados atual para aquele que contém a discountstabela:

USE classicmodels;Linguagem de código:  PHP  ( php )

Segundo, execute a LOAD DATA INFILEinstrução para importar dados de um arquivo CSV localizado no C:\temp\diretório para o servidor MySQL:

LOAD DATA LOCAL INFILE  'c:/temp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;Linguagem de código:  JavaScript  ( javascript )

A única adição à LOAD DATA INFILEdeclaração é a LOCALopção.

Se você carregar um arquivo CSV grande, verá que com a  LOCALopção levará algum tempo para transferir o arquivo para o servidor MySQL.

Observe que se você não configurar o cliente e o servidor corretamente, receberá a seguinte mensagem:

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sidesLinguagem de código:  JavaScript  ( javascript )

3) Importando arquivos CSV para uma tabela usando MySQL Workbench

O ambiente de trabalho MySQL fornece uma ferramenta para importar dados para uma tabela

Primeiro, abra a discountstabela:

Em segundo lugar, clique no botão importar:

Terceiro, selecione o caminho para o arquivo CSV e clique no botão Avançar:

Quarto, selecione a tabela de destino, que é classicmodels.discountsneste caso. Observe que você pode criar uma nova tabela antes de importar o arquivo e/ou truncar a tabela antes de importar:

Quinto, mapeie as colunas do arquivo de origem com as colunas da tabela de destino e clique no botão Avançar:

Sexto, revise as etapas que o Workbench executará e clique no botão Avançar:

Sétimo, revise as etapas que o Workbench executará e clique no botão Avançar:

Oito, revise o resultado da importação e clique no botão Concluir:

Por fim, mostre o conteúdo da tabela de descontos:

Resumo

• Use a LOAD DATA INFILEinstrução para importar um arquivo CSV para uma tabela.
• Use o MySQL Workbench para importar um arquivo CSV do computador local para uma tabela em um servidor MySQL remoto.