title: Error "External table is not in the expected format" while importing excel data into db in mvc
body: <p>I am getting an error</p>
  <blockquote>
  <p>External table is not in the expected format</p>
  </blockquote>
  <p>while importing excel data into db in mvc.</p>
  <h2>Table from Entity Framework</h2>
  <pre><code>namespace MyCRS
  {
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Web;
      
      public partial class tblStoreRequest
      {
          public int TransactionId { get; set; }
          public string DocumentNo { get; set; }
          public System.DateTime RequestedDate { get; set; }
          public int StoreId { get; set; }
          public int ArticleId { get; set; }
          public int RequestedQty { get; set; }
          public string Remarks { get; set; }
          public string EnteredBy { get; set; }
          public System.DateTime EnteredOn { get; set; }
          public string UpdatedBy { get; set; }
          public Nullable&lt;System.DateTime&gt; UpdatedOn { get; set; }
      
          public virtual AspNetUser AspNetUser { get; set; }
          public virtual AspNetUser AspNetUser1 { get; set; }
          public virtual tblArticle tblArticle { get; set; }
          public virtual tblStore tblStore { get; set; }
      }
  }
  </code></pre>
  <h2>Request.xlsx</h2>
  <p>(data format in text as in am getting into <code>List&lt;string&gt;</code>)</p>
  <pre><code>## DocumentNo   RequestedDate   StoreId ArticleId   RequestedQty Remarks ##
  ##  REQ-5       02-09-2016      4       4           100 ##
          
  </code></pre>
  <h2>Controller Code</h2>
  <pre><code>using System.Collections.Generic;
  using System.Data;
  using System.Data.Entity;
  using System.Data.Entity.Validation;
  using System.Data.OleDb;
  using System.IO;
  using System.Linq;
  using System.Net;
  using System.Text.RegularExpressions;
  using System.Web;
  using System.Web.Mvc;
  using MyCRS.Models;
  using LinqToExcel;
  using System.Data.SqlClient;
  
  namespace MyCRS.Contollers
  {
      public class StoreRequestBulkController : Controller
      {
          MyCRSEntities db = new MyCRSEntities();
              
          public ActionResult Index()
          {
              return View();
          }
              
          [HttpPost]
          public JsonResult UploadExcel(tblStoreRequest storerequests, HttpPostedFileBase FileUpload)
          {
              List&lt;string&gt; data = new List&lt;string&gt;();
  
              if (FileUpload != null)
              {
                  if (FileUpload.ContentType == &quot;application/vnd.ms-excel&quot;
                      || FileUpload.ContentType == &quot;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet&quot;)
                  {
                      string filename = FileUpload.FileName;
                      string targetpath = Server.MapPath(&quot;~/Doc/&quot;);
                      FileUpload.SaveAs(targetpath + filename);
                      string pathToExcelFile = targetpath + filename;
                      var connectionString = &quot;&quot;;
  
                      if (filename.EndsWith(&quot;.xls&quot;))
                      {
                          connectionString = string.Format(&quot;Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;&quot;, pathToExcelFile);
                      }
                      else if (filename.EndsWith(&quot;.xlsx&quot;))
                      {
                          connectionString = string.Format(&quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\&quot;Excel 14.0;HDR=YES;IMEX=1\&quot;;&quot;, pathToExcelFile);
                      }
  
                      var adapter = new OleDbDataAdapter(&quot;SELECT * FROM [Sheet1$]&quot;, connectionString);
                      var ds = new DataSet();
  
                      adapter.Fill(ds, &quot;ExcelTable&quot;);
  
                      DataTable dtable = ds.Tables[&quot;ExcelTable&quot;];
  
                      string sheetName = &quot;Sheet1&quot;;
                      var excelFile = new ExcelQueryFactory(pathToExcelFile);
                      var artistAlbums = from a in 
                                         excelFile.Worksheet&lt;tblStoreRequest&gt;(sheetName) select a;
  
                      foreach (var a in artistAlbums)
                      {
                          try
                          {
                              if (a.DocumentNo != null &amp;&amp; a.RequestedDate != null)
                              {
                                  tblStoreRequest TU = new tblStoreRequest();
                                  TU.DocumentNo = a.DocumentNo;
                                  TU.RequestedDate = a.RequestedDate;
                                  TU.StoreId = a.StoreId;
                                  TU.ArticleId = a.ArticleId;
                                  TU.RequestedQty = a.RequestedQty;
                                  TU.Remarks = a.Remarks;
                                  TU.EnteredBy = Session[&quot;U&quot;].ToString();
                                  TU.EnteredOn = DateTime.Now;
                                  db.tblStoreRequests.Add(TU);
  
                                  db.SaveChanges();
                              }
                              else
                              {
                                  data.Add(&quot;&lt;ul&gt;&quot;);
                                  if (a.DocumentNo == &quot;&quot; || a.DocumentNo == null) data.Add(&quot;&lt;li&gt; DocumentNo is required&lt;/li&gt;&quot;);
                                  if (a.RequestedDate.ToString() == &quot;&quot; || a.RequestedDate == null) data.Add(&quot;&lt;li&gt; RequestedDate is required&lt;/li&gt;&quot;);
                                  if (a.RequestedQty.ToString() == &quot;&quot;) data.Add(&quot;&lt;li&gt; RequestedQty is required&lt;/li&gt;&quot;);
  
                                  data.Add(&quot;&lt;/ul&gt;&quot;);
                                  data.ToArray();
                                  return Json(data, JsonRequestBehavior.AllowGet);
                              }
                          }
                          catch (DbEntityValidationException ex)
                          {
                              foreach (var entityValidationErrors in ex.EntityValidationErrors)
                              {
                                  foreach (var validationError in entityValidationErrors.ValidationErrors)
                                  {
                                      Response.Write(&quot;Property: &quot; + validationError.PropertyName + &quot; Error: &quot; + validationError.ErrorMessage);
                                  }
                              }
                          }
                      }
                      //deleting excel file from folder  
                      if ((System.IO.File.Exists(pathToExcelFile)))
                      {
                          System.IO.File.Delete(pathToExcelFile);
                      }
                      return Json(&quot;success&quot;, JsonRequestBehavior.AllowGet);
                  }
                  else
                  {
                      //alert message for invalid file format  
                      data.Add(&quot;&lt;ul&gt;&quot;);
                      data.Add(&quot;&lt;li&gt;Only Excel file format is allowed&lt;/li&gt;&quot;);
                      data.Add(&quot;&lt;/ul&gt;&quot;);
                      data.ToArray();
                      return Json(data, JsonRequestBehavior.AllowGet);
                  }
              }
              else
              {
                  data.Add(&quot;&lt;ul&gt;&quot;);
                  if (FileUpload == null) data.Add(&quot;&lt;li&gt;Please choose Excel file&lt;/li&gt;&quot;);
                  data.Add(&quot;&lt;/ul&gt;&quot;);
                  data.ToArray();
                  return Json(data, JsonRequestBehavior.AllowGet);
              }
          }
      }
  }
  </code></pre>
  <h2>View</h2>
  <pre><code>@{
      ViewBag.Title = &quot;Index&quot;;
  }
  
  &lt;h4&gt;Add Users via Excel&lt;/h4&gt;
  &lt;hr /&gt;
  @using (Html.BeginForm(&quot;UploadExcel&quot;, &quot;StoreRequestBulk&quot;, FormMethod.Post, 
  new { enctype = &quot;multipart/form-data&quot;, onsubmit = &quot;return myFunction()&quot; }))
  {
      &lt;div class=&quot;form-horizontal&quot;&gt;
          &lt;div class=&quot;form-group&quot;&gt;
              &lt;div class=&quot;control-label col-md-2&quot;&gt;Download Format:&lt;/div&gt;
              &lt;div class=&quot;col-md-10&quot;&gt;
                  &lt;a href=&quot;/StoreRequestBulk/DownloadExcel/&quot;&gt;&lt;img 
  src=&quot;~/excel.ico&quot; width=&quot;25&quot; height=&quot;25&quot; title=&quot;Download Excel format&quot; 
  alt=&quot;excel&quot; /&gt;&lt;/a&gt;
              &lt;/div&gt;
          &lt;/div&gt;
  
          &lt;div class=&quot;form-group&quot;&gt;
              &lt;div class=&quot;control-label col-md-2&quot;&gt;Excel:&lt;/div&gt;
              &lt;div class=&quot;col-md-10&quot;&gt;
                  &lt;input type=&quot;file&quot; id=&quot;FileUpload&quot; name=&quot;FileUpload&quot; 
  class=&quot;&quot; /&gt;
              &lt;/div&gt;
          &lt;/div&gt;
          &lt;div class=&quot;form-group&quot;&gt;
              &lt;div class=&quot;col-md-offset-2 col-md-10&quot;&gt;
                  &lt;input type=&quot;submit&quot; value=&quot;Upload&quot; id=&quot;btnSubmit&quot; 
  class=&quot;btn     btn-primary&quot; /&gt;
  
              &lt;/div&gt;
          &lt;/div&gt;
      &lt;/div&gt;
  }
  </code></pre>