Sum of columns after append

[Jeeru_venkat_Rao]

Hello Friends,

I have to append count in master sheet everyday after bot execution but now I want daily sum of master sheet.

See below screenshot:-

next day it should be like

Every day new entry will append and at the end I need sum.

Add sum after append.xlsx (9.1 KB)

Pls help.

[Jeeru_venkat_Rao]

Now I am able to do sum but last row not delete. I have used remove data row but not working.

[Jeeru_venkat_Rao]

Anyone can suggest or help here
@ashokkarale
@Anil_G

[naveen.s]

@Jeeru_venkat_Rao

Now I am able to do sum but last row not delete. I have used remove data row but not working.

@Jeeru_venkat_Rao ,

Try this:

If you want to ensure that the last row of the DataTable (which contains the previous day’s total) is deleted before you append the new entry and calculate the daily sum, you can follow these steps:

Steps to Remove the Last Row

1. Check If the DataTable is Not Empty: First, you need to check if the DataTable has rows. This will prevent errors if the DataTable is empty.

2. Remove Last Row: Use the RemoveAt method on the DataTable’s Rows collection to remove the last row.

3. Continue with Your Process: After removing the last row, proceed with appending the new count and calculating the new sum.

Example Implementation

Here’s how you can structure your UiPath workflow to remove the last row before appending the new count:

1. Check if DataTable Contains Rows

Before removing the last row, you should check if your masterDataTable has any rows. You can use an If activity for this:

If masterDataTable.Rows.Count > 0
{
    // Remove the last row
    masterDataTable.Rows.RemoveAt(masterDataTable.Rows.Count - 1)
}

2. Append New Count Entry

After potentially removing the last row, you can proceed to append the new count as described in your previous workflow:

DataRow newRow = masterDataTable.NewRow() 
newRow("CountColumnName") = newCount ' Replace "CountColumnName" with your actual column name
masterDataTable.Rows.Add(newRow)

3. Calculate Daily Sum

Now, perform the sum calculation:

totalCount = masterDataTable.AsEnumerable().Sum(Function(row) Convert.ToInt32(row("CountColumnName"))) ' Adjust for the correct type

4. Add the New Total Count Row

If you wish to add the total as a new row:

DataRow sumRow = masterDataTable.NewRow()
sumRow("CountColumnName") = totalCount
masterDataTable.Rows.Add(sumRow)

5. Write Back to Excel

Finally, write the modified DataTable back to the master sheet:

Excel Application Scope (Path to your master Excel file)
{
    Write Range - Input: masterDataTable, Range: "A1" ' Adjust the range as necessary
}

Full Sequence Overview

Here’s a simplified version of the full workflow:

1. Excel Application Scope - Open master file.
   • Read Range → masterDataTable
   • If masterDataTable.Rows.Count > 0
     • Then → masterDataTable.Rows.RemoveAt(masterDataTable.Rows.Count - 1)
   • Create New DataRow → newRow and append it to masterDataTable
   • Calculate Sum → totalCount
   • Create Sum DataRow → sumRow with totalCount
   • Write Range to master file

Important Notes

• Data Integrity: Ensure that the DataTable is not empty before trying to remove the last row to avoid an IndexOutOfRange exception.
• Count Column: Replace "CountColumnName" with the actual name of your count column.
• Error Handling: Consider adding error handling (like Try-Catch) to gracefully manage any unexpected situations, such as issues with the Excel file or data types.

[Jeeru_venkat_Rao]

Please find below screenshot and let me know where I am doing wrong.
It is working fine except removing last row.

[Jeeru_venkat_Rao]

dt=dt.AsEnumerable.Take( dt.Rows.Count - 1).CopyToDataTable

[Jeeru_venkat_Rao]

I have tried - dt.Rows.RemoveAt(dt.Rows.Count - 1)
but throw error-Expression does not produce a value

[naveen.s]

@naveen.s

masterDataTable.Rows.RemoveAt(masterDataTable.Rows.Count - 1)

Why do you wanna delete the last row??

[Jeeru_venkat_Rao]

Because after appending it will write Totals summary. see screenshot in top. If I can’t delete that one than append activity will append and calculation will be wrong

[naveen.s]

Try using the expression if row(“ColumnName”).contains(“Tota”) then remove using the condition

[Sanjay_Bhat]

Hi @Jeeru_venkat_Rao

This is a simple demo workflow
Main.xaml (10.6 KB)

Check this out!
Hope this helps!

[Jeeru_venkat_Rao]

@Sanjay_Bhat → can you re-share this xaml completely in zip. It is not opening

[Jeeru_venkat_Rao]

Its working @Sanjay_Bhat .

Thank you so much Sanjay & everyone who gave me response.

[Jeeru_venkat_Rao]

One more thing Sanjay, can we do it if cell value “Total” available then it remove last row otherwise not.

[Sanjay_Bhat]

Okay you want to remove the row if it has “Total” right…
Then you can use
if dt_data.Rows(dt_data.RowCount)(“Date”).ToString = “Total”

This way
Thank you!