How to iterate the data from excel file

Help Studio
, , ,
1

Hello,

I have to filter the data from excel file
Scenario is that
Downloading excel file and there is multiple data with header, Now in Excel file there is two option which 1) Probable 2) Confirmed so bot will check only Is there Probable and put only probable case id and ignore which is Confirmed.

thanks

2

Hi @Suraj_Gaikwad_Nuvama_Grou

=> You can use the filter datatable activity to filter the datatable with conditions.
=> Before doing filter use read range workbook activity to read the excel and store in a datatable. Give this Datatable Variable as a input for the Filter datatable activity.
=> After filter you get the output data as datatable.
=> After this use the write range workbook activity to write the datatable to excel.

Hope it helps!!

3

  1. Read Excel File: Use the “Read Range” activity to read the data from the Excel file into a DataTable variable.

  2. Filter Data: After reading the data, you can use the “Filter DataTable” activity to filter out the rows based on the “Probable” condition. Here’s how you can do it:

    • In the “DataTable” field, provide the DataTable variable that you’ve read from the Excel file.
    • In the “Filter” field, you can use an expression to filter the rows. For example: "ColumnWithOption = 'Probable'". Replace "ColumnWithOption" with the actual column name in your Excel file where the options are mentioned.
    • This will create a new DataTable with only the rows that meet the “Probable” condition.

  3. Loop Through Filtered Data: Now, you can use a “For Each Row” activity to loop through the filtered DataTable. Inside the loop, you can access the values in each row and perform the necessary actions.
    Remember to replace “ColumnWithOption” with the actual column name that contains the options (“Probable” and “Confirmed”). Also, adjust the column names and variable names according to your actual Excel file structure and the names you want to use in your workflow.

Hope this helps

4

@Suraj_Gaikwad_Nuvama_Grou

Two approaches

With Datatable
If you need the data in datatable then read the data from excel into dt and then

dt.AsEnumerable.Where(function(x) x("Options").ToString.Equals("Probable")).CopyToDatatable - this can be used in assign activity then the output would contain only probable rows

With Excel
If you need to get from excel only and is there something like status update you need to do on excel then

  1. Use excel file - given excel file path
  2. Use for each row in Excel activity and given range as Excel.Sheet(“SheetName”)
  3. Inside the loop use if condition with currentRow.ByField("Options").StringValue.Equals("Probable")
  4. On the then side you have Probable rows…and can use it as needed

Hope this helps

cheers