SQL Server - Cómo importar archivos de texto a la base (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Cómo importar archivos de texto a la base de datos con OLE Automation

Para aquellos que no están familiarizados con esta característica, permite al DBA o desarrollador realizar una serie de acciones en la base de datos usando OLE DB, como leer/escribir/mover/copiar/eliminar archivos, crear hojas de cálculo en Excel y una serie de cosas más. La sintaxis es un poco similar a VBA y utiliza la API de Windows para estas operaciones.

Para ello utilizaremos las funciones fncLer_Arquivo_FSO y fncLer_Arquivo_String_FSO, como se muestra a continuación:

Código fuente de la función fncRead_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 fuente de la función fncRead_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

Ejemplo de uso:

¿Te gustaría saber un poco más sobre la Automatización OLE?
– Habilitación de la automatización OLE a través de T-SQL en SQL Server
– Operaciones de archivos usando OLE Automation en SQL Server
– Consumiendo la API de Google Maps usando OLE Automation en SQL Server
– Cómo calcular el costo de envío y el tiempo de entrega usando Correios WebService en SQL Server
– Consultar el seguimiento de objetos de Correios usando SQL Server
– Consumir la API de Google Maps para obtener información de una dirección o código postal en SQL Server
– Cómo consultar información de un CEP en SQL Server

Cómo importar archivos de texto a la base de datos con CLR

El CLR permite crear rutinas (procedimientos almacenados, funciones, disparadores, etc.) escritas en C#, F# y VB.NET, compilarlas y ejecutarlas en la base de datos de forma nativa, ampliando las capacidades del DBMS, ya que es posible crear multitud de cosas que no serían posibles usando solo Transact-SQL, como manipulación de archivos, carga y descarga de archivos vía FTP, funciones de agregación, integración con Webservices y mucho más.

Para ello utilizaremos las funciones fncArquivo_Ler y fncArquivo_Ler_Retorna_String, como se muestra a continuación:

Código fuente de la función fncFile_Read

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 fuente de la función 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();
        }

    }
}

Ejemplo de uso:

¿Quieres saber un poco más sobre CLR?
– Introducción a SQL CLR (Common Language Runtime) en SQL Server
– SQL Server: cómo enumerar, leer, escribir, copiar, eliminar y mover archivos con CLR (C#)
– Realizar solicitudes POST y GET usando CLR (C#) en SQL Server

Cómo importar archivos de texto al banco con BCP

Esta es una de las opciones más utilizadas por los DBA, ya que es sencilla, ya está instalada con SQL Server y se puede ejecutar tanto en paquetes SSIS como en procedimientos almacenados (usando xp_cmdshell).

Recuerde que para utilizar BCP, deberá activar la función xp_cmdshell. Personalmente, no me gusta usar xp_cmdshell o dejarlo habilitado en una instancia, ya que permite numerosas vulnerabilidades y cualquier comando de Windows Prompt se puede ejecutar con esta función habilitada.

Para activar la función y habilitar xp_cmdshell, ejecute los siguientes comandos:

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

Ejemplo de uso:

dónde:

Cómo importar archivos de texto a la base de datos con BULK INSERT

BULK INSERT, una solución que no es tan conocida como las demás pero igualmente útil y poderosa, es un comando nativo de SQL Server que le permite importar archivos de texto y CSV sin necesidad de recursos externos ni habilitar nada en su instancia. Es la forma más sencilla de comenzar a importar archivos a su base de datos.

Ejemplos de uso:

Nota: Como puede ver, BULK INSERT importa las líneas en blanco como NULL.

Cómo importar archivos de texto a la base de datos con OPENROWSET(BULK)

Otra solución muy rápida y nativa de la base de datos es usar OPENROWSET junto con la opción BULK para cargar archivos de texto en la base de datos. Se usa ampliamente para importar archivos XML y le mostraré una manera fácil de importar archivos de texto:

Ejemplos de uso:

Dónde:

• SINGLE_BLOB devuelve el contenido del archivo como varbinary(max): recomendado para archivos XML
• SINGLE_CLOB devuelve el contenido del archivo como varchar(max)
• SINGLE_NCLOB devuelve el contenido del archivo como nvarchar(max)

Para archivos de texto, generalmente uso SINGLE_CLOB, pero cuando la codificación del archivo es UNICODE, debe usar SINGLE_NCLOB; de lo contrario, encontrará este mensaje de error:

SINGLE_CLOB requiere un archivo de entrada de juego de caracteres de doble byte (DBCS) (char). El archivo especificado es Unicode.

Cómo importar archivos de texto a la base de datos con OPENROWSET(OLEDB)

Otra alternativa a la importación de archivos de texto es volver a utilizar OPENROWSET, pero ahora utilizando el proveedor Microsoft ACE OLEDB. Esta solución se utiliza para importar ambos archivos de texto, devolviendo cada línea del archivo como un registro de tabla, y para importar archivos CSV.

Recuerde que el proveedor “Microsoft.ACE.OLEDB.12.0” no está instalado por defecto en SQL Server. Debe instalarse manualmente. Para saber más sobre esto, mira mi publicación. SQL Server: cómo instalar los controladores Microsoft.ACE.OLEDB.12.0 y Microsoft.Jet.OLEDB.4.0.

Ejemplos de uso:

Para importar archivos CSV, debe utilizar los parámetros HDR=Sí;FORMATO=Delimitado(;). HDR=Sí, lo que significa que la primera línea del archivo es el encabezado y FORMATO=Delimitado(;), lo que significa que las columnas estarán separadas por “;” personaje.