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.Import multiple Excel Files into SQL Server in ASP.Net MVC<p>Hi&nbsp;Ayush,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">Database</span></strong></p> <p>I have made use of the following table Customers with the schema as follows.</p> <p><img src="https://www.aspsnippets.com/Handlers/DownloadFile.ashx?File=f18ac914-bc9b-437a-88e2-bd640ce05282.png" alt="" width="384" height="137" /></p> <p>You can download the database table SQL by clicking the download link below.</p> <p><a href="https://www.aspsnippets.com/DownloadFile.aspx?File=Customers_Table_AutoIncrement.sql">Download SQL file</a></p> <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(new DataTable()); } [HttpPost] public ActionResult Index(HttpPostedFileBase[] postedFile) { DataTable dt = GetDataTableFromExcel(postedFile); using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[&#34;Constring&#34;].ConnectionString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { sqlBulkCopy.DestinationTableName = &#34;dbo.Customers&#34;; sqlBulkCopy.ColumnMappings.Add(&#34;Name&#34;, &#34;Name&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Country&#34;, &#34;Country&#34;); con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } return View(dt); } private DataTable GetDataTableFromExcel(HttpPostedFileBase[] postedFiles) { DataTable dt = new DataTable(); for (int j = 0; j &lt; postedFiles.Length; j++) { HttpPostedFileBase postedFile = postedFiles[j]; string filePath = string.Empty; string path = Server.MapPath(&#34;~/Uploads/&#34;); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } 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; odaExcel.Fill(dt); connExcel.Close(); } } } Directory.Delete(path, true); } return dt; } }</pre> <p><strong><span style="text-decoration: underline;">View</span></strong></p> <pre class="brush: html">&lt;%@ Page Language=&#34;C#&#34; Inherits=&#34;System.Web.Mvc.ViewPage&lt;DataTable&gt;&#34; %&gt; &lt;%@ Import Namespace=&#34;System.Data&#34; %&gt; &lt;!DOCTYPE html PUBLIC &#34;-//W3C//DTD XHTML 1.0 Transitional//EN&#34; &#34;http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd&#34;&gt; &lt;html xmlns=&#34;http://www.w3.org/1999/xhtml&#34;&gt; &lt;head runat=&#34;server&#34;&gt; &lt;title&gt;Index&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;div&gt; &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;hr /&gt; &lt;table&gt; &lt;tr&gt; &lt;% foreach (DataColumn column in Model.Columns) {%&gt; &lt;th&gt;&lt;%=column.ColumnName%&gt;&lt;/th&gt; &lt;% }%&gt; &lt;/tr&gt; &lt;% foreach (DataRow row in Model.Rows) {%&gt; &lt;tr&gt; &lt;%foreach (DataColumn column in Model.Columns) {%&gt; &lt;td&gt;&lt;%=row[column.ColumnName]%&gt;&lt;/td&gt; &lt;%} %&gt; &lt;/tr&gt; &lt;% }%&gt; &lt;/table&gt; &lt;% } %&gt; &lt;/div&gt; &lt;/body&gt; &lt;/html&gt; </pre> <p><strong><span style="text-decoration: underline;">Screenshots</span></strong></p> <p><span style="text-decoration: underline;">The Excel files</span></p> <p><span style="text-decoration: underline;"><img src="https://i.imgur.com/mc9CsWR.jpg" alt="" width="320" height="357" /></span></p> <p><span style="text-decoration: underline;">The Form</span></p> <p><span style="text-decoration: underline;"><img src="https://i.imgur.com/n2ibDO8.gif" alt="" width="338" height="273" /></span></p> <p><span style="text-decoration: underline;">Database after Insert</span></p> <p><span style="text-decoration: underline;"><img src="https://i.imgur.com/WdCo8cI.jpg" alt="" width="300" height="148" /></span></p>https://www.aspforums.net:443/Threads/122834/Import-multiple-Excel-Files-into-SQL-Server-in-ASPNet-MVC/https://www.aspforums.net:443/Threads/122834/Import-multiple-Excel-Files-into-SQL-Server-in-ASPNet-MVC/Mon, 08 Jul 2019 00:56:03 GMT