parsing excel fields into batches of 50 as variables to use later in a flow

(0) Share

Report

Posted on by JPey

Hello,

I have a statically named excel spreadsheet (TheTest.xlsx) hosted in OneDrive.
- The sheet has an indeterminate amount of 10-digit numbers in the A and B columns.
- There may or may not be data in the B column.

A	B
1111111111	4444444444
2222222222
3333333333	5555555555
6666666666	7777777777

The goal is to batch the A and B columns entries into groups of 50.
- Each 10-digit number is delimited with a comma (,)
- Ideally blanks don't count as part of the 50
- Each 50 batch is a variable that I can call into later parts of the flow
  - Var1: 1111111111,2222222222,...
  - Var2: 5555555555,6666666666,...
  - etc.

I am not sure where to ever start here. Any help would be appreciated! Thanks for your insights.

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

v-yueyun-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi , @JPey
According to your description, do you mean you want to get the values in columns A and B, and then group them up for every 5 values, separated by commas , like this:?
This is my test flow:
(1)The List rows and initial variable action:
(2)The Apply to each action:
(3)The last two actions:
chunk(body('Filter_array'),5)

For this , you can refer to :
Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
JPey 70 on at

Like (0)

Report
Copy link

Link copied!

@v-yueyun-msft You are close to what I am asking about.

I put a small example, but I really mean 50. The total between the A and B columns is approximately 5,000 numbers, I need them batched by 50 count. The problem is I will not know how many variables I will need due to the total (A+B) changing.

Also, I need to eliminate duplicate numbers that may exist in (A+B).

Any ideas?

Was this reply helpful? Yes No
v-yueyun-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi , @JPey
For the List rows in a table action, we can open the Pagination to get your 5000 rows in your table.

And we do not need to know how many the variables we need to batched by 50 count. The chunk() function can directly group the number batched by 50 count.
And you want to duplicate numbers that may exist in (A+B).
We can just use the union() function to duplicate numbers.
Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
JPey 70 on at

Like (0)

Report
Copy link

Link copied!
The total number of rows is unknown. Right now it is about 5000, but it will grow.

I need to:
Combine A and B column entries into one list
Each value is comma separated (1111111111,2222222222,3333333333, etc.)
Eliminate blanks that may be in the B column
Batch the list into groups of 50 for an HTTP request I am sending via JSON
Loop until all the batches of 50 are sent out using the HTTPS

Was this reply helpful? Yes No
v-yueyun-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi , @JPey
Yes , the Paigination now is enough for your data . And for more information, you can refer to :
Retrieve More Than The Default Number Of Rows Of Data From Excel Using Power Automate (c-sharpcorner.com)
And if your data is very very big , you can just create a folder and separate your data to more than one Excel file.

And we can remove the duplicate value like this:
union(variables('Data'),variables('Data'))

And the result is as follows:(in my test side , it groups of 5 items)
We can just Apply to each this Array , and then we can use each item() in your http action.
The first item() is "11111,22222,11112,22223,11113".

And this action is used to filter the blank value :

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
JPey 70 on at

Like (0)

Report
Copy link

Link copied!

I will this this and report back to you. Thank you for your help. I appreciate it.

Was this reply helpful? Yes No
JPey 70 on at

Like (0)

Report
Copy link

Link copied!

@v-yueyun-msft when I try to use LIST ROWS PRESENT IN A TABLE I cannot, because I do not have a table. So I added a step to create a table. I can CREATE TABLE (Table1), but I cannot move to LIST ROWS PRESENT IN A TABLE because Table1 doesn't exist yet. Any ideas?

Was this reply helpful? Yes No
Verified answer

v-yueyun-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi,  @JPey
Thanks for your quick response and sorry for my delay response due to weekend.
If you do not have a table in Excel . One way is you can make it a table manually.

Secondly , you need to use the run Office Script to create a table in Your Excel file because this action need to have a table in our Excel File.
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let newTable = workbook.addTable(selectedSheet.getUsedRange(), true);
}

Best Regards,
Yueyun Zhang

@JPey

Was this reply helpful? Yes No