Importando múltiplos arquivos texto

Faz alguns meses que publiquei aqui nesta coluna um artigo descrevendo alguns problemas comuns com arquivos texto. Separadores de colunas, identificadores de linhas, caracteres “proibidos” etc. Desta vez eu apresento uma solução simples para importação de múltiplos arquivos texto no SQL Server. De quebra, eu uso também um truque muito interessante apresentado por Grzegorz Oledzki para conversão de texto no formato ‘DD-MM-YYYY’ em datas reconhecidas pelo SGBD.

Alternativas de importação

Existem vários comandos e ferramentas que auxiliam na importação de dados. Alguns são simples e práticos, outros mais complexos e robustos. Basta pensar que, em última análise, um comando BULK INSERT, uma operação de DATA FLOW do SSIS e o utilitário BCP fazem a mesma coisa: importam dados.

Eu sou fã da simplicidade do comando BULK INSERT, apesar dele ter limitações importantes. Talvez a mais séria destas limitações seja não suportar arquivos CSV, que são muito comuns quando se trata de importação de dados.

Por outro lado, o comando BULK INSERT oferece uma série de benefícios interessantes. Alguns deles são:

1. Reforça a validação de tipos de dados;
2. Valida restrições do modelo (como chaves estrangeiras);
3. Permite configurar delimitadores de colunas e marcadores de final de linha;
4. Permite definir a página de códigos, para trabalhar com caracteres diferentes do ASCII;
5. Controla o fluxo de importação, comitando transações a cada N registros importados;
6. Permite desabilitar gatilhos que seriam disparados em consequência da inserção dos novos registros;
7. Permite definição de layouts de importação, essencial nos casos em que o arquivo importado tem número diferente de colunas em relação à tabela de destino;
8. Permite definir as linhas inicial e final do arquivo de dados, desprezando eventuais cabeçalhos ou rodapés no arquivo importado;
9. Controla o tipo de “lock” sobre a tabela;
10. Possibilita cargas de dados em paralelo, aumentando dramaticamente a velocidade do processo;
11. Dispensa log da operação de carga de dados e, portanto, não tem impacto sobre o “transaction log” da base;
12. Especifica um arquivo de output para registrar erros da importação.

Estudo de caso

Considere que você recebeu uma solicitação especial de um diretor da sua empresa. Ele lhe passou um conjunto de 50 arquivos texto e pediu para importá-los num banco de dados para fazer algumas análises.

Ele fez questão de passar detalhes do seu pedido: todos os arquivos tem o mesmo layout, exatamente a mesma sequência de campos. Usam o mesmo separador de colunas (caractere de tabulação “\t”) e o mesmo identificador de final do registro (caractere de retorno de linha “\n”). Os 50 arquivos recebem nomes sequenciais, que vão de “Arquivo01.txt” a “Arquivo50.txt”.

A primeira linha dos arquivos é sempre um cabeçalho informando o nome dos campos. Porém os cabeçalhos apresentam rótulos diferentes em cada arquivo. E a quantidade de linhas de dados varia de arquivo para arquivo.

As tabelas usadas

O leitor mais atento já percebeu a descrição acima traz detalhes importantes misturados com informações irrelevantes para a sua tarefa. Mas a vida real é assim mesmo. Mesmo quando lida com um usuário com bastante conhecimento técnico, é comum que aconteçam alguns deslizes e ele suponha que alguns detalhes tenham muito mais importância do que de fato tem.

O primeiro detalhe irrelevante é sobre a variação da quantidade de registros entre os arquivos. Isso não nos afeta, até porque as ferramentas de carga de dados já estão preparadas para isso (importação roda até encontrar o marcador de final de arquivo).

O segundo é sobre os cabeçalhos variáveis. O que interessa no processo importação é o número de linhas que serão desprezadas no início de cada arquivo e a sequência das colunas. Se elas são descritas de forma diferente é irrelevante. Como eu disse, estas linhas de cabeçalho são desprezadas.

Não há nenhuma informação sobre campos que devam ser desprezados. Então eu assumo que devo importar todas as colunas do arquivo. Também não há comentário sobre existência de alguma chave nestes dados. Mas como serão feitas análises, é fundamental que seja criado um índice clusterizado nesta tabela.

Tenha em mente que o SQL Server nunca oferecerá boas performances em consultas se não existir um índice clusterizado na tabela. Isso é tão importante que o SQL Server cria automaticamente um índice clusterizado assim que se define a chave primária da tabela.

Portanto a tabela de destino deverá ter uma chave primária. Para isso, eu adiciono um campo IDENTITY nesta tabela. Veja Listagem 1:

Listagem 1: tabela de destino

Importante notar que agora temos uma tabela com um campo a mais do que os arquivos. É o campo [DestinoID], chave primária da tabela.

Neste ponto, temos que escolher entre dois caminhos. A primeira opção é criar um arquivo de formatação, especificando o mapeamento dos campos entre os arquivos de origem e a tabela de destino. A segunda é criar uma tabela de carga de dados, normalmente chamada de tabela de staging. Esta tabela teria o mesmo número de campos da fonte de dados e a mesma sequência de campos. Todos os campos da tabela de staging usam o tipo VARCHAR, evitando conversões durante o processo de importação.

Como eu disse, meu objetivo é criar um processo de carga de múltiplos arquivos que seja o mais simples possível e menos suscetível a falha. Por conta disso eu escolho usar a tabela de staging. O script de criação desta tabela é apresentado na Listagem 2.

Listagem 2: tabela de staging

O script de carga de dados

Agora é necessário tratar do comando BULK INSERT. Um detalhe que sempre causa confusão é que ao usarmos esse comando, devemos especificar um caminho de arquivo conforme ele é especificado no servidor onde roda a instância SQL.

No caso atual, os arquivos estão gravados no diretório ‘C:\TEMP’ do servidor. A  Listagem 3 mostra o comando necessário.

Listagem 3: comando BULK INSERT

Temos 50 arquivos para importar e naturalmente seria possível executar esta tarefa repetindo esta instrução para cada um dos arquivos. Porém, o legal de fazer scripts genéricos é que eles podem ser reutilizados numa gama muito grande de situações. “Reusabilidade” é algo muito importante e você deve estar atento a isso quando cria seus scripts.

Este script de importação de múltiplos arquivos é fácil de criar, mas ainda assim pode ser reaproveitado para qualquer importação de arquivos que tenham nomes sequenciais. Basta criar as variáveis adequadas. No caso, eu uso os seguintes parâmetros:

• Nome do arquivo (@vchArquivo)
• Extensão do arquivo (@vchArquivoExtensao)
• Diretório de origem (@vchDiretorioOrigem)
• Nome completo da tabela de destino (@vchTabelaDestino)
• Quantidade de arquivos a importar (@intQTDArquivos)
• Número de linhas de cabeçalho (@intLinhasCabecalho)
• Separador de colunas (@vchColuna)
• Identificador de linha (@vchLinha)

A instrução inteira precisa ser montada e guardada numa outra variável que então será executada com a instrução EXEC(). A Listagem 4 mostra este script parametrizado de carga de dados.

Listagem 4: script de carga

O script de conversão de tipos de dados

O passo final é a conversão dos dados para os tipos de dados adequados. Eu costumo tratar esta tarefa em separado da carga de dados, porque é praticamente impossível generalizar qualquer tipo de tratamento. A conversão depende essencialmente do layout do arquivo de dados e, portanto, não há como generalizá-la sem restringir o layout considerado.

Neste estudo, o layout envolve campos VARCHAR na maioria dos casos. Existem apenas três exceções:

• Campo TipoPessoa, usando CHAR(2)
• Campo EmailPromo, que tem valores tipo INTEGER
• Campo DataAtualizacao, com tipo DATE (entenda-se mm/dd/yy)

Na tabela de staging todos os campos usam VARCHAR. Mas as conversões de VARCHAR para CHAR ou para INTEGER são automáticas. É preciso tratar apenas da conversão das datas. Elas estão registradas no formato “dd.mm.yyyy”.

Observe que a conversão seria automática se o formato da coluna data fosse “mm/dd/yyyy”.  Mas no nosso caso é necessário haver um tratamento. É aqui que entra um truque que eu encontrei tempos atrás num fórum. É uma sugestão de Grzegorz Oledzki (veja Referências) e é um recurso que eu mantenho na minha “caixa de ferramentas”.

Convertemos as datas usando a função CONVERT(),  especificando o novo tipo de dados como DATE e o parâmetro de formatação 103, que representa o formato “dd/mm/yyyy”.

A Listagem 5 mostra a declaração INSERT com a conversão necessária:

Listagem 5: populando a tabela de destino

Conclusão

Carga de dados é um tópico muito importante para qualquer sistema novo. Neste aspecto, dispor de um script simples e versátil como aquele apresentado neste artigo pode ser de grande utilidade para desenvolvedores e DBAs.

Em resumo, o processo apresentado aqui envolve cinco etapas:

1. Criação de uma tabela de staging com a mesma sequência de colunas dos arquivos de dados, usando sempre campos VARCHAR();
2. Criação de uma tabela de pesquisa que use os tipos de dados adequados para cada coluna e também inclua uma chave primária;
3. Adaptação (se necessário) dos nomes de arquivos de texto para que sejam sequenciais;
4. Identificação dos parâmetros do processo de importação;
5. Definição das transformações necessárias entre a tabela de staging e a de pesquisa.

Esta é uma solução bastante versátil e de implementação simples. E espero que lhe seja útil assim como tem sido para mim.

Referências

MICROSOFT. BULK INSERT (Transact-SQL). MSDN. MICROSOFT CORP
OLEDZKI, Grzegorz. How to convert a “dd/mm/yyyy” string to datetime in SQL Server? STACKOVERFLOW. Maio/2010
MICROSOFT. CAST and CONVERT (Transact-SQL). MSDN. MICROSOFT CORP