/dev/null

agosto 23, 2007

Popular registros de um arquivo CSV em um banco de dados MySQL

Filed under: banco de dados — stefanomartins @ 2:20 pm

É inegável que houveram várias boas idéias no mundo da computação que facilitam a nossa vida. O lançamento de sistemas gerenciadores de bancos de dados relacionais (SGBDR) é uma delas, principalmente aos olhos dos usuários e demais pessoas que são beneficiadas com tal tecnologia. Entretanto, nenhum usuário conhece a verdadeira mágica que um DBA, administrador de sistemas ou um programador faz para migrar dados de um sistema antigo para o novo.

Várias são as possibilidades de fazer essa migração. Para alguns, XML é a solução definitiva. Para outros, pouco aplicável pelo fato de ter de mexer com o desenvolvimento de sistemas. O quê poucos sabem é que existe uma alternativa para importar dados para tabelas do MySQL (E de outros SGBDR’s também) através de arquivos CSV (Comma-Separated Values).

Quantas vezes você, DBA, administrador de sistemas ou programador já teve que criar programas para fazer a importação de tais arquivos? Eu mesmo utilizava muito essa técnica (Shell-Script, PHP, PERL e as funções explode() e fgetcsv() são as principais ferramentas do canivete-suíço de quem faz isso) e não há problema nenhum, mas poucos sabem que o próprio SGBDR já possui ferramentas de importação e exportação de arquivos em formato CSV. Neste artigo nós nos propomos a mostrar esta técnica.

Eis aqui o roteiro necessário para a importação:

1. Criar o arquivo CSV em algum software de planilhas eletrônicas, como o Excel ou o OpenOffice, caso você já não o possua;
2. Criar a estrutura do banco e das tabelas, em termos de banco de dados, aqui nós fazemos uso da DDL (Data Definition Language);
3. Importar os dados do arquivo CSV.

Para esta tarefa, usaremos como exemplo um banco de dados chamado escola, que possuirá duas tabelas; uma chamada alunos e outra chamada cursos, no seguinte esquema:

Observação:
@ = Chave Primária
# = Chave Estrangeira

Tabela alunos:

alunos = {@alu_matricula;alu_nome;alu_sobrenome;#alu_curso}

alu_matricula alu_nome alu_sobrenome alu_curso
1 João da Silva 3
2 Marcos Nascimento 1
3 Regiane Moura 2

1;João;da Silva;3
2;Marcos;Nascimento;1
3;Regiane;Moura;2

Tabela cursos:

cursos = {@cur_codigo;cur_nome}

cur_codigo cur_nome
1 Mecânica
2 Informática
3 Administração

1;Mecânica
2;Informática
3;Administração

Script SQL para criação do banco de dados e das tabelas:

CREATE DATABASE escola;

CREATE TABLE alunos(alu_matricula int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,alu_nome varchar(20) NOT NULL,alu_sobrenome varchar(50) NOT NULL,alu_curso int(4) NOT NULL);

CREATE TABLE cursos(cur_codigo int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,cur_nome varchar(20) NOT NULL);

A sintaxe para a importação é a seguinte:

LOAD DATA LOCAL INFILE ‘[caminhodoarquivo]‘ INTO TABLE [tabela]
FIELDS TERMINATED BY ‘[delimitador]‘ LINES TERMINATED BY ‘[escapedelinha]‘;

Onde:

[caminhodoarquivo] = Caminho onde o arquivo CSV se encontra;
[tabela] = Tabela do banco de dados que será populada;
[delimitador] = Caractere utilizado para delimitar os campos no arquivo CSV. No artigo nós estamos utilizando ponto-e-vírgula;
[escapedelinha] = Este parâmetro se faz necessário porque o formato de arquivos texto difere entre sistemas operacionais. Em sistemas UNIX-Like (GNU/Linux, *BSD, Solaris, etc.), uma quebra de linha é data apenas por ‘\n’, enquanto que em sistemas Microsoft (DOS e Windows) a quebra de linha é acompanhada de um carriage return (‘\r\n’).

Sendo assim, nós vamos supôr que os arquivos com o CSV possuem os nomes alunos.csv e cursos.csv, e nós iremos efetivamente popular os registros utilizando a seguinte sintaxe SQL:

LOAD DATA LOCAL INFILE ‘alunos.csv’ INTO TABLE alunos
FIELDS TERMINATED BY ‘;’ LINES TERMINATED BY ‘\n’;

LOAD DATA LOCAL INFILE ‘cursos.csv’ INTO TABLE cursos
FIELDS TERMINATED BY ‘;’ LINES TERMINATES BY ‘\n’;

Agora, para confirmar que os registros foram importados corretamente, nós podemos fazer uma consulta no banco de dados.

SELECT a.alu_matricula,a.alu_nome,a.alu_sobrenome,b.cur_nome FROM alunos AS a INNER JOIN cursos AS b ON a.alu_curso = b.cur_codigo;

Se tudo correu bem, você deve receber o resultado da consulta.

Agora, se nós quiséssemos exportar o conteúdo de alguma query SQL para um arquivo CSV, nós utilizaríamos um SELECT convencional, mas adicionando a cláusula INTO OUTFILE. Na prática, funciona assim:

SELECT a.alu_matricula,a.alu_nome,a.alu_sobrenome,b.cur_nome FROM alunos AS a INNER JOIN cursos AS b ON a.alu_curso = b.cur_codigo INTO OUTFILE ‘alunos_cursos.csv’ FIELDS TERMINATED BY ‘;’ LINES TERMINATED BY ‘\n’;

Será criado um arquivo chamado alunos_cursos.csv dentro do diretório onde localiza-se os arquivos do banco de dados MySQL, na distribuição Ubuntu, os diretórios dos bancos de dados MySQL geralmente localizam-se em /var/lib/mysql. Em sistemas Microsoft Windows, lembre-se de trocar ‘\n’ por ‘\n\r’.

Concluindo, esta técnica é muito útil, mas pouco utilizada. Para pessoas que precisam extrair informações de bancos para um eventual pós-processamento, como, por exemplo, criação de tabelas dinâmicas em algum software para planilhas eletrônicas.

Referências:

Anúncios

1 Comentário »

  1. a rotina “load data local infile” funcionou bem pra caramba!
    resolveu um problema que tínhamos na empresa onde trabalho, em que rodávamos um DTS que fazia a importação, porém que dava muitos erros.

    Comentário por Mr. Zee — janeiro 23, 2008 @ 5:53 pm


RSS feed for comments on this post. TrackBack URI

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Crie um website ou blog gratuito no WordPress.com.

%d blogueiros gostam disto: