Comparing two DataTables with inconsistent values

[beepboop]

Hi there! Please help me if possible!

I’ll be comparing two datatables, the first extracted from an accounting software (ExtractedTable), the second from a CSV file (CSVTable). The problem is, because of how hacky the software is, the values to compare with the CSV file is inconsistent.

1. First, the software doesn’t limit how much you can type to enter the value for that column, but it limits the final character count. Thus, the value is often cut off until a certain amount. E.g: the full thing that was typed is “GENERIC BANK NAME HERE” but because of the limit, it would be “GENERIC BANK NAM”. So in ExtractedTable, I would get “GENERIC BANK NAM” even though in the CSVTable, it is “GENERIC BANK NAME HERE”.

I believe this is a problem because I cannot compare it properly since it doesn’t hold the exact same value with each other. I was hoping there is a way to get “GENERIC BANK NAM” and find the closest match and just compare it with that. I found this and don’t know if it will help: Novigo Solutions - Find Specific Value in Datatable - RPA Component | UiPath Marketplace

2. Furthermore, because of how transactions are dealt, there is no particular unique identifier to go off from. So the only way I can compare these is by comparing 2 or 3 columns’ values of extracted datatable with 2 or 3 columns’ values of CSV datatable. E.g:

For ExtractedTable, Column_3 and Column_4
compared with
For CSVTable, Description and Credit.

I don’t know if I can refer to the columns’ headers either because for ExtractedTable, it is generic “Column_1”, “Column_2”. On the other hand, CSVTable has proper headers but they start at row 8. As you can see in this picture.

Does anyone have a clue what I must do?

[Nithinkrishna]

Hey @beepboop

For Extracting the table what approach have you used here please ?

Thanks
#nK

[beepboop]

Hi @Nithinkrishna !

I’ve used Data Scraping, then Add New Row. If you want to look at the XAML file, here it is!

Main(Autosaved) (1).xaml (39.3 KB)

[jack.chan]

can you paste all the csv headers here? and do they directly match the headers of extractedDatatable?

[beepboop]

No, they do not match as you can see (I only copied the first two rows of entries):

From extractedDatatable:

Column-0	Column-1	Column-2	Column-3	Column-4	Column-5	Column-6	Column-7	Column-8
/ /	Text1	Text1	Text1	0	0	Check1	0	Text1
12/4/2022	Text1	Text1	HONG BANK	0	42263.86	Check1	0	12/4/2022

From CSVTable (this is copied straight from first row, but the first row is not the header):

Hong Bank / Hong Bank
From Date	1/3/2022
To Date	6/3/2022
Date	Transaction Description	Cheque No	Sender Name / Beneficiary Name	IR No.	Deposit	Withdrawal	Balance
	Balance from previous statement						42263.86
1/3/2022	Cr Adv - Interbank		BANK CREDIT CARD		40.79		42304.65

[jack.chan]

how does 2 rows look like in csv, can you post screenshot here like you did here

[beepboop]

CSVTable:
(removed)

ExtractedTable:
(removed)

[jack.chan]

ok, for column-3 do you know the max length of the column and is it consistent? e.g. GENERIC BANK NAM is 16 characters. does that it will only extract maximum of 16 characters for column3 every time?

[beepboop]

Yes it is consistent, I just checked! The actual thing will only show/give 25 characters for Column_3 every time, 16 was just on-the-fly example.

[jack.chan]

@beepboop

you can try this

for csv table, add new column “DescriptionAndCredit”
which is 1st 25 characters of description+credit

for extract datatable, add new column “Col3AndCol4” which is
column3 + column4

now we have 2 common columns between csv and extracted table and can perform lookup

main222.xaml (19.4 KB)

if you want to use this xaml, you have to convert your csv as xlsx first (save as xlsx) because your table starts at A8 and only xlsx read range activity has a parameter for starting cell address, the csv read activity starting cell is defaulted to A1

[beepboop]

@jack.chan ,
is it not possible to set the extracted datatable path as the datatable itself? This is continuation from the previous xaml you’ve seen me given you, so I already have the extracted table datatable

[jack.chan]

you can do that too,

but the csv still has to be converted to xlsx

[jack.chan]

i suggest using a separate sequence (with extractTablePath and CSVPath as arguments) to compare as its easier to unit test , if you put both in a single sequence then every time you test you you have to extract+compare

if they are in a separate sequence then you can test compare / extract separately

[beepboop]

yes I am doing so! I’ve also done this to convert the csv file to xlsx
this is what is written in the assign:
System.IO.Path.GetFileName(SelectedFile)+".xlsx"

Do you know a way I can make the extractedDt to be read as XLSX too?

Would writing it as CSV, then changing it back to XLSX be good enough or do you have a better idea? Because if I go that route, I’m figuring out how to dynamically rename it because this bot would be used multiple times

[jack.chan]

just write extractedDt to xlsx file

[beepboop]

I think I’m very stupid because I can’t seem to get it right on what to do

BankRecon.xaml (57.1 KB)

I’ve labelled the sequences too, it’s in Convert to XLSX. I did the ConvFile because I don’t know how I can have it named in a way that it can be run many times without having to think of what file to name it as. Or do I just put a folder there? (For CSVTable)

[jack.chan]

my bad, you dont have to convert it to xlsx…

but when you use read range the sheetName must = Path.GetFileNameWithoutExtension(SelectedFile)