Hola, chicos.
Espero que todo esté bien contigo.
En este post me gustaría demostrar un recurso muy interesante utilizado en el día a día de quienes crean rutinas de integración entre sistemas utilizando archivos con datos tabulares, es decir, archivos de texto que utilizan un delimitador para separar la información en “columnas”, como los archivos de tipo CSV (valores separados por comas). Para ayudarle con este problema, simplificaré este proceso mostrándole cómo exportar e importar archivos tabulares, incluidos archivos CSV.
Para hacer esto, usaré la característica CLR, que te permite crear códigos escritos en el lenguaje de programación C# y aprovechar varias características de Microsoft .NET Framework dentro de SQL Server, es decir, creas códigos usando el lenguaje de programación C#, en Visual Studio, como si estuvieras creando una aplicación, pero el resultado de esto son procedimientos y funciones que se ejecutan mediante comandos Transact-SQL dentro de SQL Server.
¿Quiere obtener más información sobre esta poderosa característica de SQL Server? Accede a la publicación Introducción a SQL CLR (Common Language Runtime) en SQL Server.
Hablando de esta publicación, ya había creado una solución para importar archivos CSV usando Transact-SQL y OLE Automation, en la publicación Importación de archivos CSV a la base de datos de SQL Server y decidí demostrar esta solución utilizando otra tecnología, más práctica y eficaz.
Requisitos previos para utilizar procedimientos
Como requisito previo para utilizar los procedimientos siguientes, deberá crear este archivo .cs, que contiene las clases Servidor, Servidor actual y Devolver.
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);
}
}
}
}
Cómo exportar una tabla o consulta a un archivo CSV
Para facilitar la exportación de datos de SQL Server a archivos CSV, proporcionaré un procedimiento almacenado (escrito en C#) para usar en CLR, que le permite ejecutar una consulta y exportar el resultado a archivos de texto con datos tabulares. Puede especificar el carácter delimitador y definir si el encabezado resultante de la consulta también se exportará o no.
Ver código fuente
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);
}
}
}
}
}Ejemplos de uso
Usando ";" separador, encabezado, codificación UTF-8 SIN BOM y salto de línea 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:
Usando “|” separador y sin encabezado, codificación ISO-8859-1 y ajuste de línea de 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:
Cómo importar un archivo CSV a la base de datos
Después de demostrar cómo exportar datos de la base de datos a archivos, le mostraré cómo hacer lo contrario. Utilizando el procedimiento almacenado stpImporta_CSV, es posible importar archivos delimitados a la base de datos, en forma de tabla.
Ver código fuente
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);
}
}
};Ejemplos de uso
Importar datos de un archivo a una tabla, sin indicar columnas, con un “|” 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
Archivo de ejemplo:
Resultado:
Importando el archivo, devolviendo un SELECT, omitiendo algunas líneas y con un encabezado
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)
Archivo de ejemplo:
Resultado:
¡Eso es todo, amigos!
Espero que te haya gustado este consejo.
¡Un abrazo!
sql server clr cómo importar exportar guardar importar exportar archivo archivos csv archivos delimitados por tuberías datos tabulares
sql server clr cómo importar exportar guardar importar exportar archivo archivos csv archivos delimitados por tuberías datos tabulares
Comentários (0)
Carregando comentários…