Extract result from excel to Power Automate Flow

[AnOO82]

Hello everyone

I have problems with my flow.

It actual gets all the individual codes from each file (located in two different folders) and write it into a table. An short script brings the numbers in the correct order. I needed an array with all the Codes. I used an Excel function to create it.

Now the problem: I can't find any action to extract the information back to Power Automate.

I need: [4,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21]

so that my follwing steps can find all the missing numbers.

Does anyone has an idea? 

[fargv88]

Hi @AnOO82 ,

To extract the information from an Excel table and use it in Power Automate, you can use the "Excel - Get rows" action to retrieve the rows from the table and then use the "Compose" action to extract the values that you need from the rows.

Here's an example of how you can do this:

1. Add the "Excel - Get rows" action to your flow and configure it to retrieve the rows from the table that contains the codes.

2. Add a "Compose" action to your flow and use the following formula to extract the codes from the rows:

json(body('Excel_-_Get_rows')).value.Code

This formula will extract the values from the "Code" column of the rows and return them as an array of values.

1. Use the output of the "Compose" action in the following steps of your flow as needed.

With this configuration, the "Compose" action will extract the codes from the Excel table and return them as an array that you can use in the following steps of your flow.

I hope this helps. Let me know if you have any questions or if you need further assistance.

[AnOO82]

Hi fargv88,

thanks for your assistance.

The problem with "Get a row" is that I do not have a Key Value or any dynamic content to use.

And I really need the sort-action because the codes are not in the right order and there are also emtpy cells in the list.

Do you have another idea? 🙂

[grantjenkins]

@AnOO82 A few questions:

1. How many files are you trying to extract data from?
2. Are they Excel files or CSV files?
3. If Excel files, do they have Tables defined, or just raw data?
4. Do they all have the same headers (Column titles)?
5. Are you able to show the content from the files you are trying to extract from?
6. What do you want to do with the final output (sorted array of numbers)?

[AnOO82]

Hey @grantjenkins 

1. I have two different Sharepoint folders with > 200 .pdf files. Each file has a code, the codes comes from the KI Builder.
2. My actual flow writes all codes in one Excel file.
3. and I have only one table in column A
4. The header is only "Column1"
5. I have an column for the Codes in my Sharepoint library. 
6. and I need a [ with all the numbers from the first to the highest number ] .The following steps searches for any missing numbers in the array. That part of my flow already works but for putting both parts together I need the codes in the right order.

[fargv88]

If you don't have a key value or any dynamic content to use with the "Get rows" action, you can retrieve all the rows of the Excel table and then use the "Sort array" action to sort the rows in the correct order.

To use the "Sort array" action, you can do the following:

1. Add a "Get rows" action to your flow, and specify the table and worksheet that contain the rows that you want to retrieve.
2. Add a "Sort array" action to your flow, and specify the array that you want to sort. You can use the following expression to reference the array returned by the "Get rows" action:

@variables('ExcelRows')

          3.  In the "Sort array" action, specify the criteria that you want to use to sort the array. You can use the "Field" field to specify the column that contains the codes, and the "Direction" field to specify whether you want to sort the array in ascending or descending order.

         4. Once the array is sorted, you can use the "Select" action to extract the values that you need. For example, you can use an expression similar to the following to extract the values of the "Code" column:

@{variables('SortedArray')[0]['Code']}

This will extract the value of the "Code" column from the first row of the sorted array. You can use a similar expression to extract the values from the other rows of the array as well.

I hope this helps! Let me know if you have any other questions.

[grantjenkins]

@AnOO82 Just to confirm - each of your PDF files has a property called Barcode where you get the code from? So, you're not actually retrieving anything from within each PDF file - just getting the value from the property.

And is there a reason you are writing the data to Excel? We can easily just build up an array with all the Barcodes and sort them how you want without using Excel if that's what you need.

[AnOO82]

@grantjenkins 

it's like a Code-Sticker for incoming letters, which sticks is on every letter. Just to be save, that there is no letter missing I read out every Code and want to find missing codes. 

I do not really need an Excel file- It was my first idea, because I have to folders where letters are saved and I find it really hart to build an array above two Sharepoint folders.

[grantjenkins · Accepted solution]

Assuming we are getting the Barcodes from a column in the folder, this is how I would build the flow.

For this example, I have a library called PDFs with three folders, Folder A, Folder B, and Folder C. We will get the files from Folder A and Folder B only.

See full flow below. I'll go into each of the actions.

Get files x 2 retrieve the files from the two folders.

Select uses a union expression to combine the values of both Get items. For the Map, it uses Text mode (see arrow on screenshot below) and converts each Barcode to a number. See expressions below. This will output an array of unsorted numbers.

//From (input)
union(outputs('Get_files_(properties_only)_Folder_A')?['body/value'], outputs('Get_files_(properties_only)_Folder_B')?['body/value'])

//Map - Barcode (assuming the internal column name is called Barcode)
int(item()?['Barcode'])

Compose then uses a sort expression to sort the numbers in ascending order. The expression used is.

sort(body('Select'))

After running the flow, we would have the following output.

[
  1,
  2,
  3,
  5,
  8,
  9,
  10,
  11,
  12,
  18,
  19
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[AnOO82]

Thanks for this way short flow without using Excel. That's brilliant.

Only one problem: 

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '@int(item()?['Barcode'])' failed: 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Why do I get this error?

[grantjenkins]

Is Barcode the internal name of your column in SharePoint? And do any of your rows not contain a barcode?

[AnOO82]

Yes, there are rows where the barcode column is empty - letters before etablish the new routine...

The internal name is 

"#Collection(String)","Barcode":"7","Created": 

[grantjenkins]

What do you want to do with the rows that don't have a barcode? Do you just want to ignore them?

[AnOO82]

Yes, just ignore. I only need the rows with barcodes

[grantjenkins]

For each of the Get items add a Filter Query to exclude items where the Barcode is null.

Barcode ne null

[AnOO82]

Ohhh yes, it finally worked!! 

Thank you so much for your help and showing me some new functions 🙂