cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gmaasz
Helper IV
Helper IV

Convert excel values to something I can iterate through in PAD

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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" ] }
  ]
 }

 

View solution in original post

10 REPLIES 10
miketran13
Solution Specialist
Solution Specialist

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
Resolver III
Resolver III

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

gmaasz
Helper IV
Helper IV

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

Henrik_M
Resolver III
Resolver III

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 🤔

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

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

gmaasz
Helper IV
Helper IV

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

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" ] }
  ]
 }

 

View solution in original post

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.

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

 

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

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (30,654)