Hi guys, I’m working on a small project which has a file structure like this:
Based on the headers in the result file, I have to check if the column exists in the master file and if it does, for each category, I have to calculate the total and write it to the result file.
What is the best way to do this?
I tried to use a Build Data Table activity to create the output structure (dynamically using loops) and then tried to use an Add Data Row activity but, I’m not getting the proper output.
Consider the result file screenshot above. The highlighted column F is a newly added column which might exist in the master file and if it does, we have to calculate the total for each distinct categories and if it doesn’t exist, we simply mark it as 0.
I’ve done everything but, I’m not sure how to use an Add Data Row activity dynamically.
@monsieurrahul
My question was in result we do see E, but not in Master (as we do see X). What is the expected behaviour? Please tell us the expectations.
Good to know from your feedback was as E is missing in Master we will write 0.
I’ll tell you the steps the bot is going to perform:
Read the result file and form a table with headers in it.
For each distinct category in master file, filter the data and perform the following:
2.1 Check if the columns in the result table exists in the filtered table.
2.2 If it exists, calculate the total and if not, update as 0 (Since E doesn’t exist in master).
2.3 Update the row to the result DT since it has headers.
2.4 Repeat until all the categories have been completed.
Where I’m stuck: Step 2.3. I don’t know how to use Add Data Row since we can’t predict the order in which columns exist.
@monsieurrahul
with dtResult.NewRow (to var tmpRow you will get an empty row for result
With dtResult.Columns you can iterate over the columns and can get the name with the ColumName Property
within the iteration you can check if Column is dtMaster (and also ommit the Category Column as there is nothing to sum up).
Summing up can be done with datasetextension Method Sum
and the result can be assigned to tmpRow(YourCurrentColumnName)
after all you add tmpRow to dtResult.
In case of you need more help, just share a xaml with in which have prepared two datatable with sample data. I could help you on finalizing with this
@ppr I’m getting an error in adding the rows and I can’t share the workflow since it is official so, can you please consider sharing a .xaml file for the problem?
Here’s a sample Excel file for your reference: Test.xlsx (8.9 KB)
I actually solved the problem on my own. I used an invoke code activity and wrote C# code.
I used a column counter to iterate through every column, used the column name property to get the column name and then used a if condition to check if it exists in my master data table. If it does, I’ll add it up and used the extension method to pass it to the result data table. I also used a row counter to add data to the same data table and not generate a new one, every time.
