SQL Server - Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)

Post Views 4,103 views

Neste artigo

Reading time 7 minutes

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);
            }

        }

    }

}

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);
                }
            }
        }
    }
}

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)

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)

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:

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)

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:

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);
        }
    }
};

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 “|”

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

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:

Result:

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)

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:

Result:

And that's it, folks!
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

Fabio Aragao disse:

March 20, 2017 às 16:25

Ola Dirceu, seus posts tem me enriquecido bastante.. são didáticos e práticos..
Obrigado

Responder
- Dirceu Resende disse:
  
  March 22, 2017 às 10:47
  
  Fábio, bom dia.
  Obrigado pelo feedback. 🙂
  
  Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como exportar e importar arquivos com dados tabulares (Ex: CSV) utilizando o CLR (C#)

Pré-requisitos para utilizar as Procedures

Como exportar uma tabela ou query para arquivo CSV

Visualizar código-fonte

Exemplos de uso

Como importar um arquivo CSV para o banco de dados

Visualizar código-fonte

Exemplos de uso

sql server clr como how to import export save importar exportar arquivo arquivos csv pipe delimited files tabular data

You may also like...

2 Responses

Deixe uma respostaCancelar resposta

Pré-requisitos para utilizar as Procedures

Como exportar uma tabela ou query para arquivo CSV

Visualizar código-fonte

Exemplos de uso

Como importar um arquivo CSV para o banco de dados

Visualizar código-fonte

Exemplos de uso

sql server clr como how to import export save importar exportar arquivo arquivos csv pipe delimited files tabular data

You may also like...

Ferramentas para SQL Server que todo DBA deveria conhecer – Live no SQL Norte

SQL Server – Como renomear o servidor / instância no banco de dados quando o nome da máquina é alterado no SO

SQL Server – Como instalar os drivers Microsoft.ACE.OLEDB.12.0 e Microsoft.Jet.OLEDB.4.0

2 Responses

Deixe uma respostaCancelar resposta