Importando arquivos CSV para o banco de dados SQL Server

Visualizações: 6.995 views

Tempo de Leitura: 3 minutos

Olá Pessoal!
Bom dia!

Hoje eu estou bem empolgado com essa stored procedure que vou apresentar a vocês, porque ela realmente me deu um pouco de trabalho para desenvolver. Com certeza, vocês já ouviram falar de arquivos CSV (Comma-separated values), aqueles arquivos de texto onde as informações são organizadas utilizando um delimitador, geralmente vírgula (,) ou ponto e vírgula (;).

Pois bem, semana passada surgiu uma necessidade onde eu trabalho de importar um arquivo CSV para dentro do banco de dados, onde as colunas teriam que ser criadas de acordo com o arquivo CSV. Para resolver isso, eu criei uma stored procedure que lê o arquivo CSV, identifica as colunas, e cria uma tabela dinamicamente para armazenar os dados vindos do CSV.

Na procedure, eu coloquei ainda um parâmetro opcional para informar se a primeira linha do arquivo é o cabeçalho do CSV. Neste caso, a primeira linha não será exibida no resultado final e o título das colunas é alterado dinamicamente para o nome contido naquela coluna da linha do cabeçalho.

Antes de ir ao código, essa procedure utiliza duas outras funções das quais eu já havia falado aqui no blog:

fncSplit (Visualizar post)
fncLer_Arquivo_FSO (Visualizar post)

UPDATE 20/03/2017: Caso você queira utilizar uma solução mais robusta, completa e mais performática, confira como importar dados de arquivos tabulares, com o CSV, utilizando o CLR (C#) no post SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#).

Vamos ao que interessa. Código-fonte!!

USE [CLR]
GO

CREATE PROCEDURE [dbo].[stpImporta_CSV] (
    @Ds_Caminho_Arquivo VARCHAR(MAX),
    @Ds_Separador VARCHAR(10) = ';',
    @Fl_Primeira_Linha_Cabecalho BIT = 1,
    @Ds_Tabela_Destino VARCHAR(MAX) = NULL
)
AS BEGIN

    DECLARE @tabela_bruta TABLE (
        Id INT IDENTITY(1,1),
        Ds_Linha VARCHAR(MAX)
    )

    -- Insere numa tabela temporária o conteúdo do CSV que será importado
    INSERT INTO @tabela_bruta(Ds_Linha)
    SELECT * FROM CLR.dbo.fncLer_Arquivo_FSO(@Ds_Caminho_Arquivo)

    
    DECLARE 
        @contadorColunas INT = 1,
        @numeroColunas INT = (SELECT TOP 1 LEN(Ds_Linha) - LEN(REPLACE(Ds_Linha, @Ds_Separador, '')) + LEN(@Ds_Separador) FROM @tabela_bruta),
        @query VARCHAR(MAX)
        
    
    IF (OBJECT_ID('tempdb..#Tabela_Final') IS NOT NULL) DROP TABLE #Tabela_Final
    CREATE TABLE #Tabela_Final (
        Id INT IDENTITY(1,1)
    )
    
    -- Loop para inserir as colunas de acordo com a estrutura do CSV
    WHILE(@contadorColunas <= @numeroColunas)
    BEGIN
        
        SET @query = 'ALTER TABLE #Tabela_Final ADD Ds_Coluna_' + CAST(@contadorColunas AS VARCHAR(20)) + ' VARCHAR(MAX)'
        EXEC(@query)
        
        SET @contadorColunas = @contadorColunas + 1

    END


    
    DECLARE 
        @numeroLinhas INT = (SELECT COUNT(*) FROM @tabela_bruta),
        @linha VARCHAR(MAX),
        @contadorLinhas INT = 1

    
    -- Loop para renomear as colunas de acordo com o nome do cabeçalho (se usada a Flag @Fl_Primeira_Linha_Cabecalho = 1)
    IF (@Fl_Primeira_Linha_Cabecalho = 1)
    BEGIN
        
        SET @contadorColunas = 1
        
        DECLARE 
            @cabecalho VARCHAR(MAX) = (SELECT Ds_Linha FROM @tabela_bruta WHERE Id = 1),
            @Nm_Coluna_Anterior VARCHAR(MAX),
            @Nm_Coluna_Nova VARCHAR(MAX)
        
        WHILE(@contadorColunas <= @numeroColunas)
        BEGIN
            
            SET @Nm_Coluna_Anterior = '#tabela_final.Ds_Coluna_' + CAST(@contadorColunas AS VARCHAR(20))
            SET @Nm_Coluna_Nova = (SELECT CLR.dbo.fncSplit(@cabecalho, @Ds_Separador, @contadorColunas))
            
            -- Remove aspas (se houver)
            IF (LEFT(@Nm_Coluna_Nova, 1) = '"' AND RIGHT(@Nm_Coluna_Nova, 1) = '"')
                SET @Nm_Coluna_Nova = SUBSTRING(@Nm_Coluna_Nova, 2, LEN(@Nm_Coluna_Nova) - 2)
            
            EXEC tempdb..sp_RENAME @Nm_Coluna_Anterior, @Nm_Coluna_Nova, 'COLUMN'
            SET @contadorColunas = @contadorColunas + 1
            
        END
        
        DELETE FROM @tabela_bruta WHERE id = 1
        SET @contadorLinhas = 2
    
    END


    -- Loop para inserir os dados na tabela temporária final
    
    DECLARE
        @coluna VARCHAR(MAX)
    
    WHILE(@contadorLinhas <= @numeroLinhas)
    BEGIN
        
        SET @contadorColunas = 1
        SET @linha = (SELECT Ds_Linha FROM @tabela_bruta WHERE Id = @contadorLinhas)
        
        SET @query = 'INSERT INTO #tabela_final VALUES('
        
        WHILE(@contadorColunas <= @numeroColunas)
        BEGIN
            
            SET @coluna = ISNULL(CLR.dbo.fncSplit(@linha, @Ds_Separador, @contadorColunas), '')
            
            -- Remove aspas (se houver)
            IF (LEFT(@coluna, 1) = '"' AND RIGHT(@coluna, 1) = '"')
                SET @coluna = SUBSTRING(@coluna, 2, LEN(@coluna) - 2)
            
            SET @query = @query + CHAR(39) + @coluna + CHAR(39)
            
            IF (@contadorColunas + 1 <= @numeroColunas) SET @query = @query + ','
            
            SET @contadorColunas = @contadorColunas + 1
            
        END
        
        SET @query = @query + ')'
        EXEC(@query)
        
        SET @contadorLinhas = @contadorLinhas + 1

    END 
    
    
    IF (@Ds_Tabela_Destino IS NOT NULL)
    BEGIN
    
        SET @query = 'SELECT * INTO ' + @Ds_Tabela_Destino + ' FROM #Tabela_Final'
        EXEC(@query)
    
    END
    ELSE BEGIN
    
        -- Exibe o resultado final da SP
        SELECT * FROM #Tabela_Final
    
    END
    
    
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

USE [CLR]

CREATE PROCEDURE [dbo].[stpImporta_CSV] (

@Ds_Caminho_Arquivo VARCHAR(MAX),

@Ds_Separador VARCHAR(10) = ';',

@Fl_Primeira_Linha_Cabecalho BIT = 1,

@Ds_Tabela_Destino VARCHAR(MAX) = NULL

)

AS BEGIN

DECLARE @tabela_bruta TABLE (

Id INT IDENTITY(1,1),

Ds_Linha VARCHAR(MAX)

)

-- Insere numa tabela temporária o conteúdo do CSV que será importado

INSERT INTO @tabela_bruta(Ds_Linha)

SELECT * FROM CLR.dbo.fncLer_Arquivo_FSO(@Ds_Caminho_Arquivo)

DECLARE

@contadorColunas INT = 1,

@numeroColunas INT = (SELECT TOP 1 LEN(Ds_Linha) - LEN(REPLACE(Ds_Linha, @Ds_Separador, '')) + LEN(@Ds_Separador) FROM @tabela_bruta),

@query VARCHAR(MAX)

IF (OBJECT_ID('tempdb..#Tabela_Final') IS NOT NULL) DROP TABLE #Tabela_Final

CREATE TABLE #Tabela_Final (

Id INT IDENTITY(1,1)

)

-- Loop para inserir as colunas de acordo com a estrutura do CSV

WHILE(@contadorColunas <= @numeroColunas)

BEGIN

SET @query = 'ALTER TABLE #Tabela_Final ADD Ds_Coluna_' + CAST(@contadorColunas AS VARCHAR(20)) + ' VARCHAR(MAX)'

EXEC(@query)

SET @contadorColunas = @contadorColunas + 1

END

DECLARE

@numeroLinhas INT = (SELECT COUNT(*) FROM @tabela_bruta),

@linha VARCHAR(MAX),

@contadorLinhas INT = 1

-- Loop para renomear as colunas de acordo com o nome do cabeçalho (se usada a Flag @Fl_Primeira_Linha_Cabecalho = 1)

IF (@Fl_Primeira_Linha_Cabecalho = 1)

BEGIN

SET @contadorColunas = 1

DECLARE

@cabecalho VARCHAR(MAX) = (SELECT Ds_Linha FROM @tabela_bruta WHERE Id = 1),

@Nm_Coluna_Anterior VARCHAR(MAX),

@Nm_Coluna_Nova VARCHAR(MAX)

WHILE(@contadorColunas <= @numeroColunas)

BEGIN

SET @Nm_Coluna_Anterior = '#tabela_final.Ds_Coluna_' + CAST(@contadorColunas AS VARCHAR(20))

SET @Nm_Coluna_Nova = (SELECT CLR.dbo.fncSplit(@cabecalho, @Ds_Separador, @contadorColunas))

-- Remove aspas (se houver)

IF (LEFT(@Nm_Coluna_Nova, 1) = '"' AND RIGHT(@Nm_Coluna_Nova, 1) = '"')

SET @Nm_Coluna_Nova = SUBSTRING(@Nm_Coluna_Nova, 2, LEN(@Nm_Coluna_Nova) - 2)

EXEC tempdb..sp_RENAME @Nm_Coluna_Anterior, @Nm_Coluna_Nova, 'COLUMN'

SET @contadorColunas = @contadorColunas + 1

END

DELETE FROM @tabela_bruta WHERE id = 1

SET @contadorLinhas = 2

END

-- Loop para inserir os dados na tabela temporária final

DECLARE

@coluna VARCHAR(MAX)

WHILE(@contadorLinhas <= @numeroLinhas)

BEGIN

SET @contadorColunas = 1

SET @linha = (SELECT Ds_Linha FROM @tabela_bruta WHERE Id = @contadorLinhas)

SET @query = 'INSERT INTO #tabela_final VALUES('

WHILE(@contadorColunas <= @numeroColunas)

BEGIN

SET @coluna = ISNULL(CLR.dbo.fncSplit(@linha, @Ds_Separador, @contadorColunas), '')

-- Remove aspas (se houver)

IF (LEFT(@coluna, 1) = '"' AND RIGHT(@coluna, 1) = '"')

SET @coluna = SUBSTRING(@coluna, 2, LEN(@coluna) - 2)

SET @query = @query + CHAR(39) + @coluna + CHAR(39)

IF (@contadorColunas + 1 <= @numeroColunas) SET @query = @query + ','

SET @contadorColunas = @contadorColunas + 1

END

SET @query = @query + ')'

EXEC(@query)

SET @contadorLinhas = @contadorLinhas + 1

END

IF (@Ds_Tabela_Destino IS NOT NULL)

BEGIN

SET @query = 'SELECT * INTO ' + @Ds_Tabela_Destino + ' FROM #Tabela_Final'

EXEC(@query)

END

ELSE BEGIN

-- Exibe o resultado final da SP

SELECT * FROM #Tabela_Final

END

Forma de utilização:

-- Importa o arquivo "C:\Arquivos\Clientes.csv" onde as colunas são separadas por ";" e a primeira coluna é o cabeçalho
EXEC CLR.dbo.stpImporta_CSV
       @Ds_Caminho_Arquivo = 'C:\Arquivos\Clientes.csv',
       @Ds_Separador = ';',
       @Fl_Primeira_Linha_Cabecalho = 1

-- Importa o arquivo "C:\Arquivos\Clientes.csv" onde as colunas são separadas por ";" e a primeira coluna é o cabeçalho

EXEC CLR.dbo.stpImporta_CSV

@Ds_Caminho_Arquivo = 'C:\Arquivos\Clientes.csv',

@Ds_Separador = ';',

@Fl_Primeira_Linha_Cabecalho = 1

Arquivo original:

Tabela importada no banco de dados com o comando acima:

É isso aí pessoal!
Gostaram?

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

Importando arquivos CSV para o banco de dados SQL Server

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

You may also like...

Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server

Os comandos SET do SQL Server

SQL Server Configuration Manager sumiu? Saiba como recuperar o atalho

2 Responses

Deixe uma respostaCancelar resposta