Data Table to JSON object in PAD

[LyCo]

Hi!

I have a data table (in Excel) that I need to send via an API call (invoke web action) in Power Automate Desktop.

Is there an easy way to convert this table to a JSON object? Or how is this usually handled in similar situations when data needs to get passed via this action?

Also, I noticed that in Winautomation there were the features of creating a new custom object and adding a new property to a custom object. Has this action been removed in PAD?

Thanks in advance for any input.

[muhammednihad]

Thanks @Agnius. It works!!

[pjwilson1987]

I see how @Agnius created the JSON in his example.

But if I have a datable I extracted from Excel like this example:

Item	Cost	Stocked	Ship Date
Wheel	$20.50	4	3/1/2016
Door	$15	2	3/15/2016
Engine	$100	1	3/20/2016
Totals	$135.50	7	3/20/2016

Is it possible to get my JSON string similar to this structured format?

(Basically just the values for each row).

[Agnius]

What you want there is a list of lists. If you really want to do it this way (not sure why, as it would then be more inconvenient to use this data afterwards), you would need to create a new list variable and add each row to the list before adding the list into the object.

The first row is the header row. If you have a table, you can access the headers as a list by using %Table.Columns%. The further rows are simply accessed as %Table[RowIndex]% or via a For Each loop.

So, your steps should be as follows:

1. Create a new list
2. Add %Table.Columns% to the list
3. Do a For Each loop on %Table% and add %CurrentRow% to the list
4. Add the list to the object as %Object['values']% (by using Set Variable).

[Mithlesh]

Hi @AgniusBartninka 

I have requirement where i am writing the data to Data Table and i wanted to pass it as JSON to power automate cloud from desktop flow. I have tried the your solution to solve the issue but i am encountering error. When i assign to DataTable to custom object variable then it covert the custom variable into Data Table and then this variable is not available for Convert custom object to JSON. Since this happen when the flow is executed it throw error "Argument 'CustomObject must be 'Custom Object'"  . Below the steps i have followed in desktop flow, Can you please help what i am doing wrong here

1. Created a Input variable of type Datatable, Variable name: NewInput

2. Create a empty custom object variable 

3. Assign the NewInput(Datatable) to custom object variable 

4. Covert the custom object to JSON

TIA,

Mithlesh

[Agnius]

Hello. What you are doing there is overwriting the custom object with a table. This means that you create a custom object, but then turn it into a data table variable by overwriting it, and it is thus no longer a custom object by the time you try to convert it. Instead, you should assign the data table to a property inside the custom object and not the entire object.

In your step #3, instead of setting it to %NewVar%, try setting it to %NewVar['Table']%. This will add a property called 'Table' to your custom object with the data of %NewInput% inside. This way your custom object will remain as custom object that stores a table inside of it.

[Mithlesh]

Hi @Agnius ,

I have added %NewVar['Table']% to 3rd step but it give me the Error:  Variable 'NewVar' does not have a property 'Table'. I even tried setting to %NewVar['NewInput']% but i am getting the same error.  Can you please advise

TIA

Mithlesh

[Mithlesh]

Hi @Agnius ,

I have added %NewVar['Table']% to 3rd step but it give me the Error:  Variable 'NewVar' does not have a property 'Table'. I even tried setting to %NewVar['NewInput']% but i am getting the same error.  Can you please advise

TIA

Mithlesh

[Agnius]

You're doing it wrong. You need to use %NewVar['Table']% in the Variable field and not the Value field:

[Mithlesh]

Hi @Agnius , Thank you so much to resolve the issue. 

-- Below is comment for the readers

When you set a variable to custom object , In the new value type %{{ }}%. I was missing these % and varibale was not set to custom object.

Thanks,

Mithlesh

[cjaldrich74]

I'm not sure this is possible, but I have a datatable created from the action Read From Excel. It has 6 columns and 60 rows. I would like to create a new datatable that contains only the rows that match another variable. The purpose would be to use the new datatable in a For Each Loop so that it doesn't waste time iterating through all of the datarows for only a few matches. It ends up making the flow unnecessarily time consuming.

So I went through the steps to convert the datatable to JSON. Now, what can I do to filter only the matches? I have tried scripting using Python and JS, but I am getting errors. 

If I manage to figure that step out, would I be able to use Python to return the JSON as a datatable? Maybe I need to just split the Excel table into several smaller ones for each match I am trying to loop through?

I am learning as I go, and it would sure be nice to make this happen efficiently in PAD!

[Agnius]

You don't need to convert your table to JSON. You can use Find and replace in data table to find the rows you want, and then build a loop to insert them one by one to your new data table.

I would recommend creating a separate topic for this, though.

[cjaldrich74]

I appreciate your response. It sent me in a different direction, and I ended up using SQL to import the data from Excel. It not only allowed me to accomplish exactly what I was trying to do, but it also saved time in the flow by eliminating all of the steps to open Excel, read from and close. It also kept me from having to add more loops.

[HassenH]

Thanks for this mate really helpfull but i have a issue one my side. 

Here are the steps as you mentionned them. 

Steps

This is the data in my table before converting to JSON. 

Table before converting to Json

And that's the output in my JSON. 

Json Data

Any ideas ? 

Thanks y'all