Sqlbulkinsert- Import Excel Spreadsheet

Asked

Viewed 385 times

0

I have a dotnet application to import spreadsheet, but I don’t know why I’m not able to enter the information in the database. My code is below.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

 protected void importar_Click(object sender, EventArgs e)
{
    //Carregar e salvar o arquivo
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(selecionarArquivo.PostedFile.FileName);
    string conString = string.Empty;
    string extension = Path.GetExtension(selecionarArquivo.PostedFile.FileName);

    //Validação
    //Valida a extensão do arquivo:
    if (Path.GetExtension(selecionarArquivo.FileName) != ".xlsx" && Path.GetExtension(selecionarArquivo.FileName) != ".xls")
    {

    }
    else
    {
        ClientScript.RegisterStartupScript(typeof(string), "Erro", "<script>alert('Somente arquivos em excel')</script>");
    }

    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open(); //->>>> Aqui está dando um erro estranho a propriedade connectionstring não foi inicializada.
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[6]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: Recomenda-se, caso contrário, os dados serão considerados como String por padrão.
        dtExcelData.Columns.AddRange(new DataColumn[] { new DataColumn("Id", typeof(int)), // [] passar o valor do campo da tabela do bd

         // Aqui colocarei o campo da planilha de excel
        new DataColumn("Data de Requisição do Pgto", typeof(DateTime)),
        new DataColumn("NUMERO PROCESSO ANTERIOR",typeof(Int32)),
        new DataColumn("NUMERO JUDICIAL", typeof(Int32)),
        new DataColumn("COMARCA", typeof(string)),
        new DataColumn("VARA", typeof(string)),
        new DataColumn("NOME", typeof(string)),
        new DataColumn("CPF", typeof(Int16)),
        new DataColumn("DEENDERECO", typeof(string)),
        new DataColumn("NUENDERECO", typeof(string)),
        new DataColumn("DECOMPLEMENTO", typeof(string)),
        new DataColumn("DEBAIRRO", typeof(string)),
        new DataColumn("NUCEP", typeof(Int16)),
        new DataColumn("NMMUNICIPIO", typeof(string)),
        new DataColumn("BRUTO", typeof(decimal)),
        new DataColumn("IRPF", typeof(decimal)),
        new DataColumn("Nº SEP", typeof(Int16)),
        new DataColumn("DATA SEP", typeof(DateTime)),
        new DataColumn("DATA DE RECEBIMENTO NA ORIGEM", typeof(DateTime)),
        });

        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM sdpj_import_processo", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Define o nome da tabela
                sqlBulkCopy.DestinationTableName = "@sdpj_import_processo";
                //[OPTIONAL]: Mapeie as colunas do Excel com a da tabela do banco de dados 
                sqlBulkCopy.ColumnMappings.Add("Data de Requisição Pgto", "DAT_REQ_PGTO");
                sqlBulkCopy.ColumnMappings.Add("NUMERO PROCESSO ANTERIOR", "NUM_PROC_ANTERIOR");
                sqlBulkCopy.ColumnMappings.Add("NUMERO JUDICIAL", "NUM_PROC_JUDICIAL");
                sqlBulkCopy.ColumnMappings.Add("COMARCA", "DSC_COMARCA");
                sqlBulkCopy.ColumnMappings.Add("VARA", "NUM_VARA");
                sqlBulkCopy.ColumnMappings.Add("VARANOME", "NME_VARA");
                sqlBulkCopy.ColumnMappings.Add("NOME", "NME_INTERESSADO");
                sqlBulkCopy.ColumnMappings.Add("CPF", "CPF_CNPJ_INTERESSADO");
                sqlBulkCopy.ColumnMappings.Add("DEENDERECO", "DSC_ENDERECO");
                sqlBulkCopy.ColumnMappings.Add("NUENDERECO", "NUM_ENDERECO");
                sqlBulkCopy.ColumnMappings.Add("NMMUNICIPIO", "NME_MUNICIPIO");
                sqlBulkCopy.ColumnMappings.Add("DECOMPLEMENTO", "DSC_DECOMPLEMENTO");
                sqlBulkCopy.ColumnMappings.Add("DEBAIRRO", "NME_BAIRRO");
                sqlBulkCopy.ColumnMappings.Add("NUCEP", "NUM_CEP");
                sqlBulkCopy.ColumnMappings.Add("NMMUNICIPIO", "NME_MUNICIPIO");
                sqlBulkCopy.ColumnMappings.Add("BRUTO", "VLR_BRUTO");
                sqlBulkCopy.ColumnMappings.Add("IRPF", "VLR_IR");
                sqlBulkCopy.ColumnMappings.Add("LIQUIDO", "VLR_LIQUIDO");
                sqlBulkCopy.ColumnMappings.Add("Nº SEP", "COD_SEP");
                sqlBulkCopy.ColumnMappings.Add("DATA SEP", "DAT_SEP");
                sqlBulkCopy.ColumnMappings.Add("DATA DE RECEBIMENTO NA ORIGEM", "DAT_RECEB_ORIGEM");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}
  • Is generating some exception ?

  • Name the Message it gives is: Server Error in the/' Application. The Connectionstring property has not been initialized. Description: An untreated exception occurred during the execution of the current web request. Examine stack tracking for more information about the error and where it originated in the code. Exception Details: System.Invalidoperationexception: The Connectionstring property has not been initialized.

  • 1

    You started the variable conString empty, then tried to format it, and then tried to start a OleDbConnection with it still empty, then the error seems clear to me. Assign a valid connection string to the variable.

  • Friend, could you give me an example, I didn’t understand.

  • @Amaral, your variable constr in the first part of reading excel you do not assign any value to it and then in Bulk copy, you have checked if it is actually declared in your settings file in the key "constr"?

  • 1

    Go to <a href="https://www.connectionstrings.com/excel/">connectionstrings.com</a> and take a valid connection string, change to your need and arrow in variable consString, so by example: conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.