Remove row from excel sheet

Help Forum
,
1

From attached excel i have to remove rows where
1.Weight with a “0” value
2.Ticker with more than one $ or any ticker 5 characters long ending in XX.
2.xlsx (8.7 KB)

3

i tried but not working could u share the code in UiPath

4

Hi @sshitol ,
use read range to get the data into datatable and use below query to filter

use assign activity to achieve this
DataTable = DataTable.Select("NOT (LEN(Ticker) = 5 AND Ticker LIKE '%XX') and Weight>0").CopyToDataTable()

then use write range

Regards,
Arivu

6

pls share in UiPath workflow

7

Hi @sshitol

Can you try this

This image depicts a UiPath workflow process that reads data from one Excel workbook, assigns it to a variable, and writes the data to another Excel workbook. (Captioned by AI)
This image depicts a UiPath workflow process that reads data from one Excel workbook, assigns it to a variable, and writes the data to another Excel workbook. (Captioned by AI)1164×564 27.8 KB

dt = (From row In DT.AsEnumerable()
              Let ticker = row("Ticker").ToString()
              Let weight = Convert.ToInt32(row("Weight"))
              Where weight <> 0 AndAlso Not (ticker.Count(Function(c) c = "$") > 1 OrElse (ticker.Length = 5 AndAlso ticker.EndsWith("XX")))
              Select row).CopyToDataTable()

Input:

The image shows an Excel spreadsheet with two columns labeled "Ticker" and "Weight," listing various tickers along with their corresponding weights. (Captioned by AI)
The image shows an Excel spreadsheet with two columns labeled "Ticker" and "Weight," listing various tickers along with their corresponding weights. (Captioned by AI)636×706 8.39 KB

Output:

A spreadsheet displaying a list of 11 financial instruments with their corresponding ticker symbols and weights. (Captioned by AI)
A spreadsheet displaying a list of 11 financial instruments with their corresponding ticker symbols and weights. (Captioned by AI)566×507 6.02 KB

Regards,

8

hello @sshitol ,

See Below Output is expected output

The image shows a UiPath workflow for reading an Excel range, converting the data table to an enumerable, and logging a message, with the Immediate panel displaying the contents of the "dt_Input" variable before and after conversion. (Captioned by AI)
The image shows a UiPath workflow for reading an Excel range, converting the data table to an enumerable, and logging a message, with the Immediate panel displaying the contents of the "dt_Input" variable before and after conversion. (Captioned by AI)970×546 48.2 KB

dt_Input.AsEnumerable().Where(Function(row)  row.Field(Of Double)("Weight") <> 0 AndAlso  Not (row.Field(Of String)("Ticker").Count(Function(c) c = "$"c) > 1 OrElse  (row.Field(Of String)("Ticker").Length = 5 AndAlso row.Field(Of String)("Ticker").EndsWith("XX")))).CopyToDataTable

Main.xaml (8.2 KB)

Regards
Dheerandra Vishwakarma

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.