{"id":2908,"date":"2014-10-21T09:04:31","date_gmt":"2014-10-21T12:04:31","guid":{"rendered":"http:\/\/blog.dialhost.com.br\/?p=2908"},"modified":"2014-10-21T09:04:31","modified_gmt":"2014-10-21T12:04:31","slug":"importando-multiplos-arquivos-texto","status":"publish","type":"post","link":"https:\/\/www.dialhost.com.br\/blog\/importando-multiplos-arquivos-texto\/","title":{"rendered":"Importando m\u00faltiplos arquivos texto"},"content":{"rendered":"
\"multiplos<\/a>
Imagem ilustrativa<\/figcaption><\/figure>\n

Faz alguns meses que publiquei aqui nesta coluna um artigo descrevendo alguns problemas comuns com arquivos texto. Separadores de colunas, identificadores de linhas, caracteres \u201cproibidos\u201d etc. Desta vez eu apresento uma solu\u00e7\u00e3o simples para importa\u00e7\u00e3o de m\u00faltiplos arquivos texto no SQL Server. De quebra, eu uso tamb\u00e9m um truque muito interessante apresentado por Grzegorz Oledzki<\/a> para convers\u00e3o de texto no formato \u2018DD-MM-YYYY\u2019 em datas reconhecidas pelo SGBD.<\/p>\n

Alternativas de importa\u00e7\u00e3o<\/strong><\/p>\n

Existem v\u00e1rios comandos e ferramentas que auxiliam na importa\u00e7\u00e3o de dados. Alguns s\u00e3o simples e pr\u00e1ticos, outros mais complexos e robustos. Basta pensar que, em \u00faltima an\u00e1lise, um comando BULK INSERT, uma opera\u00e7\u00e3o de DATA FLOW do SSIS e o utilit\u00e1rio BCP fazem a mesma coisa: importam dados.<\/p>\n

Eu sou f\u00e3 da simplicidade do comando BULK INSERT, apesar dele ter limita\u00e7\u00f5es importantes. Talvez a mais s\u00e9ria destas limita\u00e7\u00f5es seja n\u00e3o suportar arquivos CSV, que s\u00e3o muito comuns quando se trata de importa\u00e7\u00e3o de dados.<\/p>\n

Por outro lado, o comando BULK INSERT oferece uma s\u00e9rie de benef\u00edcios interessantes. Alguns deles s\u00e3o:<\/p>\n

    \n
  1. Refor\u00e7a a valida\u00e7\u00e3o de tipos de dados;<\/li>\n
  2. Valida restri\u00e7\u00f5es do modelo (como chaves estrangeiras);<\/li>\n
  3. Permite configurar delimitadores de colunas e marcadores de final de linha;<\/li>\n
  4. Permite definir a p\u00e1gina de c\u00f3digos, para trabalhar com caracteres diferentes do ASCII;<\/li>\n
  5. Controla o fluxo de importa\u00e7\u00e3o, comitando transa\u00e7\u00f5es a cada N registros importados;<\/li>\n
  6. Permite desabilitar gatilhos que seriam disparados em consequ\u00eancia da inser\u00e7\u00e3o dos novos registros;<\/li>\n
  7. Permite defini\u00e7\u00e3o de layouts de importa\u00e7\u00e3o, essencial nos casos em que o arquivo importado tem n\u00famero diferente de colunas em rela\u00e7\u00e3o \u00e0 tabela de destino;<\/li>\n
  8. Permite definir as linhas inicial e final do arquivo de dados, desprezando eventuais cabe\u00e7alhos ou rodap\u00e9s no arquivo importado;<\/li>\n
  9. Controla o tipo de \u201clock\u201d sobre a tabela;<\/li>\n
  10. Possibilita cargas de dados em paralelo, aumentando dramaticamente a velocidade do processo;<\/li>\n
  11. Dispensa log da opera\u00e7\u00e3o de carga de dados e, portanto, n\u00e3o tem impacto sobre o \u201ctransaction log\u201d da base;<\/li>\n
  12. Especifica um arquivo de output para registrar erros da importa\u00e7\u00e3o.<\/li>\n<\/ol>\n

     <\/p>\n

    Estudo de caso<\/strong><\/p>\n

    Considere que voc\u00ea recebeu uma solicita\u00e7\u00e3o especial de um diretor da sua empresa. Ele lhe passou um conjunto de 50 arquivos texto e pediu para import\u00e1-los num banco de dados para fazer algumas an\u00e1lises.<\/p>\n

    Ele fez quest\u00e3o de passar detalhes do seu pedido: todos os arquivos tem o mesmo layout, exatamente a mesma sequ\u00eancia de campos. Usam o mesmo separador de colunas (caractere de tabula\u00e7\u00e3o \u201c\\t\u201d) e o mesmo identificador de final do registro (caractere de retorno de linha \u201c\\n\u201d). Os 50 arquivos recebem nomes sequenciais, que v\u00e3o de \u201cArquivo01.txt\u201d a \u201cArquivo50.txt\u201d.<\/p>\n

    A primeira linha dos arquivos \u00e9 sempre um cabe\u00e7alho informando o nome dos campos. Por\u00e9m os cabe\u00e7alhos apresentam r\u00f3tulos diferentes em cada arquivo. E a quantidade de linhas de dados varia de arquivo para arquivo.<\/p>\n

    As tabelas usadas<\/strong><\/p>\n

    O leitor mais atento j\u00e1 percebeu a descri\u00e7\u00e3o acima traz detalhes importantes misturados com informa\u00e7\u00f5es irrelevantes para a sua tarefa. Mas a vida real \u00e9 assim mesmo. Mesmo quando lida com um usu\u00e1rio com bastante conhecimento t\u00e9cnico, \u00e9 comum que aconte\u00e7am alguns deslizes e ele suponha que alguns detalhes tenham muito mais import\u00e2ncia do que de fato tem.<\/p>\n

    O primeiro detalhe irrelevante \u00e9 sobre a varia\u00e7\u00e3o da quantidade de registros entre os arquivos. Isso n\u00e3o nos afeta, at\u00e9 porque as ferramentas de carga de dados j\u00e1 est\u00e3o preparadas para isso (importa\u00e7\u00e3o roda at\u00e9 encontrar o marcador de final de arquivo).<\/p>\n

    O segundo \u00e9 sobre os cabe\u00e7alhos vari\u00e1veis. O que interessa no processo importa\u00e7\u00e3o \u00e9 o n\u00famero de linhas que ser\u00e3o desprezadas no in\u00edcio de cada arquivo e a sequ\u00eancia das colunas. Se elas s\u00e3o descritas de forma diferente \u00e9 irrelevante. Como eu disse, estas linhas de cabe\u00e7alho s\u00e3o desprezadas.<\/p>\n

    N\u00e3o h\u00e1 nenhuma informa\u00e7\u00e3o sobre campos que devam ser desprezados. Ent\u00e3o eu assumo que devo importar todas as colunas do arquivo. Tamb\u00e9m n\u00e3o h\u00e1 coment\u00e1rio sobre exist\u00eancia de alguma chave nestes dados. Mas como ser\u00e3o feitas an\u00e1lises, \u00e9 fundamental que seja criado um \u00edndice clusterizado nesta tabela.<\/p>\n

    Tenha em mente que o SQL Server nunca oferecer\u00e1 boas performances em consultas se n\u00e3o existir um \u00edndice clusterizado na tabela. Isso \u00e9 t\u00e3o importante que o SQL Server cria automaticamente um \u00edndice clusterizado assim que se define a chave prim\u00e1ria da tabela.<\/p>\n

    Portanto a tabela de destino dever\u00e1 ter uma chave prim\u00e1ria. Para isso, eu adiciono um campo IDENTITY nesta tabela. Veja Listagem 1:<\/p>\n

    Listagem 1: tabela de destino<\/p>\n


    <\/p>\n\n\n\n
    1<\/code><\/td>\nCREATE<\/code> TABLE<\/code> dbo.tbDestino(<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    2<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>DestinoID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>int<\/code> identity(1,1) <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    3<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>TipoPessoa\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>char<\/code>(2) <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    4<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>NomeCompleto\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(200) <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    5<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Titulo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(8) <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    6<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Prenome\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50) <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    7<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>NomeDoMeio\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50) <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    8<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Sobrenome\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50) <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    9<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Sufixo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50) <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    10<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>EmailPromo\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>int<\/code> NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    11<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>DataAtualizacao datetime <\/code>NOT<\/code> NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    12<\/code><\/td>\n\u00a0<\/code>CONSTRAINT<\/code> PK_Destino <\/code>PRIMARY<\/code> KEY<\/code> CLUSTERED (DestinoID) <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    13<\/code><\/td>\n) <\/code>ON<\/code> PRIMARY<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    14<\/code><\/td>\nGO<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Importante notar que agora temos uma tabela com um campo a mais do que os arquivos. \u00c9 o campo [DestinoID], chave prim\u00e1ria da tabela.<\/p>\n

    Neste ponto, temos que escolher entre dois caminhos. A primeira op\u00e7\u00e3o \u00e9 criar um arquivo de formata\u00e7\u00e3o, especificando o mapeamento dos campos entre os arquivos de origem e a tabela de destino. A segunda \u00e9 criar uma tabela de carga de dados, normalmente chamada de tabela de staging. Esta tabela teria o mesmo n\u00famero de campos da fonte de dados e a mesma sequ\u00eancia de campos. Todos os campos da tabela de staging usam o tipo VARCHAR, evitando convers\u00f5es durante o processo de importa\u00e7\u00e3o.<\/p>\n

    Como eu disse, meu objetivo \u00e9 criar um processo de carga de m\u00faltiplos arquivos que seja o mais simples poss\u00edvel e menos suscet\u00edvel a falha. Por conta disso eu escolho usar a tabela de staging. O script de cria\u00e7\u00e3o desta tabela \u00e9 apresentado na Listagem 2.<\/p>\n

    Listagem 2: tabela de staging<\/p>\n


    <\/p>\n\n\n\n
    1<\/code><\/td>\nCREATE<\/code> SCHEMA<\/code> staging<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    2<\/code><\/td>\nGO<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    3<\/code><\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    4<\/code><\/td>\nCREATE<\/code> TABLE<\/code> staging.tbDestino(<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    5<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>TipoPessoa\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(2)\u00a0\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    6<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>NomeCompleto\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(200) <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    7<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Titulo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(8)\u00a0\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    8<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Prenome\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    9<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>NomeDoMeio\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    10<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Sobrenome\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    11<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>Sufixo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    12<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>EmailPromo\u00a0\u00a0\u00a0\u00a0\u00a0 <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code>,<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    13<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>DataAtualizacao <\/code>varchar<\/code>(50)\u00a0 <\/code>NULL<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    14<\/code><\/td>\n) <\/code>ON<\/code> PRIMARY<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    15<\/code><\/td>\nGO<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    O script de carga de dados<\/strong><\/p>\n

    Agora \u00e9 necess\u00e1rio tratar do comando BULK INSERT. Um detalhe que sempre causa confus\u00e3o \u00e9 que ao usarmos esse comando, devemos especificar um caminho de arquivo conforme ele \u00e9 especificado no servidor onde roda a inst\u00e2ncia SQL.<\/p>\n

    No caso atual, os arquivos est\u00e3o gravados no diret\u00f3rio \u2018C:\\TEMP\u2019 do servidor. A\u00a0 Listagem 3 mostra o comando necess\u00e1rio.<\/p>\n

    Listagem 3: comando BULK INSERT<\/p>\n


    <\/p>\n\n\n\n
    1<\/code><\/td>\nBULK <\/code>INSERT<\/code> staging.tbDestino <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    2<\/code><\/td>\nFROM<\/code> 'C:\\Temp\\Arquivo01.txt'<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    3<\/code><\/td>\nWITH<\/code> (<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    4<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>FIELDTERMINATOR =<\/code>'\\t'<\/code>, <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    5<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>ROWTERMINATOR = <\/code>'\\n'<\/code>, <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    6<\/code><\/td>\n\u00a0\u00a0\u00a0\u00a0<\/code>FIRSTROW = 12<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    7<\/code><\/td>\n)<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    \n\n\n\n
    8<\/code><\/td>\nGO<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

    Temos 50 arquivos para importar e naturalmente seria poss\u00edvel executar esta tarefa repetindo esta instru\u00e7\u00e3o para cada um dos arquivos. Por\u00e9m, o legal de fazer scripts gen\u00e9ricos \u00e9 que eles podem ser reutilizados numa gama muito grande de situa\u00e7\u00f5es. \u201cReusabilidade\u201d \u00e9 algo muito importante e voc\u00ea deve estar atento a isso quando cria seus scripts.<\/p>\n

    Este script de importa\u00e7\u00e3o de m\u00faltiplos arquivos \u00e9 f\u00e1cil de criar, mas ainda assim pode ser reaproveitado para qualquer importa\u00e7\u00e3o de arquivos que tenham nomes sequenciais. Basta criar as vari\u00e1veis adequadas. No caso, eu uso os seguintes par\u00e2metros:<\/p>\n