Resumo: neste tutorial, você aprenderá como importar um arquivo CSV para uma tabela MySQL usando a LOAD DATA INFILE
instruçã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 INFILE
instruçã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 discounts
com a seguinte estrutura:
E o discounts.csv
arquivo 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,10
Linguagem de código: PHP ( php )
Para importar o discounts.csv
arquivo para a discounts
tabela, 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 discounts
tabela:
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_priv
variá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 INFILE
instrução.
Quinto, copie o discounts.csv
arquivo para o diretório especificado pela secure_file_priv
opção.
Sexto, importe os dados do discounts.csv
arquivo para a discounts
tabela 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: 0
Linguagem 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 INFILE
funciona:
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 dadosFIELDS 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 discounts
tabela 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_priv
variável, receberá o seguinte erro:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Linguagem 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 SET
cláusula da LOAD DATA INFILE
instrução.
Suponha que a coluna de data expirada no discount2.csv
arquivo esteja no mm/dd/yyyy
formato:
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,10
Linguagem de código: PHP ( php )
Ao importar dados para a discounts
tabela, você pode transformá-los no formato de data MySQL usando a função STR_TO_DATE() :
Primeiro, trunque a discounts
tabela:
TRUNCATE TABLE discounts;
Segundo, transforme e carregue os dados do discount2.csv
arquivo na discounts
tabela:
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 INFILE
permite importar um arquivo CSV do seu computador local para uma tabela em um servidor MySQL remoto por meio da LOCAL
opçã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_infile
se o cliente e o servidor permitem o uso da LOCAL
opção na LOAD DATA INFILE
instrução.
Primeiro, abra o arquivo de configuração do MySQL ( my.ini
ou 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_infile
no servidor e no cliente.
Carregando o arquivo local
Primeiro, altere o banco de dados atual para aquele que contém a discounts
tabela:
USE classicmodels;
Linguagem de código: PHP ( php )
Segundo, execute a LOAD DATA INFILE
instruçã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 INFILE
declaração é a LOCAL
opção.
Se você carregar um arquivo CSV grande, verá que com a LOCAL
opçã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 sides
Linguagem 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 discounts
tabela:
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.discounts
neste 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 INFILE
instruçã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.