Read data by use header (column A)

Help Activities
, ,
1

I have file excel as below.

image

If I want to read data column B that header (column A = Type)

Please guide me for solve it.

Thank you.

Forum Engagement Daily Reports
2

Hi @Stef_99

What’s your expected output?

Regards

3

@Parvathy expect output in column B

4

Hi @Stef_99

Your output should be like for Type the value should be a. Is my understanding right?

Regards

1 Like
5

@Parvathy Yes. Correct

1 Like
6

Hi @Stef_99

Steps to read data from column B based on data in column A:

  1. Read Excel Data: Use the “Read Range” activity to read the entire Excel sheet into a DataTable.
  2. Loop Through Rows: Use a “For Each Row” activity to iterate through each row in the DataTable.
  3. Access Column A Data: Inside the loop, use the row("ColumnName") syntax to access the value in column A for the current row.
  4. Read Corresponding Data from Column B: Use the value from column A to determine which row of column B to read.

For example:

  1. Drag a “Read Range” activity into your workflow and configure it to read the Excel file into a DataTable variable, let’s say dtExcelData.
  2. Add a “For Each Row” activity and set its DataTable property to dtExcelData.
  3. Inside the loop, use an “Assign” activity to get the value from column A for the current row:
    valueA = row(“ColumnA”).ToString()
  4. Using the value from column A, find the corresponding value from column B:
    valueB = dtExcelData.AsEnumerable().Where(Function(x) x.Field(Of String)(“ColumnA”) = valueA).Select(Function(x) x.Field(Of String)(“ColumnB”)).FirstOrDefault()
    Replace “ColumnA” and “ColumnB” with the actual column names in your Excel sheet.
  5. You can then use valueB as needed.
7

Hi @Stef_99

=> Read Range Workbook. Make sure to Remove Add Headers option in properties Panel of Read Range Workbook.
image
Output-> dt

=> Use below syntax in Assign:
Output = dt.AsEnumerable().Where(Function(row) row(0).ToString() = "Type").Select(Function(row) row(1).ToString()).FirstOrDefault()

Check the below image for better understanding

image
image1541Ă—812 47.4 KB

Hope it helps!!

1 Like
8
  • Loop the first row using For Each activity
  • Then Assign dt.Columns(columnIndex).ColumnName = firstRow(columnIndex) this will rename the header
  • After the loop delete the first row
9

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