How to extract values from excel column dynamically

Hello all,

I am new RPA dev, need help.
I have a workflow ready, I am reading excel column and in that col there will be 1 value e.g(D5, JK or LD) any such combination. I need to do further process on that value and find the output for that value.

This I did. But now as per changed business requirement, there will be values separated by comma, or cell can be blank as well, or there will be only value, or many,

I want to run my exiting workflow for these values, I can used for each value and can process.

how to read those value dynamically?
if no values then e should display log msg
if values there split with comma, remove spaces if any,
for each value ( further i have workflow)

Hi @Shruthika_r ,

Step-by-Step Implementation

  1. Read Excel Data into DataTable:
<Excel Application Scope FilePath="path\to\your\excel.xlsx">
  <Read Range SheetName="Sheet1" Range="A1" Output="{x:Reference dt}" />
</Excel Application Scope>
  1. Iterate Through Each Row:
<For Each Row In="{x:Reference dt}" DisplayName="For Each Row">
  <Sequence>
    <!-- Get the value from the specific column -->
    <Assign DisplayName="Get Column Value" To="[colValue]" Value="[currentRow("YourColumnName").ToString()]" />
    
    <!-- Check if the cell is empty -->
    <If Condition="[String.IsNullOrEmpty(colValue)]">
      <Then>
        <Log Message Level="Info" Message="No values to process." />
      </Then>
      <Else>
        <Sequence>
          <!-- Split the values by comma and trim spaces -->
          <Assign DisplayName="Split Values" To="[valuesArray]" Value="[colValue.Split({","c}, StringSplitOptions.RemoveEmptyEntries).Select(Function(s) s.Trim()).ToArray()]" />
          
          <!-- Iterate through each value -->
          <For Each Item In="{x:Reference valuesArray}">
            <Body>
              <!-- Process each value -->
              <Invoke Workflow File WorkflowFileName="path\to\your\existing\workflow.xaml" Arguments="value: item" />
            </Body>
          </For Each>
        </Sequence>
      </Else>
    </If>
  </Sequence>
</For Each>

Regards
Sandy

2 Likes

In details

Detailed Explanation

  1. Read Range:
  • Excel Application Scope opens the Excel file.
  • Read Range reads the entire sheet into a DataTable.
  1. For Each Row:
  • Iterates through each row in the DataTable.
  1. Assign Activity:
  • colValue gets the value of the specific column for the current row.
  1. If Condition:
  • Checks if colValue is empty or null.
  • If empty, logs a message “No values to process.”
  • If not empty, proceeds to split the value by comma and trims any spaces.
  1. Split and Trim Values:
  • valuesArray splits colValue into an array of strings, removes empty entries, and trims spaces.
  1. For Each Value:
  • Iterates through each value in valuesArray.
  • Invokes the existing workflow for each value.
2 Likes

hi @Shruthika_r ,


Show me your example file,
I think to flexibly get a value you can use for each row
Regards,

1 Like

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