Pesquisar este blog

domingo, 14 de agosto de 2011

Importar um arquivo texto direto para o SQL Server

Imagine que você tem um arquivo texto, delimitado por ponto-e-vírgula (um CSV) e você deseja inserí-lo diretamente dentro de uma tabela. Porém, eu não vou mostrar como fazer isso no Management Studio, que é fácil. Eu vou mostrar como fazer isso via T-SQL! E porque? Bom, para mim isto foi muito útil para automatizar uma rotina de comunicação entre sistemas, rodando automaticamente.

Vamos então para a parte prática. É muito simples, primeiro analise o arquivo texto a ser importado, e identifique todas as colunas do arquivo com seu respectivo tipo de dado (data, texto, número, money, decimal, etc). É muito importante este trabalho de análise, pois será necessário criar uma tabela exatamente com as mesmas especificações.

Vamos ver um exemplo, lançamentos contábeis. Eu tenho aqui um arquivo texto, delimitado por vírgulas gerado a partir de um sistema ERP tosco em que a contabilidade não funciona, e nós precisávamos importar para um que funcionasse, de fato, porém em SQL Server. Este tipo de coisa sempre surge no meu dia a dia, a quantidade de sistemas e a necessidade de comunicação entre eles tem crescido exponencialmente.

Uma sugestão é que sempre (quando possível) abra o arquivo no excel, estude a estrutura das colunas e seus valores, veja se não existem coisas que podem impactar na sua importação (como por exemplo aspas simples ‘ , arquivo delimitado por ponto e vírgula e o infeliz usar ponto-e-vírgula na observação do lançamento e o sistema não tratar isso) entre outras coisas… o excel ajuda muito, nomeie as colunas, insira observações, planeje, para depois partir para  parte prática.

*desculpem pela formatação do blog, estou revendo este layout para permitir inserir grandes imagens…

Vamos olhar o arquivo:

 

image

 

Então temos algum punhado de colunas, algumas DATETIME compatível com SQL Server, outros valores texto e money, e assim por diante. Para que possamos executar esta tarefa diretamente via T-SQL, é necessário criar a tabela com precisão, para que não ocorram erros de importação, que podem comprometer a qualidade da ferramenta desenvolvida. Para o exemplo acima, eu criei a seguinte tabela:

 

  1. CREATE TABLE [dbo].[ctb_movimento_202011](
  2.     [Coluna 0]  bigint NULL,
  3.     [Coluna 1]  bigint NULL,
  4.     [Coluna 2]  bigint NULL,
  5.     [Coluna 3]  datetime NULL,
  6.     [Coluna 4]  varchar(10) NULL,
  7.     [Coluna 5]  varchar(500) NULL,
  8.     [Coluna 6]  varchar(1) NULL,
  9.     [Coluna 7]  money NULL,
  10.     [Coluna 8]  bigint NULL,
  11.     [Coluna 9]  varchar(50) NULL,
  12.     [Coluna 10] bigint NULL,
  13.     [Coluna 11] varchar(1) NULL,
  14.     [Coluna 12] bigint NULL,
  15.     [Coluna 13] bigint NULL,
  16.     [Coluna 14] bigint NULL,
  17.     [Coluna 15] bigint NULL,
  18.     [Coluna 16] bigint NULL,
  19.     [Coluna 17] bigint NULL,
  20.     [Coluna 18] bigint NULL,
  21.     [Coluna 19] bigint NULL,
  22.     [Coluna 20] bigint NULL
  23. )

 

Coisas de programador… coloquei estes nomes de coluna por que previamente havia feito o mapeamento em outra ferramenta (excel). Enfim, a parte prática. Para importar o arquivo, basta utilizar uma ferramenta muito útil, chamada BULK INSERT (inclusive, muito mais ampla do que este simples exemplo). Bulk significa “grande volume”, e Insert é inserir. Então vamos inserir um grande volume de dados em uma tabela.

Vamos fazer um único exemplo, porém acredito que seja o mais comum deste tipo de aplicação, o arquivo delimitado por um caracter de controle.

 

  1. BULK INSERT ctb_movimento_202011
  2.     FROM 'C:\Users\nms.cores\Desktop\districomp-20052011\Cordilheira\CTBMOV_D.txt'
  3.     WITH
  4.     (
  5.         FIELDTERMINATOR = ';',
  6.         ROWTERMINATOR = '\n'
  7.     )

 

Este comando informa o nome da tabela que deverá ser inserida a informação (no caso, ctb_movimento_202011), a cláusula from (origem dos dados), onde especificamos a localização do arquivo texto, e logo em seguida alguns parâmetros para esta importação (dentro do WITH), como o FIELDTERMINATOR, que especifica qual é o delimitador para cada coluna (no caso, ponto e vírgula), e também qual é o delimitador de cada linha (\n no caso separado por salto de linha, veja no Books Online as opções disponíveis).

 

Voilà

 

image

 

image

 

Espero que seja útil!

 

image

4 comentários:

  1. Respostas
    1. e quando tem um campo NUMERICO como faz\ para inserir este campo.

      Excluir
    2. Além de ser INTEGER ele é chave estrangeira, expedito_costa2003@hotmail.com

      Excluir
  2. Olá bom dia,

    Ótimo exemplo

    Mas ainda tenho uma dúvida, caso eu precise validar se um determinado campo no arquivo já existe na minha tabela , como faço ?

    A minha importação será diariamente e preciso efetuar uma validação antes de inserir, validar se o registro já existe, entende ?

    ResponderExcluir