Read excel or formula tips

Help Studio
1

image
image280×593 5.62 KB

Hi I wanted to get the name that have “Available” status only.
Meanwhile the table is divided into 3 table and each have different function.

How to get the name with the status “Available” only and write to datatable

2

@ainaasyhda

  1. Use “Read Range” activity three times to read the data from the three tables into three separate DataTables (let’s call them dtTable1, dtTable2, dtTable3).

  2. Use the “Build DataTable” activity to create a new DataTable (dtFiltered) with the same structure as the original tables.

  3. Use a For Each Row loop to iterate through dtTable1.

  4. Inside the loop, use an If activity to check if the row has “Available” status.
    Condition: row(“Status”).ToString.Trim.Equals(“Available”)

  5. If the condition is true, use the “Add Data Row” activity to add the row to dtFiltered.

  6. Repeat steps 3-5 for dtTable2 and dtTable3.

  7. After processing all three tables, use the “Write Range” activity to write dtFiltered to a new Excel or CSV file.

  8. Optionally, you can display or log the content of dtFiltered to verify the extracted data.

3

Can I use modern excel activity with DataTable activity?

4

@ainaasyhda

Yes you can use modern activity or simply use read range workbook, instead of excel process scope

1 Like
5

Hi @ainaasyhda

Try this:

  1. Read the data from each of the three tables and store them in separate datatables (table1, table2, and table3).
  2. Use the Filter Data Table activity for each of the three datatables to keep only the rows with the “Available” status.
  3. Use the Merge Data Table activity to combine the filtered results from the three tables into a new datatable (mergedTable).
1 Like
6

Hi @ainaasyhda

Try like this

image
image554×734 24.3 KB

Regards,

7

Hi @ainaasyhda

Please find the below xaml.

ExcelTask.zip (141.4 KB)

O/P Excel:

Input.xlsx (10.3 KB)

I hope it helps!!

8

@ainaasyhda

have you tried this?

9

Hi @ainaasyhda

For table2 change
For each Row in Excel.Table(“Table2”)

image
image880×1510 48.5 KB

Hope it helps!!

10

For this method, will it read each table ? Sometimes different table need to output different column value

11

No It was read as one table. If the columns are same in all then it will works. @ainaasyhda

Its works check the below excel.

Input.xlsx (10.3 KB)

Regards,

12

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