Importing CSV files into SQL Server database

Hey guys!
Good morning!

Today I'm really excited about this stored procedure that I'm going to present to you, because it really took me a bit of work to develop. You've certainly heard of CSV files (Comma-separated values), those text files where information is organized using a delimiter, usually comma (,) or semicolon (;).

Well, last week a need arose where I work to import a CSV file into the database, where the columns would have to be created according to the CSV file. To solve this, I created a stored procedure that reads the CSV file, identifies the columns, and dynamically creates a table to store the data coming from the CSV.

In the procedure, I also added an optional parameter to inform whether the first line of the file is the CSV header. In this case, the first row will not be displayed in the final result and the column titles are dynamically changed to the name contained in that column of the header row.

Before going to the code, this procedure uses two other functions that I had already talked about here on the blog:

• fncSplit (View post)
• fncRead_FSO_File (View post)

UPDATE 03/20/2017: If you want to use a more robust, complete and more performant solution, check out how to import data from tabular files, with CSV, using the CLR (C#) in the post SQL Server – How to export and import files with tabular data (Ex: CSV) using the CLR (C#).

Let's get to the point. Source code!!

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

How to use:

-- 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

Original file:

Table imported into the database with the above command:

That's it folks!
Did you like it?