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.
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
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)
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)
Resultado:
Utilizando separador “|” e sem cabeçalho, codificação ISO-8859-1 e quebra de linha Windows (CR+LF)
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)
Resultado:
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
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 “|”
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
Arquivo de exemplo:
Resultado:
Importando o arquivo, retornando um SELECT, pulando algumas linhas e com cabeçalho
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)
Arquivo de exemplo:
Resultado:
É 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
Comentários (0)
Carregando comentários…