Help on extracting data from excel

Hi, I am relatively new to uipath and I am facing some problems when working. Can someone help teach me how I can use the individual invoice number to extract out the total amount in excel in its respective row using uipath?

Hi @Juhy657

  • Use Excel Application scope Activity

  • Read the excel file using Read Range Activity - Set the Output as DTOutput

  • Use For each row in Data Table Activity - Enter VB Expression as - DTOutput

  • Use Assign activity
    LHS - RowCount
    RHS - 4

  • Use Assign activity
    LHS - ColumnCount
    RHS - 0

  • Use Assign activity
    LHS - DataValue
    RHS - Currentrow(ColumnCount)(RowCount)

  • Log message "Print the " - DataValue

Increment the ColumnCount & RowCount

Hope it will work

Regards
Gokul

Another method refer to the screenshot

Regards
Gokul

Sorry, the output for this is not like how I need it to, is there any other way to do it?
For example, I ask Uipath to read the “Invoice Number” column and store it in a variable. Then, from the variable, if have the Invoice Number : PR001, I want to get the value, 5371.4 and 5692.4, from the “Total Amount” column. If I have the Invoice Number: PR002, I want to get 588.5 from the “Total Amount” column and so on.
Sorry for the trouble, I was stuck at this for 2 weeks. Thanks in advance.

Can you send the sample excel file @Juhy657

Hi @Juhy657 ,

Have we tried using the Look Up Datatable Activity ?

You can assign the Properties value to it as below :

DataTable : InputDT, datatable read using Read Range Activity

LookupValue ; Your Dynamic Invoice Number value. Example : PR002

Lookup Column -> ColumnName : Source Column where you want to Search the LookupValue. Example : Invoice Number

Target Column -> ColumnName : Destination Column, from where you want to retrieve the matching row value. Example : Total Amount

Output -> CellValue : A variable need to be declared of String Type, say TotalAmtValue. This would be the Total Amount value that matched the Invoice Number value.

Let us know if you have already tried this and didn’t get the expected output.

Also, We could try for a Linq Select as an Alternate.

@Juhy657
Here is the workflow for your reference

Excel.zip (63.2 KB)

Hope it will solve your query

Regards
Gokul

Thank you very much, it works!

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