SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Olá pessoal,
Bom dia!

Neste post eu gostaria de mostrar pra vocês, diferentes formas de importar o conteúdo um arquivo de texto para uma variável no SQL Server, para que você possa utilizar essa informação da forma que atenda a sua necessidade.

Eu já postei algumas formas de se fazer isso aqui no blog, mas em tópicos diferentes em com finalidades diferentes, então gostaria de repostar essa soluções, além de incluir mais algumas que ainda não tinha mencionado aqui e assim, centralizar todas essas abordagens.

OLE Automation

Como importar arquivos de texto para o banco com OLE Automation

Para quem não conhece esse recurso, ele permite que o DBA ou Desenvolvedor execute uma série de ações no banco de dados usando OLE DB, como leitura/escrita/movimentação/cópia/deleção de arquivos, criação de planilhas no Excel e mais uma série de coisas. A sintaxe é um pouco parecida com o VBA e utiliza a API do Windows para essas operações.

Para essa finalidade, vamos utilizar as funções fncLer_Arquivo_FSO e fncLer_Arquivo_String_FSO, conforme demonstrado abaixo:

Código-fonte da função fncLer_Arquivo_FSO

Código-fonte da função fncLer_Arquivo_String_FSO

Exemplo de utilização:
SQL Server - How to import text files with OLE Automation

Gostaria de conhecer um pouco mais sobre OLE Automation?
Habilitando OLE Automation via T-SQL no SQL Server
Operações com arquivos utilizando OLE Automation no SQL Server
Consumindo a API do Google Maps usando OLE Automation no SQL Server
Como calcular o valor do frete e prazo de entrega utilizando WebService dos Correios no SQL Server
Consultando o rastreamento de objetos dos Correios pelo SQL Server
Consumindo a API do Google Maps para obter informações de um endereço ou CEP no SQL Server
Como consultar informações de um CEP no SQL Server

Common Language Runtime (CLR)

Como importar arquivos de texto para o banco com CLR

O CLR permite que você consiga criar rotinas (stored prodecures, functions, triggers, etc) escritas em C#, F# e VB.NET, compilá-las e executá-las no banco de dados nativamente, estendendo as capacidades do SGBD, pois é possível criar uma infinidade de coisas que não seriam possíveis utilizando apenas o Transact-SQL, como manipulação de arquivos, upload e download de arquivos via FTP, funções de agregação, integração com Webservices e muito mais.

Para essa finalidade, vamos utilizar as funções fncArquivo_Ler e fncArquivo_Ler_Retorna_String, conforme demonstrado abaixo:

Código-fonte da função fncArquivo_Ler

Código-fonte da função fncArquivo_Ler_Retorna_String

Exemplo de utilização:
SQL Server - How to import text files with CLR (C#)

Gostaria de conhecer um pouco mais sobre CLR?
Introdução ao SQL CLR (Common Language Runtime) no SQL Server
SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#)
Realizando requisições POST e GET utilizando CLR (C#) no SQL Server

BCP (Bulk Copy)

Como importar arquivos de texto para o banco com BCP

Essa é uma das opções mais utilizadas pelos DBA’s, pois é simples, já vem instalada com o SQL Server e pode ser executada tanto em packages SSIS quanto em stored procedures (utilizando xp_cmdshell)

Lembrando que para utilizar o BCP, você precisará ativar o recurso xp_cmdshell. Eu particularmente não gosto de utilizar o xp_cmdshell e nem de deixar habilitado em uma instância, pois permite inúmeras vulnerabilidades e qualquer comando do Prompt do Windows pode ser executado com esse recurso habilitado.

Para ativar a feature e permitir o xp_cmdshell, execute os seguintes comandos:

Exemplo de utilização:
SQL Server - How to import text files with BCP

onde:
BCP Help

BULK INSERT

Como importar arquivos de texto para o banco com BULK INSERT

Uma solução que não é tão conhecida como as outras mas igualmente útil e poderosa, o BULK INSERT é um comando nativo do SQL Server que permite importar arquivos texto e CSV sem precisar de nenhum recurso externo ou habilitar nada na sua instância. É a forma mais simples de começar a importar arquivos para o seu banco de dados.

Exemplos de utilização:
SQL Server - How to import text files with BULK INSERT

Obs: Como vocês podem notar, linhas em branco são importadas como NULL pelo BULK INSERT.

OPENROWSET(BULK)

Como importar arquivos de texto para o banco com OPENROWSET(BULK)

Outra solução nativa do banco de dados e muito rápida, é a utilização do OPENROWSET junto com a opção BULK para carregar arquivos de texto para o banco de dados. Ela é muito utilizada para importação de arquivos XML e vou mostrar uma forma fácil de importar arquivos de texto:

Exemplos de utilização:
SQL Server - Import text txt file openrowset bulk

Onde:

  • SINGLE_BLOB retorna o conteúdo do arquivo como varbinary(max) – Recomendo para arquivos XML
  • SINGLE_CLOB retorna o conteúdo do arquivo como varchar(max)
  • SINGLE_NCLOB retorna o conteúdo do arquivo como nvarchar(max)

Para arquivos textos, eu geralmente uso SINGLE_CLOB, mas quando a codificação do arquivo for UNICODE, você deve usar o SINGLE_NCLOB, caso contrário, irá se deparar com essa mensagem de erro:

SINGLE_CLOB requires a double-byte character set (DBCS) (char) input file. The file specified is Unicode.

OPENROWSET(OLEDB)

Como importar arquivos de texto para o banco com OPENROWSET(OLEDB)

Uma outra alternativa para importar arquivos de texto é utilizar novamente o OPENROWSET, mas agora utilizando o provider Microsoft ACE OLEDB. Essa solução serve para importar tanto arquivos de texto, retornando cada linha do arquivo como um registros de uma tabela, quanto para importar arquivos CSV.

Lembre-se que o provider “Microsoft.ACE.OLEDB.12.0” não vem instalado por padrão no SQL Server. Ele precisa ser instalado manualmente. Para saber mais sobre isso, veja meu post SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0.

Exemplos de utilização:
SQL Server - Import text txt file openrowset microsoft ace oledb 12.0

Para importar arquivos CSV, você deve utilizar os parâmetros HDR=Yes;FORMAT=Delimited(;). HDR=Yes que dizer que a primeira linha do arquivo é o cabeçalho e FORMAT=Delimited(;) quer dizer que as colunas serão separadas pelo caracter “;”.

Uma das vantagens do CLR e do OLE Automation é o fato de serem funções ao invés de SP, o que lhes permite utilizar o recurso dentro de outras funções. Além disso, linhas vazias são retornadas como strings vazias, enquanto nas outras duas soluções são retornados como NULL.

Caso você queira saber como exportar os dados do banco de dados para arquivos de texto, saiba mais acessando o post SQL Server – Como exportar dados do banco para arquivo texto (CLR, OLE, BCP)

É isso aí, pessoal!
Obrigado pela visita e até o próximo post.

sql importar dados de arquivo de texto txt para o banco de dados import data from text files to database

sql importar dados de arquivo de texto txt para o banco de dados import data from text files to database

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Deixe uma resposta