Hi,
I had this problem where I have a table going into a process and an updated table coming out of it. The table that went in was a spreadsheet, so I wanted to write the update back to this same sheet, to fill in the blanks as it were.
Originally, I was just clearing the original out and writing in the new data. However, the risk here is if a queue item is deleted/not created in error then the data for that row wouldn’t be included and therefore would be missing from the return rather than being included with it’s blanks.
I understand this could be a niche scenario, but I can imagine it being help for for others in related areas - for example the query in this post. I think this is much simpler than the solution given there and has the added benefit of not being reliant on a primary key.
To solve this I’ve used C#. Let’s say I’m using the tables from the post above and call these DT1 and DT2.
- Initialise a new table OutputDT by cloning DT1
- Add a for each row in datatable activity for DT1, using the item name CurrentRow1
- Inside this assign a new IEnumerable variable called EnumRow1 as CurrentRow1.ItemArray
- Still inside the for each add another for each row for DT2, using CurrentRow2. Make sure to add a variable for the index within this loop
- Repeat the assign of step 3 inside this loop but using CurrentRow2, assigning this to EnumRow2
- Add an Else If activity. The condition is EnumRow1.Intersect(EnumRow2).SequenceEqual(EnumRow1.Where(x => !string.IsNullOrWhiteSpace(Convert.ToString(x)))). This basically says “if the rows are the same except for the blanks, then true”.
- Inside the Then add an Add Data Row activity that adds the itemarray for CurrentRow2 to the OutputDT. Follow this with a break.
- Add an Else If condition Idx == DT2.Rows.Count-1, which says you’ve checked all the rows in DT2 and not found a match then true
- Inside this add an add data row activity just like in step 7, but add CurrentRow1 instead
Once this runs, OutputDT will contain all the rows from DT2 that matched in DT1, plus all the rows in DT1 (in the right place) that aren’t in DT2. Rows in DT2 but not in DT1 are ignored.
