SQL Server - How to export and import files with tabular data (Ex: CSV) using CLR (C #)

Views: 3.660 views

In this article

Reading Time: 7 minutes

Hello guys.
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 in “columns”, as for example, the CSV (Comma-Separated Values) file type. To assist you in this matter, 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 code written in the C # programming language and take advantage of various Microsoft .NET Framework features within SQL Server, ie you create code using the C # programming language in Visual Studio, as if you were creating an application, but the result is procedures and functions that are executed by Transact-SQL commands within SQL Server.

Want to know more about this powerful feature of SQL Server? Access the post Introduction to SQL Common Language Runtime (CLR) 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 the SQL Server Database and decided to demonstrate this solution using another technology, more practical and performative.

Prerequisites for using Procedures

As prerequisites for using the procedures below, you will need to create this .cs file, which contains the classes employee, CurrentServer e ROI.

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

}

How to export a table or query to CSV file

To facilitate the export of SQL Server data to CSV files, I will provide a Stored Procedure (written in C #) to be used in 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 or not the resulting query header is exported.

View source

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

}

Examples of use

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)

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 the “|” tab and without header, ISO-8859-1 encoding and Windows line break (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:

How to import a CSV file into the database

After demonstrating how to export database data to files, I will show you how to do the reverse path. Using the stpImporta_CSV Stored Procedure, you can import delimited files into the database in table form.

View source

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

}

};

Examples of use

Importing file data into a table, with no column indication, 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

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

Sample file:

Result:

Importing the file, returning a SELECT, skipping a few lines and heading

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)

Sample file:

Result:

That's it folks!
I hope you enjoyed this tip.

A hug!

sql server clr how to import export save import export file csv files pipe delimited files tabular data

Fabio Aragao said:

20 March 2017 to 16: 25

Hello Dirceu, your posts have enriched me a lot .. they are didactic and practical ..
Thank you

- Dirceu Resende said:
  
  22 March 2017 to 10: 47
  
  Fabio, good morning.
  Thanks for the feedback. 🙂

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #)

Prerequisites for using Procedures

How to export a table or query to CSV file

View source

Examples of use

How to import a CSV file into the database

View source

Examples of use

sql server clr how to import export save import export file csv files pipe delimited files tabular data

You may also like ...

Comments

Leave a Comment Cancel reply

Prerequisites for using Procedures

How to export a table or query to CSV file

View source

Examples of use

How to import a CSV file into the database

View source

Examples of use

sql server clr how to import export save import export file csv files pipe delimited files tabular data

You may also like ...

SQL Server - Using traceflag 3226 to avoid writing informational messages generated by backups to the SQL Server log

Is SQL Server Configuration Manager gone? Learn how to recover the shortcut

SQL Server – ISNULL x COALESCE: Knowing the Differences Between the Two Functions

Comments

Leave a Comment Cancel reply