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

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

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

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

Exemplo de utilização:

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

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

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();
        }

    }
}

Exemplo de utilização:

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:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

Exemplo de utilização:

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 “;”.