How to Add Data to Excel Row with Existing Data?

Help
1

I cannot figure out how to solve this and I think it may be because I am making this more complicated than it needs to be… But I need some help.

I am trying to combine two data tables. I have one spreadsheet that I am reading and replacing some values by getting those from another spreadsheet. I have both in Excel sheets read to data tables, but cannot figure out how to replace the values in each Data Row with the values I’m hoping for.

For example, lets say one data table is called dtColors and the other is dtAnimals. I am hoping to replace the first and third values of each row in dtAnimals with values from each row of dtColors.

For example:
dtColors could be: [row 1]: Red, Orange, Yellow, Green
[row 2]: Blue, Indigo, Violet, Black
dtAnimals could be: [row 1]: Lion, Tiger, Bear, Frog
[row 2]: Cow, Horse, Goat, Pig

What I am hoping to get would look like this: [row 1] Red, Tiger, Yellow, Frog
[row 2] Blue, Horse, Violet, Pig

This is a much simpler version of what I am trying to do but I hope that providing an example would be helpful to someone that might be able to help me…

Thanks for your time.

1 Like
2

Use the below logic.

Counter = 1

For each row in dt1
{
Assign row (1) = dt2.rows(Counter).item(1)
Assign row (3) = dt2.rows(Counter).item(3)
Counter=Counter+1
}

1 Like
3

Thank you for the idea - I can see how this could work. I knew it was simpler than I was making it.

However, I am getting an error when trying to assign the row a new value. The error is: Exception Occurred during Process Exception has been thrown by the target of an invocation.

Do you have any suggestion?

1 Like
4

You might try adding .ToString to the end.

dt2.rows(Counter).item(1).ToString

Without, you are assigned an object to the item in the row, so I wonder if it needs to be a string.

1 Like
5

I just tried your suggestion… Unfortunately that didn’t solve the error.

6

Can you upload or show a snippet of your code in your workflow and the entire exception message (also which activity it stops at). Maybe then I can see the problem.

Thanks.

1 Like
7

Sure! The full error is this: Exception Occured during Process Exception has been thrown by the target of an invocation. Source Invoke BuildPartialReport_HRCompRpt workflow: Invoke CombinedToCompComCensus workflow: Assign - Performance Rating

Here is a snippet of the code:

image
image418×726 35 KB

Details for The activity Assign - Performance Rating are
image
There are two arguments, one called in_DTFormattedCompComCensus and one called io_DTCompComCensusTemplate.
image

I will need to do this for many columns, but right now I am trying to replace the column “Performance Review Rating” in io_DTCompComCensusTemplate with the value of “Performance Review Rating” from in_DTFormattedCompComCensus.

8

Hi kappky,
I’m assuming in_DTFormattedCombined is a Data Table, or is that an Array Of DataRows?
If it is a Data Table, you would want to use .Rows()

in_DTFormattedCombined.Rows(counter)("Performance Review Rating").ToString

But, if it’s an Array, then you are good there.

The second thing I would check is if in_DTFormattedCombined Argument is set as an IN argument in that workflow. Also, verify it has data and that the column exists; you can do this in a Message Box or Write Line by using Output Data Table or hardcoding some values.

Hope that provides some ideas.

If I think of anything else, I’ll let you know.

Regards.

1 Like
9

Also, since you are using the For each row activity, then you might need to use .Item() next to row

row.Item("Performance Review Rating")

It probably doesn’t matter though.

2 Likes