Excel File question

[A_Learner]

Hi,
I am reading a downloaded excel file. The number of rows is dynamic.
The Excel file has last row with totals. I have to eliminate this row before read range.
How can I do that?

Thank you,

[mkankatala (Mahesh Kankatala)]

Hi @A_Learner

→ Use the Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Then use the LINQ Exression to remove the last row from the datatable.

- Assign -> Input_dt = DT_True.AsEnumerable().SkipLast(1).CopyToDataTable()

Hope it helps!!

[A_Learner]

DT_True same as input data table?

Thank you,

[mkankatala (Mahesh Kankatala)]

Sorry @A_Learner

- Assign -> Input_dt = Input_dt.AsEnumerable().SkipLast(1).CopyToDataTable()

we are removing the last row of the datatable and storing into same datatable.

Hope you understand!!

[A_Learner]

Thank you, it does not look like it is deleting the last row.
I am doing write range into the same file into the sheet.
Could it be that the previous value staying there?
How do I delete that sheet and write again?

Thank you,

[mkankatala (Mahesh Kankatala)]

You want to delete the last row in the excel file, If my understanding is correct.

Then follow the below steps,
→ Use the Excel Process scope activity and insert the Use excel file activity inside of it.
→ Give the Path of the Excel file to the Use excel file activity.
→ Inside Use excel file activity insert the Find First\Last row activity to get the last row number.
→ In the Source give Excel.Sheet(“Sheet name”),
Check the Has headers option,
Select the Last populated row option from the Configure last row dropdown and
Create a variable in the Save row number as field, let’s call the variable as Lastrow.
→ Then use the Delete rows activity to delete the Last row of the Excel.
→ Give Excel.Sheet(“Sheet name”) in the In what table or range field and Select the Specific rows in the What to delete dropdown, and pass the Lastrow variable to the At Position field.

Check the below workflow for your better understanding,

Hope it helps!!