Read excel sheet and Bulk Insert records and Update existing rows if record exists using C# and VB.Net

Last Reply on Oct 19, 2016 07:17 AM By dharmendr

Posted on Oct 18, 2016 06:28 AM

EXCEL:

1.Tran Date

2.Value Date

3.CHQNO

4.Transaction Particulars

5.Amount

6.DR/CR

7.Branch Name

SQL:

EXCEL:

1.Tran Date

2.Value Date

3.CHQNO

4.Transaction Particulars

5.Amount

6.DR/CR

7.Branch Name

8.Bank Name

DESIGN:

<table>
    <tr class="pageText">
        <td style="text-align: right">
            <span style="color: red;">*</span>Category:
        </td>
        <td>
            <asp:DropDownList ID="ddlCategory" runat="server">
                <asp:ListItem>AXIS</asp:ListItem>
                <asp:ListItem>INDUS</asp:ListItem>
                <asp:ListItem>KVB</asp:ListItem>
                <asp:ListItem></asp:ListItem>
            </asp:DropDownList>
            <asp:RequiredFieldValidator InitialValue="0" ID="RequiredFieldValidator1" runat="server"
                ControlToValidate="ddlCategory" Display="None" ErrorMessage="Please Select Category"></asp:RequiredFieldValidator>
        </td>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </tr>
    <tr class="pageText">
        <td style="text-align: right">
            <span style="color: red;">*</span>Upload:
        </td>
        <td>
            <asp:FileUpload ID="FileUpload1" runat="server" onchange="ShowImagePreview(this);" />
        </td>
    </tr>
    <tr class="pageText">
        <td style="text-align: right">
        </td>
        <td>
            <asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Submit_Click1" />
        </td>
    </tr>
</table>

SOURCE CODE:

protected void Page_Load(object sender, EventArgs e)
{
    Label1.Visible = false;
}

protected void Submit_Click1(object sender, EventArgs e)
{
    //Upload and save the file
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);

    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dtExcelData = new DataTable();

        //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
        dtExcelData.Columns.AddRange(new DataColumn[8] {
            new DataColumn("Tran Date", typeof(DateTime)),
            new DataColumn("Value Date", typeof(DateTime)),
            new DataColumn("CHQNO",typeof(string)),
            new DataColumn("Transaction Particulars",typeof(string)),
            new DataColumn("Amount(in Rs.)",typeof(decimal)),
            new DataColumn("DR|CR",typeof(string)),
            new DataColumn("Branch Name",typeof(string)),

            new DataColumn("Bank Name",typeof(string)),

            });
        dtExcelData.Columns["Bank Name"].DefaultValue = ddlCategory.Text.Trim();
        dtExcelData.Columns["Date"].DefaultValue = DateTime.Today.ToShortDateString();
        dtExcelData.Columns["Status"].DefaultValue = 1;
        dtExcelData.Columns["BStatus"].DefaultValue = 1;
        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            SqlCommand cmd = new SqlCommand("");
            //oda.Fill(dtExcelData);
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Bank Name", "BankName");
                sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
                sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
                sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
                sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
                sqlBulkCopy.ColumnMappings.Add("Amount(in Rs.)", "Amount");
                sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
                sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}

ACTUAL RESULT:

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) NULL

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) NULL

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) NULL

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) NULL

NULL            NULL         NULL  NULL  NULL NULL     NULL     AXIS

EXCEPTING RESULT

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) AXIS

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) AXIS

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) AXIS

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) AXIS

14.10.2016 14.10.2016 123456 NEFT  2500 DR AXIS(KERALA) AXIS

 

Posted on Oct 19, 2016 07:17 AM Modified on one year ago

Hi smuthu,

I have created sample refering the below article. Refer the below code.

SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net

HTML

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <br />
    <asp:Button ID="Button1" Text="Bulk Update" OnClick="Bulk_Update" runat="server" />
</div>

Code

protected void Bulk_Update(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls":
                //Excel 97-03 
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx":
                //Excel 07 or higher 
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default. 
            dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id"), new DataColumn("Name"), new DataColumn("Country") });
            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            // Adding data to table by removing spaces. 
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Country", typeof(string)) });
            for (int i = 0; i < dtExcelData.Rows.Count; i++)
            {
                dt.Rows.Add(Convert.ToInt16(dtExcelData.Rows[i]["Id"].ToString().Trim()), dtExcelData.Rows[i]["Name"].ToString().Trim(), dtExcelData.Rows[i]["Country"].ToString().Trim());
            }

            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("Update_Customers"))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@tblCustomers", dt);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

Screenshot

ExcelData

Id Name Country
1 John Hammond United States
2 Mudassar Khan USA
3 Suzanne Mathews France
4 Robert Schidner Russia
5 Muthu INDIA

DataBase Record before Insert / Update

DataBase Record after Insert / Update