Creating pivot for 2 Columns in excel

[aadhyadas]

Hi ,
I am using excel file which contains columns city and TxAmount value . I need to pivot the City wise to sum up the TxAmount value. How can achieve this?

[mkankatala (Mahesh Kankatala)]

Hi @aadhyadas

You can use the Create Pivot Table activity to create the Pivot based on your requirement.

Create Pivot Table activity is the Modern Excel activity, you have to use this activity inside the Use excel file.

To know more about Create Pivot Table activity check the below one,

Activities - Create Pivot Table

Hope it helps!!

[sandyarpa767]

Hi @aadhyadas ,

Detailed Steps:

Step 1: Read Data from Excel

1. Drag an Excel Application Scope activity onto the workflow.

• Set the FilePath to your Excel file (e.g., "Input.xlsx").

2. Inside the Excel Application Scope, drag a Read Range activity.

• Set the SheetName to the name of the sheet you want to read.
• Set Output to a DataTable variable, e.g., dtInput.

Step 2: Process Data with LINQ

1. Add an Assign activity to pivot the data.

• Create a new DataTable variable, e.g., dtPivoted.
• Use LINQ to group and sum the data. In UiPath, you can use an Assign activity to store the results of the LINQ query in a Dictionary<string, Decimal> variable.Here’s the LINQ expression you can use:

dtInput.AsEnumerable().
    GroupBy(Function(row) row.Field(Of String)("City")).
    Select(Function(g) New With {
        .State = g.Key,
        .TotalCCInvoice = g.Sum(Function(row) row.Field(Of Decimal)("TxAmount"))
    }).ToDictionary(Function(x) x.State, Function(x) x.Txamount)

2. Convert Dictionary to DataTable:

• Use Assign activities to convert the dictionary to a DataTable if needed.Here’s a sample way to convert the dictionary to a DataTable:

dtPivoted = New DataTable()
dtPivoted.Columns.Add("City", GetType(String))
dtPivoted.Columns.Add("TotalTxAmount", GetType(Decimal))

For Each kvp In dictPivotResult
    dtPivoted.Rows.Add(kvp.Key, kvp.Value)
Next

Step 3: Write Pivoted Data to Excel

1. Drag another Excel Application Scope activity onto the workflow to save the results.

• Set the FilePath to the location where you want to save the output file.

2. Inside the Excel Application Scope, use the Write Range activity.

• Set the DataTable property to dtPivoted.
• Specify the sheet name and range if needed.

Regards
Sandy

[aadhyadas]

it shows unable to convert “” to double

[sandyarpa767]

Make sure u dont have null values in the columns