Olá, pessoal.
Espero que esteja tudo bem com vocês.
Neste post, gostaria de demonstrar um recurso bem interessante e utilizado no dia a dia de quem cria rotinas de integrações entre sistemas utilizando arquivos com dados tabulares, ou seja, arquivos de texto que utilizam um delimitador para separar as informações em “colunas”, como por exemplo, o tipo de arquivo CSV (Comma-Separated Values). Para lhes auxiliar nesta questão, vou simplificar esse processo, mostrando como exportar e importar arquivos tabulares, incluindo arquivos CSV.
Para fazer isso, vou utilizar o recurso do CLR, que permite criar códigos escritos na linguagem de programação C# e aproveitar de vários recursos do Microsoft .NET Framework dentro do SQL Server, ou seja, você cria códigos utilizando a linguagem de programação C#, no Visual Studio, como se estivesse criando uma aplicação, mas o resultado disso, são procedures e funções que são executadas por comandos Transact-SQL dentro do SQL Server.
Quer saber mais sobre esse poderoso recurso do SQL Server? Acesse o post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.
Falando sobre esse post, eu já havia criado uma solução para importar arquivos CSV utilizando Transact-SQL e OLE Automation, no post Importando arquivos CSV para o banco de dados SQL Server e resolvi demonstrar essa solução utilizando uma outra tecnologia, mais prática e performática.
Pré-requisitos para utilizar as Procedures
Como pré-requisitos para utilizar as procedures abaixo, você precisará criar esse arquivo .cs, que contém as classes Servidor, ServidorAtual e Retorno.
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 |
using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; namespace Bibliotecas.Model { public class ServidorAtual { public string NomeServidor { get; set; } public ServidorAtual() { try { using (var conn = new SqlConnection(Servidor.Context)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT @@SERVERNAME AS InstanceName"; NomeServidor = (string)cmd.ExecuteScalar(); } var partes = NomeServidor.Split('\\'); if (partes.Length <= 1) return; if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase)) NomeServidor = partes[0]; } } catch (Exception ex) { throw ex; } } } public static class Servidor { public static string Context => "context connection=true"; public static string getLocalhost() { var servidorAtual = new ServidorAtual().NomeServidor; return "data source=" + servidorAtual + ";initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'"; } } public static class Retorno { public static void Erro(string erro) { throw new ApplicationException(erro); } public static void Mensagem(string mensagem) { using (var conexao = new SqlConnection(Servidor.Context)) { conexao.Open(); using (var comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", conexao)) { if ((int)comando.ExecuteScalar() != 0) return; } var retorno = SqlContext.Pipe; retorno?.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem); } } } } |
Como exportar uma tabela ou query para arquivo CSV
Para facilitar a exportação dos dados do SQL Server para arquivos CSV, vou disponibilizar uma Stored Procedure (escrita em C#) para ser utilizada no CLR, que permite executar uma query e exportar o resultado para arquivos texto com dados tabulares. Você pode especificar o caractere delimitador e definir se o cabeçalho resultante da query será exportado também ou não.
Visualizar código-fonte
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 |
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.IO; using System.Globalization; using System.Text; using Bibliotecas.Model; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpExporta_Query_Txt_Encoding(SqlString query, SqlString separador, SqlString caminho, SqlInt32 Fl_Coluna, SqlString Ds_Codificacao, SqlString Ds_Formato_Quebra_Linha) { var diretorio = new FileInfo(caminho.Value).DirectoryName; if (!Directory.Exists(diretorio)) { Retorno.Erro($"O diretório de destino '{diretorio}' não existe ou não está acessível."); } var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value; if (Ds_Codificacao.Value.Trim() == "") encoding = "UTF-8"; var codificacao = (Ds_Codificacao.Value.Trim().ToUpper() == "UTF-8 WBOM") ? new UTF8Encoding(false) : Encoding.GetEncoding(encoding); using (var fileStream = new FileStream(caminho.Value, FileMode.Create)) { using (var sw = new StreamWriter(fileStream, codificacao)) { switch (Ds_Formato_Quebra_Linha.Value.ToLower()) { case "unix": sw.NewLine = "\n"; break; case "mac": sw.NewLine = "\r"; break; default: sw.NewLine = "\r\n"; break; } try { using (var conn = new SqlConnection(Servidor.Context)) { conn.Open(); using (var cmd = new SqlCommand { CommandText = query.Value, CommandType = CommandType.Text, CommandTimeout = 120, Connection = conn }) { using (var dr = cmd.ExecuteReader()) { if (Fl_Coluna == 1) { for (var i = 0; i < dr.FieldCount; i++) { sw.Write(dr.GetName(i)); if (i < dr.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } if (string.IsNullOrEmpty(separador.Value)) { while (dr.Read()) { for (var i = 0; i < dr.FieldCount; i++) { sw.Write(Convert.ToString(dr.GetValue(i), CultureInfo.GetCultureInfo("pt-BR"))); if (i < dr.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } } else { var separadorTroca = new string(' ', separador.Value.Length); while (dr.Read()) { for (var i = 0; i < dr.FieldCount; i++) { sw.Write(Convert.ToString(dr.GetValue(i), CultureInfo.GetCultureInfo("pt-BR")).Replace(separador.Value, separadorTroca)); if (i < dr.FieldCount - 1) sw.Write(separador); } sw.WriteLine(); } } } } } Retorno.Mensagem("Resultado da query exportado para: " + caminho.Value); } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } } } } |
Exemplos de uso
Utilizando separador “;”, cabeçalho, codificação UTF-8 SEM BOM e quebra de linha Unix (LF)
1 2 3 4 5 6 7 |
EXEC CLR.dbo.stpExporta_Query_Txt_Encoding @query = N'SELECT name, type_desc, create_date FROM sys.tables ORDER BY name', -- nvarchar(max) @separador = N';', -- nvarchar(max) @caminho = N'C:\Teste\Arquivo.csv', -- nvarchar(max) @Fl_Coluna = 1, -- int @Ds_Codificacao = N'UTF-8 WBOM', -- nvarchar(max) @Ds_Formato_Quebra_Linha = N'UNIX' -- nvarchar(max) |
Utilizando separador “|” e sem cabeçalho, codificação ISO-8859-1 e quebra de linha Windows (CR+LF)
1 2 3 4 5 6 7 |
EXEC CLR.dbo.stpExporta_Query_Txt_Encoding @query = N'SELECT name, type_desc, create_date FROM sys.tables ORDER BY name', -- nvarchar(max) @separador = N'|', -- nvarchar(max) @caminho = N'C:\Teste\Arquivo.csv', -- nvarchar(max) @Fl_Coluna = 0, -- int @Ds_Codificacao = N'ISO-8859-1' , -- nvarchar(max) @Ds_Formato_Quebra_Linha = N'windows' -- nvarchar(max) |
Como importar um arquivo CSV para o banco de dados
Após demonstrar como exportar dados do banco de dados para arquivos, vou mostrar como fazer o caminho inverso. Utilizando a Stored Procedure stpImporta_CSV, é possível importar arquivos delimitados para o banco de dados, em forma de tabela.
Visualizar código-fonte
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 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 |
using System; using System.Data; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; using System.Text; using System.Linq; using System.Data.SqlClient; using Bibliotecas.Model; public partial class StoredProcedures { [SqlProcedure] public static void stpImporta_CSV(SqlString Ds_Caminho_Arquivo, SqlString Ds_Separador, SqlBoolean Fl_Primeira_Linha_Cabecalho, SqlInt32 Nr_Linha_Inicio, SqlInt32 Nr_Linhas_Retirar_Final, SqlString Ds_Tabela_Destino, SqlString Ds_Codificacao) { try { if (!File.Exists(Ds_Caminho_Arquivo.Value)) Retorno.Erro("Não foi possível encontrar o arquivo no caminho informado (" + Ds_Caminho_Arquivo.Value + ")"); var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value; if (Ds_Codificacao.Value.Trim() == "") encoding = "UTF-8"; var arrLinhas = File.ReadAllLines(Ds_Caminho_Arquivo.Value, Encoding.GetEncoding(encoding)); string[] cabecalho; string[] linha; var nrLinhas = arrLinhas.Length; var nrLinhaInicioLeitura = Nr_Linha_Inicio.Value; if (nrLinhaInicioLeitura <= 0) nrLinhaInicioLeitura = 1; var nrLinhasRetirarLeitura = Nr_Linhas_Retirar_Final.Value; if (nrLinhasRetirarLeitura >= nrLinhas) nrLinhasRetirarLeitura = 0; if (nrLinhaInicioLeitura > nrLinhas) Retorno.Erro($"O parâmetro @Nr_Linhas_Inicio ({nrLinhaInicioLeitura}) é maior que a quantidade total de linhas do arquivo ({nrLinhas})."); nrLinhas = nrLinhas - nrLinhasRetirarLeitura; int nrColunas; var separador = Ds_Separador.Value; var aspasNoSeparador = false; if (arrLinhas[nrLinhaInicioLeitura - 1].IndexOf("\"") >= 0) { separador = $"{Ds_Separador.Value}\""; aspasNoSeparador = true; } nrColunas = arrLinhas[nrLinhaInicioLeitura - 1].Split(new string[] { separador }, StringSplitOptions.None).Length; var rowId = 1; if (!Ds_Tabela_Destino.IsNull && Ds_Tabela_Destino.Value != "") { using (var conn = new SqlConnection(Servidor.getLocalhost())) { conn.Open(); var objectId = new SqlCommand("SELECT OBJECT_ID('" + Ds_Tabela_Destino.Value + "')", conn).ExecuteScalar().ToString(); if (!string.IsNullOrEmpty(objectId)) { Retorno.Erro("A tabela de destino '" + Ds_Tabela_Destino.Value + "' já existe! Favor apagar antes de importar o CSV"); } var queryCriacaoTabela = "CREATE TABLE " + Ds_Tabela_Destino.Value + "( RowID INT"; using (var dados = new DataTable()) { dados.Columns.Add("RowID", typeof(int)); if (Fl_Primeira_Linha_Cabecalho.Value) { cabecalho = arrLinhas[nrLinhaInicioLeitura - 1].Split(new string[] { separador }, StringSplitOptions.None); for (var i = 0; i < nrColunas; i++) { var nomeColuna = cabecalho[i].Replace("\"", ""); if (nomeColuna.Length == 0) nomeColuna = "Coluna_" + i; dados.Columns.Add(nomeColuna, typeof(string)); queryCriacaoTabela += ", [" + nomeColuna + "] VARCHAR(MAX)"; } nrLinhaInicioLeitura = nrLinhaInicioLeitura + 1; } else { for (var i = 0; i < nrColunas; i++) { dados.Columns.Add("Ds_Coluna_" + (i + 1), typeof(string)); queryCriacaoTabela += ", Ds_Coluna_" + (i + 1) + " VARCHAR(MAX)"; } } queryCriacaoTabela += " )"; for (var i = (nrLinhaInicioLeitura - 1); i < nrLinhas; i++) { linha = arrLinhas[i].Split(new string[] { separador }, StringSplitOptions.None); var arrId = new string[] { rowId.ToString() }; linha = arrId.Concat(linha).ToArray(); var linha2 = linha.Select(x => x.Replace("\"", "")).ToArray(); dados.Rows.Add(linha2); rowId++; } // Grava os dados new SqlCommand(queryCriacaoTabela, conn).ExecuteNonQuery(); using (var s = new SqlBulkCopy(conn)) { s.DestinationTableName = Ds_Tabela_Destino.Value; s.BulkCopyTimeout = 7200; s.BatchSize = 50000; s.WriteToServer(dados); } } } } else { var pipe = SqlContext.Pipe; // Cria o cabeçalho var colunas = new SqlMetaData[nrColunas + 1]; colunas[0] = new SqlMetaData("RowID", SqlDbType.Int); if (Fl_Primeira_Linha_Cabecalho) { cabecalho = arrLinhas[0].Split(new string[] { separador }, StringSplitOptions.None); for (var i = 0; i < nrColunas; i++) colunas[i + 1] = new SqlMetaData(cabecalho[i].Replace("\"", ""), SqlDbType.VarChar, 1024); nrLinhaInicioLeitura = nrLinhaInicioLeitura + 1; } else { for (var i = 0; i < nrColunas; i++) colunas[i + 1] = new SqlMetaData("Ds_Coluna_" + (i + 1), SqlDbType.VarChar, 1024); } // Recupera os registros var linhaSql = new SqlDataRecord(colunas); pipe?.SendResultsStart(linhaSql); for (var i = (nrLinhaInicioLeitura - 1); i < nrLinhas; i++) { linha = arrLinhas[i].Split(new string[] { separador }, StringSplitOptions.None); linhaSql.SetSqlInt32(0, new SqlInt32(rowId)); for (var j = 0; j < nrColunas; j++) { linhaSql.SetSqlString(j + 1, new SqlString(linha[j].Replace("\"", ""))); } pipe?.SendResultsRow(linhaSql); rowId++; } pipe?.SendResultsEnd(); } } catch (Exception e) { Retorno.Erro("Erro : " + e.Message); } } }; |
Exemplos de uso
Importando dados de arquivo para uma tabela, sem indicação de colunas, com separador “|”
1 2 3 4 5 6 7 8 9 10 11 12 |
IF (OBJECT_ID('tempdb..##Teste') IS NOT NULL) DROP TABLE ##Teste EXEC CLR.dbo.stpImporta_CSV @Ds_Caminho_Arquivo = N'C:\Teste\Arquivo.csv' , -- nvarchar(max) @Ds_Separador = N'|' , -- nvarchar(max) @Fl_Primeira_Linha_Cabecalho = 0, -- bit @Nr_Linha_Inicio = 0, -- int @Nr_Linhas_Retirar_Final = 0, -- int @Ds_Tabela_Destino = N'##Teste' , -- nvarchar(max) @Ds_Codificacao = N'ISO-8859-1' -- nvarchar(max) SELECT * FROM ##Teste |
Importando o arquivo, retornando um SELECT, pulando algumas linhas e com cabeçalho
1 2 3 4 5 6 7 8 |
EXEC CLR.dbo.stpImporta_CSV @Ds_Caminho_Arquivo = N'C:\Teste\Arquivo.csv' , -- nvarchar(max) @Ds_Separador = N';' , -- nvarchar(max) @Fl_Primeira_Linha_Cabecalho = 1, -- bit @Nr_Linha_Inicio = 4, -- int @Nr_Linhas_Retirar_Final = 3, -- int @Ds_Tabela_Destino = N'' , -- nvarchar(max) @Ds_Codificacao = N'UTF-8' -- nvarchar(max) |
É isso aí, pessoal!
Espero que tenham gostado dessa dica.
Um abraço!
sql server clr como how to import export save importar exportar arquivo arquivos csv pipe delimited files tabular data
sql server clr como how to import export save importar exportar arquivo arquivos csv pipe delimited files tabular data
Ola Dirceu, seus posts tem me enriquecido bastante.. são didáticos e práticos..
Obrigado
Fábio, bom dia.
Obrigado pelo feedback. 🙂