Hey guys.
I hope all is well with you.
In this post, I would like to demonstrate a very interesting resource used in the daily lives of those who create integration routines between systems using files with tabular data, that is, text files that use a delimiter to separate the information into “columns”, such as the CSV (Comma-Separated Values) file type. To help you with this issue, I will simplify this process by showing you how to export and import tabular files, including CSV files.
To do this, I will use the CLR feature, which allows you to create codes written in the C# programming language and take advantage of several features of the Microsoft .NET Framework within SQL Server, that is, you create codes using the C# programming language, in Visual Studio, as if you were creating an application, but the result of this are procedures and functions that are executed by Transact-SQL commands within SQL Server.
Want to learn more about this powerful SQL Server feature? Access the post Introduction to SQL CLR (Common Language Runtime) in SQL Server.
Talking about this post, I had already created a solution to import CSV files using Transact-SQL and OLE Automation, in the post Importing CSV files into SQL Server database and I decided to demonstrate this solution using another technology, more practical and performant.
Prerequisites for using Procedures
As prerequisites for using the procedures below, you will need to create this .cs file, which contains the classes Server, CurrentServer and Return.
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);
}
}
}
}
How to export a table or query to CSV file
To facilitate the export of data from SQL Server to CSV files, I will provide a Stored Procedure (written in C#) to be used in the CLR, which allows you to execute a query and export the result to text files with tabular data. You can specify the delimiter character and define whether the header resulting from the query will also be exported or not.
View source code
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);
}
}
}
}
}Usage examples
Using “;” separator, header, UTF-8 encoding WITHOUT BOM and Unix line break (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)
Result:
Using “|” separator and headerless, ISO-8859-1 encoding and Windows line wrapping (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)
Result:
How to import a CSV file into the database
After demonstrating how to export data from the database to files, I will show you how to do the opposite. Using the Stored Procedure stpImporta_CSV, it is possible to import delimited files into the database, in table form.
View source code
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);
}
}
};Usage examples
Importing file data into a table, without indicating columns, with a “|” separator
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
Example file:
Result:
Importing the file, returning a SELECT, skipping some lines and with a header
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)
Example file:
Result:
That's it, folks!
I hope you liked this tip.
A hug!
sql server clr how to import export save import export file csv files pipe delimited files tabular data
sql server clr how to import export save import export file csv files pipe delimited files tabular data
Comentários (0)
Carregando comentários…