Combine/Merge Datasets From Multiple Excel Files

Question

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.

score 0 · Accepted Answer · edited May 23 '17 at 12:24

0

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");

edited May 23 '17 at 12:24

Community

1
1

answered Sep 22 '16 at 13:07

Tim Schmelter

450,073
74
686
939

This is awesome. Thank you so much. I had to mess with some datatypes of the columns but after some conversions, it is working perfect! – dev53 Sep 22 '16 at 15:04

score 0 · Answer 2 · answered Sep 22 '16 at 13:12

For DataSets to merge, you will have to specify the primary key.

ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["pri_key"] };
ds2.Tables[0].PrimaryKey = new DataColumn[] { ds2.Tables[0].Columns["pri_key"] };
ds.Merge(ds2);

Let me know if that helps.

score 0 · Answer 3 · answered Dec 23 '16 at 19:09

What about this?

Grab the contents into a dictionary:

private Dictionary<string, string> LoadFile(string path)
        {
            string line;
            Dictionary<string, string> vals = new Dictionary<string, string>();
            using (StreamReader file = new StreamReader(path))
            {
                while ((line = file.ReadLine()) != null)
                {
                    string[] parts = line.Split(',');
                    vals.Add(parts[0], parts[1]);
                }
            }
            return vals;
        }

Then in your program, Load each file and merge

Dictionary<string, string> fileAValues = LoadFile(@"C:\Temp\FileA.txt");
Dictionary<string, string> fileBValues = LoadFile(@"C:\Temp\FileB.txt");

            using (StreamWriter sr = new StreamWriter(@"C:\Temp\FileC.txt"))
            {
                foreach (string key in fileAValues.Keys)
                {
                    if (fileBValues.ContainsKey(key))
                    {
                        string combined = key + "," + 

                          String.Join(",", fileAValues[key].ToString(),
                        fileBValues[key].ToString());  
                        sr.WriteLine(combined);
                    }
                }
            }

Combine/Merge Datasets From Multiple Excel Files

3 Answers3