Extract data from many invoice xls

Help
1

Hi Everyone.

I have many invoice files with xls type.

I want to extract some field ( Yellow ) base on keyword ( Green ).

I have tried to make XAML file as attach file but I don’t know how to extract base keyword in files.

Thanks you!
Extract Invoice Data.zip (30.6 KB)

2

Hi Everyone.

I can do this by extract cell… but the format can be changed, so I want extract data base on keywords.

Pls kindly support me this case.

Thanks you so much!

3

hi @trunghai

I am kinda confused with what you are asking for ?

  1. Do you mean the column name can change ?
  2. Are we looking for a keyword everytime in a column or columnname ?
1 Like
4

Hi bro @Shubham_Varshney

I want to extract all of information which highlighted with “Yellow” but base on keyword highlighted “Green”

Because the files format can be changed ( insert or delete row )…. so I could not extracted data if base on the cell only… I want to extract base on keywords.

Ex: in the invoice attached file: If the text is “Số tờ khai” , then it will extract “100958379652”
Sample Invoice.xls (53.5 KB)

5

hi @trunghai

Hope this activity help you :slight_smile:

https://docs.uipath.com/activities/docs/excel-get-cell-color

On this basis you can get the text required :slight_smile:

1 Like
6

Hi bro @Shubham_Varshney

Sorry because make you confuse… I don’t want to extract base on cell color…

In the attached file, I highlighted color for details only… and the actual file, not color inside :slight_smile:

I want to extract data base on the text keyword…

7
  • set Counter to 1
  • for each loop, for each item in {Directory}
    • read range (hopefully you have the files named in number format for it to loop like Invoice_1, Invoice_2 and etc) u can set dynamic filenames.
    • for each row in {DataTable}
      • if {condition}
        • then add to {new Datatable}
        • else nothing
    • set counter + 1
  • write range
1 Like
8

Hi Bro @MythicGold

I understood but i want to extract text base on another text.

As below picture… I want to extract the number “100958379652” from “Số tờ khai”… because they are same line.

Because the row can be changed, so I want to extract text base on another text ( keyword )

image
image934×122 6.56 KB

9

Hi Bro @MythicGold

Sorry, could you pls support me a xaml file ?

Thanks in advance!

10

then i cant help you. this is not a webpage or pdf, this is excel, i cannot use anchor base find element to get text because excel is excel and it does not support dynamic anchor base.

there is no aaname, no name, no definite items i can use. ur only choice is either get ur excel to have a fixed format or otherwise this is beyond me.

11

Hi Bro @MythicGold

wa have another way without scrapt type ?

12

your only hope to extract data from that is to have a fixed format for ur excel file. otherwise, get ur excel into pdf format and that will be possible to extract based on keywords

13

We could not created a “row” variable and then it will run row by row all of excel files to check text and extract data which we want ?

14

u do know there is the save excel to pdf function right? you can just automate the process :confused:

15

No, because we have many invoices with xls.

I have make a xaml file as attached file… Could you pls check?

I want to check row by row in each file… if the row.tostring = “Số tờ khai” then it will extract row.
File.xaml (9.5 KB)