title: Combine/Merge Datasets From Multiple Excel Files
body: <p>I have three excel files that all have the same primary key.  I am uploading these excel files into my application and want to merge them all in one dataset/datatable (whatever is easier).  I have tried a couple things but have been unsuccessful. </p>
  
  <p>Here is what I am currently trying...</p>
  
  <pre><code>    [HttpPost]
      public async Task&lt;ActionResult&gt; Index(ICollection&lt;IFormFile&gt; files)
      {
          var uploads = Path.Combine(_environment.WebRootPath, "uploads");
  
          DataSet ds = new DataSet();
          IExcelDataReader reader = null;
          DataTable dt = new DataTable();
  
          foreach (var file in files)
          {
  
              Dataset ds2 = null;
  
              if (file == null || file.Length == 0)
              {
                  ViewBag.Error = "Please Select An Excel File&lt;br&gt;";
                  return View("Index");
              }
              else
              {
                  using (var fileStream = new FileStream(Path.Combine(uploads, file.FileName), FileMode.OpenOrCreate, FileAccess.ReadWrite))
                  {
                      await file.CopyToAsync(fileStream);
  
                      if (file.FileName.EndsWith("xls"))
                      {
                          reader = ExcelReaderFactory.CreateBinaryReader(fileStream);                       
                      }
                      else if (file.FileName.EndsWith("xlsx"))
                      {
                          reader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
                      }
                      else
                      {
                          ViewBag.Error = "File type is incorrect&lt;br&gt;";
                          return View("Index");
                      }
  
                      reader.IsFirstRowAsColumnNames = true;                      
                      //set the second dataset as the value of the excel data
                      ds2 = reader.AsDataSet();
  
                      //merge the second dataset with the first...
                      ds.Merge(ds2);
                  }                                      
               }                
          }
  
          dt = ds.Tables[0];
  
          return View("Index", dt);
      }  
  </code></pre>
  
  <p>Do I need to specify the primary key of the datasets?  Currently, it is looping through all three of my excel spreadsheets but only outputting the first dataset as the datatable.</p>
  
  <p>Any input is appreciated.</p>