Increment Variable Name in For Each Loop

Help
1

I am trying to go through a data table and assign each row to a variable that includes the row number. So the item in row 0 would get assigned ‘item_0’ and the item in row 1 would be ‘item_1’ and so forth.This data table is dynamic though and could contain any number of rows, so it would have to account for that. Does anyone have experience with this? Thanks

1 Like
2

Use datatable.rows.count to know the number of rows in datatable.

3

Right. But how do I go through and assign row 0 to Item0 and row 1 to Item1 and so forth so that the variable name increments as I go through the data

4

Are you saving the extracted data from a datatable into an excel.

5

The opposite. I extracted some data from an excel file and put it into a datatable. I want to turn each row into a string now so I can use it in an SQL query

6

Hi
Once after getting the datatable we can use FOR EACH ROW activity and pass the variable dt as input
—Inside the loop use a ASSIGN ACTIVITY like this
dict_value(“item_”+(dt.Rows.IndexOf(row)).ToString) = row(“yourcolumnname”).ToString

Where dict_value is a variable of type System.Collections.Generic.Dictionary(Of string,string) with default value as New Dictionary(of string, string)

Or if we want all data in that row then mention like this in that assign activity
dict_value(“item_”+(dt.Rows.IndexOf(row)).ToString) = String.Join(“-“,row.ItemArray).ToString

//we can change the delimiter from - to any thing you want

Where dict_value is a variable of type System.Collections.Generic.Dictionary(Of string,string) with default value as New Dictionary(of string, string)

And may I know why we want to do like this to pass that as input to sql

Cheers @jpreziuso

7

I am trying to use this to input into a temp table in SQL. In your example, how does that assign statement work since neither of them are really variables? Could you provide a picture?

1 Like
8

Another way of explaining what I am trying to do is…can you attach a count variable to another variable name? So it the variable name would be “item”+count.

So when count is 0 the row of data is stored into the variable item0. But when count gets incremented to 1…the row of data would be stored into the variable item1. Make sense?

@Palaniyappan @Shivaraju

1 Like
9

Fine let’s go one by one

Usually we use dictionary as a key value pair
That is if we call the key if that dictionary variable we will get it’s corresponding value
Same as calling a variable which would give us its value
Makes sense right…

And coming to this

When we are forming a ASSIGN activity only VALUE part of assign activity (that is right side of equal to in assign activity) can take concatenation or any manipulation
While TO part (left side of assign activity) can’t afford such manipulation
So we won’t be able create a variable name of such kind

That’s why we are planning now to use the dictionary variable

So now we are clear on like if we call the dictionary key name we will be getting it’s corresponding value as output but still the next question will be like how this can be passed as input to SQL

same as how we created
That is pass the input as dict_value(“your keyname”).ToString which will give its value
For example

If we mention like
dict_value(“item_4”).ToString then it will give corresponding row string value and can be passed as string input to SQL query activity

Cheers @jpreziuso

1 Like
10

That makes a lot of sense. Thank you. My last concern is that when when I try to use your statement above, I get an error for trying to convert a string to a double. This is what I am attempting that gives me the error:

dict_value(“item_”+PPData.Rows.IndexOf(row))

11

Ok got it
Mention this
dict_value(“item_”+(PPData.Rows.IndexOf(row)).ToString)

Cheers @jpreziuso

12

That does not seem to have fixed the problem. But it is ok. Thank you for your help

13

Fine can I have a screenshot of that error like on which we are getting that error

Cheers @jpreziuso

14

This is the new errornew error

15

Hmm ok
Let’s for thing
Before this assign activity use one more assign activity like this
str_variablename = “item_”+(PPData.Rows.IndexOf(row)).ToString

Now in the next assign activity mention like this
dict_value(str_variablename) = row(“yourcolumnname”).ToString

Kindly try this and let know for any queries or clarification
@jpreziuso

16

This seems to be working now. Thank you for your help

1 Like
17

Cheers @jpreziuso

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.