Answered

Adding multiple rows to a Datatable using variables

(0) Share

Report

Posted on by MisterH

Hi Everyone,

I am working with a sizable data set from a bespoke data source. I can create a Datatable using the 'Set Variable' step like so:

20220711_153442_PAD.Designer_l7PQDxOLHd.png

What I am also able to do is to add a new row(s) with the data hardcoded like so:

This will result in a Datatable with three columns (SKU, DES, BUY) and two rows of data. This works successfully for adding hardcoded rows several at a time --> 'Set Variable' step with the step value as: %Test + ['A','B','C'] + ['D','E','F']%

What I want to be able to do is to use a variable that contains the text value --> ['A','B','C'] + ['D','E','F'] and add it to the Datatable.

What is happening at the moment is that I am receiving an error 'No row at position 0' when I try to do this:

'Set Variable' step with the value as: %Test + MyVariable%

WHERE %MyVariable% contains the text value: ['A','B','C'] + ['D','E','F']

Any ideas on how to get PAD to accept the text string as a multiline/row addition in a single step?

Cheers

MisterH

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Haven't tried that way but if having 3 variables is ok for you then this works

where MyVariable1 will hold the value of A and so on.

Was this reply helpful? Yes No
MisterH 51 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for looking at this.

The text that is coming to me is in the form:
["ProductSKU123","Some product description here",123.00]
This is a bracketed set of information with the same three fields of data for each item in the dataset. The entire dataset comes as either a series (which can be split into a list) of these bracketed items, or it can come as [three fields here] + [three fields here] + [three fields here] + ....... as one giant long text string with all of the items already concatenated in (what appears to be) the correct syntactical form for adding to a Datatable. So this will look like:
["SKU_1","Description_1",123.00] + ["SKU_2","Description_2",456.00] + ........
In the returned form they are not broken into individual variables per-se and are simply text.

I am hoping to avoid the need to 'split' the incoming text due to the size of the dataset. Many tens of thousands of rows --> ~200k in this case. Processing each row by splitting and then multiple parsing to extract the text data into variables is grossly inefficient.

If PAD had a full fledged REGEX step this would be another matter entirely, but alas it does not.

Was this reply helpful? Yes No
MisterH 51 on at

Like
a
(0)

Report
Copy link

Link copied!
Bit of extra information from PAD that I am not sure how to interpret:

If you select and copy the steps to clipboard and then paste into Notepad you can see the inner workings of PAD (at least to some degree)

This one fails I think due to the variable OUT containing what looks like escaped text
SET Test TO { ^['SKU', 'DES', 'BUY'] } SET OUT TO $'''[\'A\', \'B\', \'C\'] + [\'D\', \'E\', \'F\']''' SET Test TO Test + OUT

And this one works I think because the information is seen as actual data:
SET Test TO { ^['SKU', 'DES', 'BUY'] } SET Test TO Test + ['A', 'B', 'C'] + ['D', 'E', 'F']

Interestingly the error that PAD reports, if you look at the 'view details' stuff, when the 'there is no row at position 0' error occurs, makes reference to adding two data tables together:

Microsoft.Flow.RPA.Desktop.Robin.Engine.Operators.Calculators.DataTableCalculator.Add(DataTable left, DataTable right)

If this is a correct understanding then the question becomes is there a way to make the text contents (in it's current format with square brackets and all) be seen as 'data' rather than the escaped looking text?

Was this reply helpful? Yes No
Verified answer

MisterH 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Everyone,
Just a quick follow up on this. It appears that the ability to achieve what I was originally intending is not possible at this time in PAD. The reason for this is the way that PAD sees and handles the data types. When the data is presented as a single giant 'blob' of information - in this case a long text string - PAD sees it as only text and not as a series of discreet elements. There appears to be no obvious way to change the data type / cast to a new data type, that is provided by PAD.

In short it cannot be done as a single step at this time.

Cheers

MisterH

Was this reply helpful? Yes No