Share
ReportHi,
I am trying to write a flow that gets data from a excel files, and sends it using the 'Invoke Web Service' (in a loop).
Currently I am opening the excel file and reading it into a Datatable variable using 'Read from Excel worksheet'.
The aim then is to provide this variable to the 'Invoke Web Service' action in PAD.
When the Datatable variable is used in the 'Invoke Web Service', it is converted into a string with comma seperated cells, with rows seperated by new lines.
E.g.
A, B, C
1, 2, 3
But I need to provide this as a string representing an array that can be parsed on the server side (JSON parsing).
So I need it in the format:
[["A","B","C"],["1","2","3"]]
I could not find a way to do this easily - the only method I could find was using a series of replace actions.
The problem is, some of the fields contain commas (,) so when I use the replace action they also get replaced and the resulting 2D array string is incorrect.
e.g. PAD shows the Datatable values as:
Steve, Smith, 50,
Which would then provide the array as:
[["Steve", "Smith", "50"]], but it should be [["Steve, Smith", "50"]].
Does anyone have any ideas how to better manage this?
For background, the excel files we are dealing with are around 20,000 lines and 50 Columns, and there are several - so looping through every item in the datatable in Power Automate Desktop in order to build the array string is not a viable solution as it takes way too long.
Thanks for your help!
All responses (
Answers (
243
