Ty53ler Ty53ler - 2 months ago 23
ASP.NET (C#) Question

Combine/Merge Datasets From Multiple Excel Files

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.

Here is what I am currently trying...

[HttpPost]
public async Task<ActionResult> Index(ICollection<IFormFile> 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<br>";
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<br>";
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);
}


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.

Any input is appreciated.

Answer

I guess that IExcelDataReader.AsDataSet only reads the excel-rows into DataRows and adds them to a DataTable, but without defining a real primary-key, then DataSet/DataTable.Merge only appends the rows without actually merging them.

Then you could use my approach here to merge the tables:

Combining n DataTables into a Single DataTable

So the remaining code would be:

var tables = new List<DataTable>();
foreach (var file in files)
{
            // ....
            tables.Add(reader.AsDataSet().Tables[0]);
            // ...
}

DataTable mergedTables = tables.MergeAll("PimaryKeyColumnName");