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 AutomationComo 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE FUNCTION [dbo].[fncLer_Arquivo_FSO] ( @Ds_Arquivo VARCHAR(256) ) RETURNS @Tabela_Final TABLE ( Ds_Linha VARCHAR(8000) ) AS BEGIN DECLARE @OLEResult INT DECLARE @FileSystemObject INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @Tabela TABLE ( Ds_Linha varchar(8000) ) EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUT IF @OLEResult <> 0 BEGIN SET @Message = 'Scripting.FileSystemObject - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXEC @OLEResult = sp_OAMethod @FileSystemObject, 'OpenTextFile', @FileID OUT, @Ds_Arquivo, 1, 1 IF @OLEResult <> 0 BEGIN SET @Message = 'OpenTextFile - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT WHILE (@OLEResult >= 0) BEGIN INSERT INTO @Tabela(Ds_Linha) VALUES( @Message ) EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT END EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FileSystemObject INSERT INTO @Tabela_Final SELECT Ds_Linha FROM @Tabela RETURN END |
Código-fonte da função fncLer_Arquivo_String_FSO
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
CREATE FUNCTION [dbo].[fncLer_Arquivo_String_FSO] ( @Ds_Arquivo VARCHAR(256) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @OLEResult INT DECLARE @FileSystemObject INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @Retorno VARCHAR(MAX) EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUT IF @OLEResult <> 0 BEGIN SET @Message = 'Scripting.FileSystemObject - Error code: ' + CONVERT (VARCHAR, @OLEResult) RETURN @Message END EXEC @OLEResult = sp_OAMethod @FileSystemObject, 'OpenTextFile', @FileID OUT, @Ds_Arquivo, 1, 1 IF @OLEResult <> 0 BEGIN SET @Message = 'OpenTextFile - Error code: ' + CONVERT (VARCHAR, @OLEResult) RETURN @Message END EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT SET @Retorno = ISNULL(@Retorno, '') + ISNULL(@Message, '') + CHAR(13) WHILE (@OLEResult >= 0) BEGIN SET @Message = NULL EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT SET @Retorno = ISNULL(@Retorno, '') + ISNULL(@Message, '') + CHAR(13) END EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FileSystemObject RETURN @Retorno END |
Usage example:
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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
using System.IO; using System.Collections; using System.Data.SqlTypes; public partial class UserDefinedFunctions { private class ArquivoLer { public SqlInt32 Nr_Linha; public SqlString Ds_Texto; public ArquivoLer(SqlInt32 nrLinha, SqlString dsTexto) { Nr_Linha = nrLinha; Ds_Texto = dsTexto; } } [Microsoft.SqlServer.Server.SqlFunction( FillRowMethodName = "FillRow_Arquivo_Ler", TableDefinition = "Nr_Linha INT, Ds_Texto NVARCHAR(MAX)" )] public static IEnumerable fncArquivo_Ler(string Ds_Caminho) { var ArquivoLerCollection = new ArrayList(); if (string.IsNullOrEmpty(Ds_Caminho)) return ArquivoLerCollection; var contador = 1; using (var sr = new StreamReader(Ds_Caminho)) { while (sr.Peek() >= 0) { ArquivoLerCollection.Add(new ArquivoLer( contador, sr.ReadLine() )); contador++; } sr.Close(); } return ArquivoLerCollection; } protected static void FillRow_Arquivo_Ler(object objArquivoLer, out SqlInt32 nrLinha, out SqlString dsTexto) { var ArquivoLer = (ArquivoLer) objArquivoLer; nrLinha = ArquivoLer.Nr_Linha; dsTexto = ArquivoLer.Ds_Texto; } } |
Código-fonte da função fncArquivo_Ler_Retorna_String
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System.Data.SqlTypes; using System.IO; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString fncArquivo_Ler_Retorna_String(SqlString Ds_Caminho) { if (Ds_Caminho.IsNull) return SqlString.Null; if (!File.Exists(Ds_Caminho.Value)) return SqlString.Null; using (var sr = new StreamReader(Ds_Caminho.Value)) { return sr.ReadToEnd(); } } } |
Usage example:
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
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:
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO |
Usage example:
onde:
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:
Obs: Como vocês podem notar, linhas em branco são importadas como NULL pelo BULK INSERT.
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:
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.
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:
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)
And that's it, folks!
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
Portuguese 
English