You’re offline. This is a read only version of the page.
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.
Share
Report
All responses (
Answers (%CSV[1]%, I would like to search using %CSV.Company%. After some experimentation, I found that converting the DataTable to JSON, renaming it, and then converting it back helped solve my problem. Here’s the code in case anyone else finds it useful.
File.ReadFromCSVFile.ReadCSV CSVFile: $'''C:\\Users\\Stanleyco93\\Downloads\\Test.csv''' Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: False ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable
LOOP FOREACH CurrentItem IN CSVTable[0]
Variables.AddItemToList Item: CurrentItem List: Field
END
Variables.DeleteRowFromDataTable DataTable: CSVTable RowIndex: 0
SET JSON TO {{ }}
SET JSON['CSVTable'] TO CSVTable
Variables.ConvertCustomObjectToJson CustomObject: JSON Json=> JSON
SET LoopIndex TO 0
LOOP FOREACH CurrentItem IN Field
Text.Replace Text: JSON TextToFind: $'''Column#%LoopIndex + 1%''' IsRegEx: False IgnoreCase: False ReplaceWith: CurrentItem ActivateEscapeSequences: False Result=> JSON
Variables.IncreaseVariable Value: LoopIndex IncrementValue: 1
END
Variables.ConvertJsonToCustomObject Json: JSON CustomObject=> JsonAsCustomObject
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
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.
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.
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!
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
You're doing it wrong. You need to use %NewVar['Table']% in the Variable field and not the Value field:
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
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
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.
#1
eetuRobo
18
Super User 2025 Season 1
#2
stampcoin
6
#2
John_Mavridis
6
Microsoft Employee
