Hello guys!
Good Morning!
Today I am very excited about this stored procedure that I will present to you, because it really gave me a little work to develop. Of course, you have heard of CSV (Comma-separated values) files, 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 CSV.
In the procedure, I also put an optional parameter to inform if 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 heading will dynamically change 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 mentioned here on the blog:
UPDATE 20 / 03 / 2017: If you want to use a more robust, complete and more performative solution, check out how to import data from tabular files with CSV using CLR (C #) in the post. SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #).
Lets go to what matters. Source code!!
|
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 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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] 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 |
Directions for use:
|
1 2 3 4 5 |
-- 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 database with the above command:
That's it folks!
Do you like it?
Missing: fncSplit
Sorry I found ...
😉