Data Scraping Software's Table, Compare Data Tables and Add New Column into the Same CSV File

Help Studio
, , , , ,
1

Hello! If anyone is willing to guide/help me, it is much appreciated! I am tasked to compare the table from a Windows accounting software with a CSV file. I was able to do it up until the part where I have to compare it. This is because of a few problems:

  1. The CSV’s header at row 8 instead of 1. Whereas the data starts at row 9.
  2. The window that is showcasing the table cannot be maximised or anything of the sorts so I can’t use Data Scraping and get the full table.

What I want to achieve:

FOR DEBIT (loop)
(cvs) find sender name, look at deposit

(accounting app) find description, then to debit

IF CAN LOCATE = tick MARK (accounting app), write YES
ELSE IF CANNOT LOCATE = write NO

FOR CREDIT (loop)
same thing but find withdrawal instead of deposit and credit instead of debit

THUS, is it possible to:

  1. Data Scrape whole table in accounting software.
  2. Compare Data Table from CSV File with Data Table from accounting software.
  3. Checking each row, if found data in both, write “True” in CSV at the end of row and tick the checkbox in accounting software.
  4. Else (cannot locate the row of CSV Data Table in Data Table of accounting software), write “False” in CSV.

Please help if possible! It seems I’m stuck on how to do it! I’m doing an internship and this is to automate bank reconciliation statement of the company I’m working at.

2

1.Are you able to view the table? the i don’t think there will be an issue with scraping. Or if data scraping doesn’t work use table column and row values fetch the desired values for comparison
2.use lookup range activity to get the cell index and use write cell activity to write in appropriate cell

1 Like
3

Hi @Lak_Ui !

  1. Yes, I am able to view the table in the accounting software. But it will show up like this:

This is the default view (what you see as soon as you open the window)

image
image902×306 47.5 KB

This is the view after I’ve scrolled down/pressed page down or Down key.

image
image925×318 27.2 KB

When I use Data Scraping on the default view, it only shows those rows. When I use it after I have scrolled the table (to see the remaining rows), in this case it only shows those 3 rows.

If I am to do as you suggested, how would I do that? Can you give an example?

  1. Can I use Excel’s Activities for CSV? As far as I’m aware you can’t use it unless I got it all wrong. I have to strictly use CSV files.
4

so you are able to detect the elements in this pop up using ui explorer so if you see the attributes in ui explorer you can see tableCol and TableRow…You can use a for or while loop to loop through each of the elements by incrementing these 2 attributes

you can convert csv to excel and use excel activities

1 Like
5

Hi @beepboop ,

We should Check at First, If we can get all the Row Data Properly from the Table.

As you have already Tried Data Scraping, the rows which aren’t visible doesn’t get extracted in the Output.

So, Could you Try using Find Children Activity and Check if you are able to get the Correct Row Count from it?

Also, Do you happen to know what would be the Maximum Rows that could be present in the Table or it it Dynamic ?

1 Like
6

Can you explain further on what do you mean?

7

Hi @supermanPunch !
I will be trying that and get back to you. I’ve never used Find Children and I don’t know where to start so it might take awhile until I can show you I’ve gotten results. If you think you can point me in the right direction, I’ll appreciate it a lot!

Yes, it is dynamic! It changes depending on the bank transactions done that month.

8

@beepboop ,

You would Require to use UiExplorer for efficient Configuration. The UiExplorer should be available in the Design Ribbon.

image

Maybe Try Following the Below Steps :

  1. Use Find Children Activity, Indicate one of the rows.

  2. From the Properties of Find Children Activity, Click on Edit Selector → Open In UiExplorer.

  3. UiExplorer is Opened. If The Element isn’t validated yet, Click on Indicate Element and indicate the row element again.

  4. Now, In UiExplorer, the Visual Tree should appear somewhat like below. Meaning there is a Top Element under which the Sub Elements would be Present. We would need to Select the Top Element as Highlighted, it might not be the same Tag.
    image

  5. Next, After Selecting the Top Element, Save the Selector which should be modified in the Find Children Selector Property as well. Create the Output for Find Children Activity.

  6. Next, Execute and Check if the Count of the Find Children result is the same as that of the Rows Present in Application.

This is all just to Confirm whether we are able to retrieve all row elements.

1 Like
9

@supermanPunch Oh god I don’t know how I completely missed that, thank you so much!

I’ve done as you said, but this is my result instead:

image
image236×560 7.86 KB

the Grouping is where the table headers are.
In this scenario, does this mean the Top Element would be Table View 1?

I used Write Line to check it it but it didn’t list out on Output, might be my own fault. But I think it doesn’t work because when I counted manually, there are 11 rows but my UI Explorer only saw 10.