Convert excel values to something I can iterate through in PAD

[Anonymous]

I want to use the data from an excel file in PAD, but am unsure of what format to send the data to the PAD flow and then also how to iteratively work through each row of data and input it into our ERP system. I can convert the data to a JSON array, but that left me stranded as I don't know how to take the JSON array in PAD and get the appropriate cells from the data to input into our system.

An help would be appreciated.

[miketran13]

Hi there,

There are two ways that you can read data from an excel file as a datatable.

1. First option: Using Read from excel action. I really don't like this action since you have to fix the position. That means you need to know start column, end column, start row, end row to use this action. That does not work in the real world because your excel file is dynamic and you won't know the exact number of rows and columns. 
2. Second option: Using Write to CSV file action (you have to open an excel file), then using Read from CSV file to get the data. By using this way, you will get the datatable with all the data from the file without specifying start, end for row and column. 

Thanks and hope it can help you. 

Mike

---------------------------------

Did I answer your question? Please consider to Mark my post as a solution! to guide others

[Henrik_M]

You can't send a DataTable as input to a Desktop flow. You will have to read it locally like Mike describes.

[Anonymous]

So I can't read JSON within the Desktop Flow?

[Henrik_M]

You should actually be able to pass JSON from Power Automate Cloud to PAD as Text Input. I just checked, and there does not seem to be any text length limit when running from the Desktop console 🤔

[Anonymous]

Great thanks, the tough part I have is to actually read the data and process it in pAD. No idea where to start.

[Henrik_M]

Is the Excel file available locally from the PAD computer, or is it required that the data is loaded from the cloud?

[Anonymous]

It will only be available on the cloud, so not locally.

[Henrik_M · Accepted solution]

Then I think there are two options:

1. Processing the loop in the cloud, and run a desktop flow for every item. I'm not really an expert on this yet.

2. Convert the Excel data to JSON and Input it. This way, the desktop flow runs only once.

Paste the following into an empty flow to see how it could work.

Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
File.ReadText File: $'''%SpecialFolderPath%\\\\json.txt''' Encoding: File.TextFileEncoding.DefaultEncoding Content=> FileContents
Variables.ConvertJsonToCustomObject Json: FileContents CustomObject=> JsonAsCustomObject
LOOP FOREACH CurrentItem IN JsonAsCustomObject.cars
    Display.ShowMessage Message: CurrentItem.name Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False
END

You will also need a text file json.txt on your desktop, with the following content:

{
  "cars": [
    { "name":"Ford", "models":[ "Fiesta", "Focus", "Mustang" ] },
    { "name":"BMW", "models":[ "320", "X3", "X5" ] },
    { "name":"Fiat", "models":[ "500", "Panda" ] }
  ]
 }

[Anonymous]

The idea seems great, just want to check I get the following error:
Invalid JavaScript property identifier character: ,. Path 'cars[0]', line 3, position 14.

[Henrik_M]

Hmmm... I don't know about that 🤷🏼‍♂️

But try and see if you can get it to work with your own data.