Reading specific fields from Excel and uploading to Orchestrator Queue

Help Studio
, ,
1

Hi,

I would like to know what’s the most ideal way to read specific fields from excel and upload them to orchestrator queue. Knowing that I’ll be looping over several excel files so it’s fields won’t be in same position always…

2

Hi @MHarakeh

If column names are constant then use them to find the columns and add the queue items

Once you read into datatable you can access column either using index as 0,1,2,3… or by using columnnames

cheers

3

Hi @Anil_G

Column names aren’t constant, Example in one PDF column names are different than the others…
And I only need 3 specific fields from a column, read them and then upload them to orchestrator queue

4

Hi @MHarakeh ,

The 3 columns that you need are having constant name? If yes then also you can use names as other columns can be anything

Cheers

5

Hey @Anil_G,

The fields I have to pick aren’t column based or row, but they are at the end of the excel sheet and have fixed names next to them, example: Subtotal: 1000
VAT: 8%
Total: 1080

Do you think using anchor in this case is the best ?

When trying to use anchor and use find element inside it to select the text Example Subtotal
am getting the following error:
[Exception Object reference not set to an instance of an object]

6

Hi @MHarakeh

No.on excel it is never good to use ui automation. Read the excel into datatable amd use lookup to look up for sub total/vat/total and get the value beside it

Cheers

7

I can get them from PDF before reading my PDF into excel, if from pdf it’s easier…!
But as I said before I tried anchor in PDF and used find element it’s giving this error
" [Exception Object reference not set to an instance of an object]"

8

If from pdf then read pdf and then use split left/right string activity to split your string and get the required values

Cheers

9

But that’s supposed to split fields in single column or that are separated by a character…

This an example of what am talking about…
PDF: here in PDF I thought anchor would be the best idea but Idk why it’s not working,… When I try to select the element I need to find it throws an error
image

Excel: but here in excel I can;t decide on column name
image

10

Hi @MHarakeh

to get sub Total you just need to do this say your pdf text is in str string then str.Split(“Sub Total”,2,StringSPlitOptions.None)(1).Trim.Split(Environment.Newline,2,StringSPlitOptions.None)(0).Trim

if Environment.Newline does not work use vbCrLf

If Newline is not there then for sure you will have a space you can split by " "(Space)

repeat same for others

cheers

11

My PDF is being scrapped into a datatable will that work in such case…

Plus I managed to use anchor and get element then get full text… but the get full text is always getting value 0 although it’s assigned to pick the value of Subtotal !!

12

Hi @MHarakeh

Using ui for pdf itself is not adviced that too anchor base not very good.

In the datatable how are you seeing this ? Is subtotal and the value are there in same cell or different?? If same use split if different then use look up and get the value …you need to first decide what you want to use and from where and how the data is

You have all the options now …its upto you to choose which best suites for you

Cheers

13

Hello @MHarakeh

Do you need to upload the data from excel file or from pdf file? The topic and the discussion is little confusing.

Can you please share screenshot of 2 sample files and explain the requirement for better understanding of your use case.

thanks

14

Hi @Anil_G

this is how data is showing id DT
image

Your support is much appreciated !!

15

Hi @Rahul_Unnikrishnan,

I have the data in PDF am scraping into an excel so the fields I want to pick in order to upload are scraped from PDF along with more data and then saved into DT to create an excel file…

16

Before writing to excel, you will have to remove the unwanted data .

Plz share screenshot of one rough excel.

Thanks

17

All the data that is scraped into excel is needed… But from the scraped data I need to upload 3 specific fields to orchestrator

18

Hi @MHarakeh

To get the values use filter datatatanle to filter for sub total and then get the last column from the resultant dt. Repeat for all values you need

As the data is in last two columns. Use dt.columns.count to know the last column and subtract to get the last but one column….use these indexes in filter data table and getting the value

Cheers

19

@Anil_G what am not getting is that those fields position might change it’s not always under the same column…
Got my point ?

20

Hi @MHarakeh

Yes. The field position might change but will they be the last two columns always?

If not then add a for loop and loop through all the columns by specifying list of items property as dt.Columns and type argument as datacolumn and add a filter datatable inside and in column pass item.ColumnName and filter for sub total…let the output datatable be out_dt and now add a if condition to check if the filter datatable has returned any row or not out_dt.rowcount >0 will go into if condition. In the true side to get the value add a assign activity and out_dt.rows(0)(out_dt.Columns(item.ColumnName.Ordinal+1)).

Similarly add filter and if for other values like total and vat as well

Cheers