Pass list of Datatables into Output object as Input for Parent flow

[arturito]

Hello All,

What i am trying to do is create a function to read out Excel.

It should accept as parameter an InFileName

And as output is should give an Output

The function needs to iterate over all the excel sheets and return all sheet information in the 'Output' variable

Underneath example is how i imagine it should work. However i get an error that the Output variable can't contain a list of datatables.

I've also tried creating Custom Objects, convert the list to JSON and try and parse back again in the calling function, but i can't seem to parse it back to the list objects it was, and thus that method also was a bit of a dead end.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: File Visible: False ReadOnly: True Instance=> ExcelInstance
Variables.CreateNewList List=> List
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
LOOP FOREACH Sheet IN SheetNames
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: Sheet
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Variables.AddItemToList Item: ExcelData List: List
END
SET NewOutput TO List
Excel.CloseExcel.Close Instance: ExcelInstance

Any IT wizard out there with great idea's?

[Agnius]

I am not aware of a way to do that in PAD, really. The best way to return data like that would be converting the tables to custom objects and sending those as outputs. Processing those in the parent flow really depends on what it is that you want to do with them. It should usually be possible to do pretty much anything, but might require a few extra steps to process.

If you can share some context to what you want to do with the output in the parent flow, I could maybe suggest how to do that using a list of objects instead of a list of tables.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[arturito]

Thanks for responding!

With the Custom Objects route i seem to have hit the same dead end. I removed the List object from the code, added a Custom Object (CustomOutput) as Output object. this is what happend (see screenshot)

code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: File Visible: False ReadOnly: True Instance=> ExcelInstance
Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: Sheet
SET CustomObject TO {{ }}
LOOP FOREACH SheetName IN SheetNames
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
SET CustomObject[SheetName] TO ExcelData
END
Excel.CloseExcel.Close Instance: ExcelInstance
SET CustomOutput TO CustomObject

[Agnius · Accepted solution]

In that case, convert the custom object to JSON using the Convert custom object to JSON action and pass it to the output variable as a string. The parent flow can convert it back to a custom object using Convert JSON to custom object.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[arturito]

Great thinking, this definitely works!