Assign multiple row value from excel to single variable

I have a excel table with 10 rows, now I want to assign all the row values to single string variable with limit of 5 value at a time in a loop, how can i achieve this?

image

Output string variable should be ID=1,2,3,4,5 and in next loop it should be ID=6,7,8,9,10

Hey @anto.santhosh!! What a great question!! Lets go to the solution!!

→ First ill introduce the variables.

  • dt_test is the data table that we will store the reading of the data in the excel files, that is, the IDs.
  • arr_test will be the array that will contain this column of IDs, that’s because we’re going to transform the column into a array, so we can handle it better.
  • parts is the division quotient, because since you want to divide a number N into equal parts of 5, let’s get how many parts are needed.
  • control is the variable that will indicate the beginning of the array slice. For example, if we want to take elements from position 10 to 15, our control will assume the value of 10 in our workflow, so we can perform the correct extraction.
  • arr_temp will be the array that will temporarily contain the slice of the main array (arr_test).

Let’s go step by step!

→ Step 1: Read excel data and store in dt_test.

→ Step 2: Transform the column of IDs into a vector and store in the arr_test variable.

dt_test.AsEnumerable().Select(Function (a) a("ID").ToString).ToArray()

→ Step 3: Let’s calculate how many equal parts we will divide the array and store in the variable parts.

PAY VERY ATTENTION HERE!!! I defined this variable as String because in cases of division by 5 not being exact, depending on the number of IDs, we can extract the integer part without any problem of rounding, With .Split() we take the integer value before the “.” Example: 24.8 we extract the value 24.

→ Step 4: Conditional to check if there is remainder in the division by 5. Because in this case we will have to extract the missing elements from the array, which will be smaller than 5.

→ Step 5: While loop. The only difference between the then path and the else path is the stop condition. In then we are in the case where the division by 5 is not an integer, so we will have to take all the integer parts plus one. In the else we will only take the integer parts because the division by 5 is exact.

So the stop condition will be our control variable being less than 5 * the number of integer parts. If there is a remainder, we will do <= because we will take one more iteration, otherwise we will just do <.

→ Step 6: Inside the Loop. First we are going to initialize the empty temporary array. That’s because we don’t want any garbage inside it.

Now we are going to remove the part of the array that we are interested in. We will use the .Skip(N) method to skip the first N elements of the array and then we will use .Take(N) to take the next N elements from the position set in Skip().

So let’s increment our control variable by 5, so we can move forward through the array in the loop.

→ Step 7: Check the results.

The Output:

The .xaml
Main.xaml (14.5 KB)
Hope this helps!

3 Likes

Hi @anto.santhosh ,

As an alternative solution you can use skip and take function on array elements to achieve this.

Step 1: Read excel file and get IDs in a string array
IdArray=dt_test.AsEnumerable().Select(Function (a) a("ID").ToString).ToArray()
Step 2: Initialize (int)index variable to 0
index=0
Step 3: Create a while loop with condition index<=IdArray.Length
Step 4: Inside loop

  1. Assign String TextVar=String.Join(",",IdArray.AsEnumerable().Skip(index).Take(5).ToArray())

  2. Update Index Variable index=index+5

  3. print TextVar

3 Likes

Thank you very much Gabriel for your detailed explanation.
This will be very helpful for me. :clap:

1 Like

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