Testing Studio X For Business User Use - Excel Questions

Help StudioX
,
1

I am a new uiPath programmer, been using Studio for about 5 months.

Been asked to use Studio X for a small project to see how good a fit it would be for business users.

My task is to take an Excel spreadsheet and create a new one spreadsheet where Column X and Column Y are totaled for values that are the same in column A. So if we have 4 rows where column A is “684” then a new spreadsheet is produced with on row where column A is “684” all other columns are the same except column X and Y are totaled.

First off I do not see data tables being available in Studio X. Am I missing something?

Have added the Excel Add. My approach if I do not have data tables is to read rows in the first table and keep a list of values until we reach a new row that has a different number in column “A” and then write a line to the new excel and blank the values and keep reading.

Any suggestions would be greatly appreciated.

Any feedback about the success (or lack thereof) of allowing business users to use Studio X? with

2

Hi @Bryan_Schmiedeler - Do you have any sample sheet or screenshot of the file to share , to compare with question?

3

This is part of the excel.

Column D is the unique key. Column 0 and Q are what are totaled.

s1
s11934×324 228 KB

4

I am still not able to grasp you requirement…you mean, you would like to sum column O and Q based the Column D(i.e grouping)…

image
image992×83 27.9 KB

5

My example was not clear. Let me try with a more straightforward example.

Below is the (simplified) excel file. Column A is the “key” while Columns B and C data points. The BOT takes this excel and produces an output excel where columns B and C are summed when the key is the same.

a1

For the excel above the output would be as below. For each set of rows where the key is the same, copy the key to a different spreadsheet and total columns B and C. In essence there will be one row for each unique key with totals in B and C.

a2

The actual spreadsheet has about 30 columns and some will also be carried over from the first row that I copy, but the heart of the problem is contained in this example.

This might be easier in Studio but I have been asked to try it in Studio X to see if users could use Studio X to do some of their own automation.

I understand there are no data tables in X, so I am doing a for each on the input excel and trying to save results in the notebook scratch sheet and then write out a row to the “new” Excel when I have a key change in column A.

Really struggling with “saved values”. I would like to set a saved value to blank and then compare the value of cell A of the current row in my for each row loop, to detect where the break is, but I cannot seem to get that to work.

Any guidance would be much appreciated, Prasath17!

Thank you,

Bryan

6

@Bryan_Schmiedeler - Yes , as I expected what you are looking for is Grouping and Summing, which can be accomplished in Studio using LINQ query(I agree with you). To be honest, I haven’t tried it in StudioX yet. But I have done other calculation like sum/total and use Save for Later (variables) and stuffs…

If I get a chance I will try something tomorrow.

I am tagging @flavius and @studioX to get some ideas on this.

7

In StudioX, you should use Excel capabilities to process data. Your requirement is very similar to a Pivot Table:

image

image
image571×574 14.3 KB

image