ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Read excel sheet and Bulk Insert records and Update existing rows if record exists using C# and VB.Net <p>Hi <a class="username" rel="smuthu"> smuthu</a>,</p> <p>I have created sample refering the below article. Refer the below code.</p> <h1 class="header"><a title="SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net'a" href="http://www.aspsnippets.com/Articles/SqlBulkCopy--Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet.aspx">SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net</a></h1> <p><span style="text-decoration: underline;">HTML</span></p> <pre class="brush: html">&lt;div&gt; &lt;asp:FileUpload ID=&#34;FileUpload1&#34; runat=&#34;server&#34; /&gt; &lt;br /&gt; &lt;asp:Button ID=&#34;Button1&#34; Text=&#34;Bulk Update&#34; OnClick=&#34;Bulk_Update&#34; runat=&#34;server&#34; /&gt; &lt;/div&gt;</pre> <p><span style="text-decoration: underline;">Code</span></p> <pre class="brush: csharp">protected void Bulk_Update(object sender, EventArgs e) { if (FileUpload1.HasFile) { string excelPath = Server.MapPath(&#34;~/Files/&#34;) + Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(excelPath); string conString = string.Empty; string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (extension) { case &#34;.xls&#34;: //Excel 97-03 conString = ConfigurationManager.ConnectionStrings[&#34;Excel03ConString&#34;].ConnectionString; break; case &#34;.xlsx&#34;: //Excel 07 or higher conString = ConfigurationManager.ConnectionStrings[&#34;Excel07+ConString&#34;].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][&#34;TABLE_NAME&#34;].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(&#34;Id&#34;), new DataColumn(&#34;Name&#34;), new DataColumn(&#34;Country&#34;) }); using (OleDbDataAdapter oda = new OleDbDataAdapter(&#34;SELECT * FROM [&#34; + sheet1 + &#34;]&#34;, 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(&#34;Id&#34;, typeof(int)), new DataColumn(&#34;Name&#34;, typeof(string)), new DataColumn(&#34;Country&#34;, typeof(string)) }); for (int i = 0; i &lt; dtExcelData.Rows.Count; i++) { dt.Rows.Add(Convert.ToInt16(dtExcelData.Rows[i][&#34;Id&#34;].ToString().Trim()), dtExcelData.Rows[i][&#34;Name&#34;].ToString().Trim(), dtExcelData.Rows[i][&#34;Country&#34;].ToString().Trim()); } string constr = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand(&#34;Update_Customers&#34;)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = con; cmd.Parameters.AddWithValue(&#34;@tblCustomers&#34;, dt); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } } } }</pre> <p><span style="text-decoration: underline;">Screenshot</span></p> <p><strong><span style="text-decoration: underline;">ExcelData</span></strong></p> <table width="273"> <tbody> <tr> <td width="62"><strong>Id</strong></td> <td width="121"><strong>Name</strong></td> <td width="90"><strong>Country</strong></td> </tr> <tr> <td>1</td> <td>John Hammond</td> <td>United States</td> </tr> <tr> <td>2</td> <td>Mudassar Khan</td> <td>USA</td> </tr> <tr> <td>3</td> <td>Suzanne Mathews</td> <td>France</td> </tr> <tr> <td>4</td> <td>Robert Schidner</td> <td>Russia</td> </tr> <tr> <td>5</td> <td>Muthu</td> <td>INDIA</td> </tr> </tbody> </table> <p><strong><span style="text-decoration: underline;">DataBase Record before Insert / Update</span></strong></p> <p><img src="https://i.imgur.com/S9Jthuh.jpg" alt="" width="277" height="159" /></p> <p><strong><span style="text-decoration: underline;">DataBase Record after Insert / Update</span></strong></p> <p><img src="https://i.imgur.com/En6Q6Qf.jpg" alt="" width="304" height="182" /></p>https://www.aspforums.net:443/Threads/303241/Read-excel-sheet-and-Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet/https://www.aspforums.net:443/Threads/303241/Read-excel-sheet-and-Bulk-Insert-records-and-Update-existing-rows-if-record-exists-using-C-and-VBNet/Wed, 19 Oct 2016 07:17:54 GMT