ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Import multiple Excel Files into multiple Tables in database in ASP.Net MVC<p>Hi&nbsp;Ayush,</p> <p>In this example i have read each excel in&nbsp;DataTable and return all the DataTable in DataSet.</p> <p>Then looping through the DataTable present in the DataSet inserting to database using&nbsp;SqlBulkCopy class&nbsp;WriteToServer method.</p> <p>For table maping i have assigned DestinationTableName i have used the same name in the excel and passed the excel name to the&nbsp;DestinationTableName property.</p> <p>And removed the&nbsp;ColumnMappings from the code. If you want to add&nbsp;ColumnMappings then check&nbsp;with condition.</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <pre class="brush: csharp">using System.Configuration; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO;</pre> <p><strong><span style="text-decoration: underline;">Controller</span></strong></p> <pre class="brush: csharp">public class HomeController : Controller { // GET: /Home/ public ActionResult Index() { return View(); } [HttpPost] public ActionResult Index(HttpPostedFileBase[] postedFile) { DataSet ds = GetDataTablesFromExcel(postedFile); for (int i = 0; i &lt; ds.Tables.Count; i++) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[&#34;Constring&#34;].ConnectionString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { sqlBulkCopy.DestinationTableName = ds.Tables[0].TableName; con.Open(); sqlBulkCopy.WriteToServer(ds.Tables[0]); con.Close(); } } } return View(); } private DataSet GetDataTablesFromExcel(HttpPostedFileBase[] postedFiles) { DataSet ds = new DataSet(); string path = Server.MapPath(&#34;~/Uploads/&#34;); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } for (int j = 0; j &lt; postedFiles.Length; j++) { HttpPostedFileBase postedFile = postedFiles[j]; string filePath = string.Empty; filePath = path + Path.GetFileName(postedFile.FileName); string extension = Path.GetExtension(postedFile.FileName); postedFile.SaveAs(filePath); string conString = string.Empty; switch (extension) { case &#34;.xls&#34;: //Excel 97-03. conString = ConfigurationManager.ConnectionStrings[&#34;Excel03ConString&#34;].ConnectionString; break; case &#34;.xlsx&#34;: //Excel 07 and above. conString = ConfigurationManager.ConnectionStrings[&#34;Excel07ConString&#34;].ConnectionString; break; } conString = string.Format(conString, filePath); using (OleDbConnection connExcel = new OleDbConnection(conString)) { using (OleDbCommand cmdExcel = new OleDbCommand()) { using (OleDbDataAdapter odaExcel = new OleDbDataAdapter()) { cmdExcel.Connection = connExcel; connExcel.Open(); DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); connExcel.Close(); string sheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); connExcel.Open(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + sheetName + &#34;]&#34;; odaExcel.SelectCommand = cmdExcel; DataTable dt = new DataTable(); dt.TableName = Path.GetFileNameWithoutExtension(postedFile.FileName); odaExcel.Fill(dt); connExcel.Close(); ds.Tables.Add(dt); } } } } Directory.Delete(path, true); return ds; } }</pre> <p><strong><span style="text-decoration: underline;">View</span></strong></p> <pre class="brush: html">&lt;% using (Html.BeginForm(&#34;Index&#34;, &#34;Home&#34;, FormMethod.Post, new { enctype = &#34;multipart/form-data&#34; })) {%&gt; &lt;input type=&#34;file&#34; name=&#34;postedFile&#34; multiple required /&gt; &lt;input type=&#34;submit&#34; value=&#34;Import&#34; /&gt; &lt;% } %&gt;</pre> <p>&nbsp;</p>, 09 Jul 2019 02:21:51 GMT