How to update fixed template using another file of Pivot data

Help Activities
, ,
1

Hi Everyone,

I have two excel file one is pivot table and another is pivot value to be be write in summary file and in summary file template is fixed. in summary file there three different templates creation, modifications and updation.

First filter pivot table as a creation as shown in below image

image-1774596952114
image-17745969521141920×1440 490 KB

This data we have to write in Summary file which looks like

image-1774597117990
image-17745971179901920×1440 551 KB

All three templates we have to update, how to resolve this?

Thanks

2

Hi @suraj_gaikwad

Please try this approach
1 Read Pivot Data - Use Read Range to get pivot table into DataTable.
2 Filter for Creation - Use Filter Data Table where Workflow = Creation.
3 Extract Values - Get counts for example 0–5 Days
4 Write to Summary File - Open summary template (fixed format)- Use Write Cell to map values to specific cells.
5 Repeat - Do same for Modification and Updation.

If solution works for you please mark as solution happy automations with UiPath
Regards
Nishi

3

@suraj_gaikwad Please follow below steps:

1. Read Pivot Table Data

  • Use Excel → Read Range

  • Read the pivot sheet into a DataTable

  • Make sure “Add Headers” is checked

2. Apply Filter (Creation / Modification / Updation)

Since your pivot has categories like Creation, first filter it:

  • Use Filter Data Table
    OR

  • Use LINQ (recommended)

Example (Assign activity):

filteredDT = pivotDT.AsEnumerable(). Where(Function(x) x(“MIS Summary”).ToString.Contains(“Creation”)).CopyToDataTable()

3. Extract Required Values

Now don’t copy full rows — just extract required numbers like:

  • 0–5 Days

  • 6–9 Days
    10 Days

  • Grand Total

Use:

  • For Each Row

  • Or directly:

value_0_5 = row("0-5 Days").ToString

Store values in variables.

4. Write into Summary Template

Since template is fixed:

  • Use Write Cell / Write Range

  • Map each value to exact cell

Example:

  • “0–5 Days” → Cell D6

  • “6–9 Days” → Cell E6

  • “>10 Days” → Cell F6

5. Repeat for All 3 Templates

You can:

  • Either call same workflow 3 times with different filter

  • Or loop using array:

types = {"Creation","Modification","Updation"}

Then inside loop → filter → extract → write

4

Hi @Dhruba_Jyoti_Kalita & @nishi_jain

I tried both ways , output is not coming as expected. Writing any cell.

Thank you

5

Please mark as solution so it helps other too

6

Hi @nishi_jain ,

Output is not coming as expected.

Thank you

7

Hi @suraj_gaikwad
Could you please share the details logs
Thanks

8

pivot table is a projection of a datasource, so what ever data you see here is in a proper table format in the other sheet.
so instead of trying to read and filter the pivot table, why not read the data source table directly and then make data transformations/calculations using LINQ and then update you summary template.

SG.