cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Solution Supplier
Solution Supplier

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

Resolver I
Resolver I

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

Helper IV
Helper IV

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

Resolver I
Resolver I

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?

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
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (64,894)