Excel data query compr

Hi, My requirement is, I have two folder
Ex. PO and Tax
I have 2 files in the PO folder and same files I have in the Tax files with same data and file name
PO Folder: image
Tax Folder: image

I want to compare the data suppose first I am processing the United Food I want to bot pic up the United Food from Tax Folder and compare the data. I will show you the format of the file.
This is my data.
image
I want to compare it headerwise. Like customerName if same in the Tax folder for united food

If data is match then I want to write a status in the UpdateStaus excel. Yes/No

@Palaniyappan @Lahiru.Fernando kindly help
@lakshman @indra

2 Likes

A query, each file has only one row of data?

1 Like

Hi @balkishan

Use row(“CustomerName”).ToString.equals(row(“CustomerName”).ToString

row(“Status”).ToString=“Pass”

Check this

Thanks
Ashwin S

1 Like

Yes bro only one row. Just I want to compare this all. If matched the write in the another excel Match/Unmatch

1 Like

Both are having the same amount of columns as well right?

1 Like

is it sufficient,
Bro suppose I have these two files in array. Lets say first I read the United Food files. Then I want to read the United Food files from Tax Folder. Okay
Then I want to match the data if both has same data then write into another excel. Hope you understand the requirements.

2 Likes

Yes bro. same amount of column even same file name. Just I want to compare the data, suppose my Customer Name is not matching and except this all data matching then I have write like cutomer name not matching.

1 Like

ohhh, now I get it!! this will require a lot of comparing!!!

if you want both sheets aren’t matching we can do it by converting both to a data table and do a “Full Join”, if the resulting count of data table is >1 that mean’s it’s not matching

else Match

Now once that’s done, what’s not matching would be a trial hit method after that!!!

A few looping and overwriting may lead you to mapping how that could be done!!!
hope this helps :slight_smile:

Cheers

1 Like

Fine
—hope these steps would help you
—let’s take two strings like one fore PO Folder and another for TAX Folder
First string is
“C:/Yourfolderpath/in_variable/UnitedFoods.xlsx”
Second string is
“C:/Yourfolderpath/in_variable/Al Gurair Foods.xlsx”

—now our in_variable can be assigned as either PO or Tax which is the folder name

—now let’s assign as Tax and so the get the two paths
In_firstpath = “C:/Yourfolderpath/PO/UnitedFoods.xlsx”

In_secondpath = “C:/Yourfolderpath/Tax/UnitedFoods.xlsx”

—use a excel application scope and mention the first path and use a read range and get the datatable as dt1
—use a excel application scope and mention the second path and use a read range and get the datatable as dt2

—now create a datatable with build datatable with same set of columns but with extra column status and get the output as Finaldt

—now use for each row and lass the first datatble dt1 as input
Inside the loop use a if condition and mention like this
row(“yourcolumnname”).ToString.Equals(dt2.Rows(dt1.Rows.IndexOf(row)(“yourcolumnname”))

—if true it will go to true part where we can use a add data row and in the array row property mention as {row(0),row(1),row(2),…,”Yes”}
And in the datatable mention as Finaldt

That’s all you are done
Hope this would help you
Cheers @balkishan

2 Likes

Not lot of bro. Only I have five columns and one row data.
Just I have to compare this.

image

Same data I have into another excel also.

Do a “Full Join” for starter’s that will cut the comparing on a huge scale.

now once a “full Join” is done, it basically will copy both the data on the merged datatable and a simple compare logic will help you out :slight_smile:

Can you do a bit of “full Join” and notice it’s behaviour ?

1 Like

I you don’t mind can you write the steps.

The reason I am asking you to do this is, it uses the default activities and the process is way faster if you are doing a huge comparison of files!!!

Let’s assume if this works perfectly, not sure if you will get multiple rows or additional columns :slight_smile:

1 Like

Well if we have same set of columns then we can go for Join datatable activity
Were we can pass the two datatble variable and get the output with a new datatable variable that can be written to a excel we want
same steps except this

For more details

Cheers @balkishan

This part not getting bro.
As I am performing a simple operation. Like stored all files in an array then use that array element and pass into the For Each loop then use the Excel Scope and Read the first file and store this data into POdt variable. I am doing this for PO Folder.
Lets say United Food files data is stored in POdt variable. Now I want to read the United Food files from the Tax Folder and do the comparison.

Always I will get same column bro. As I shown you in the excel also.

Yeah but in full merge you will be able to get a proper data set of compare!!!
Try it out once

1 Like

But I read this, it showing me If DataTable2 has same columns name in DataTable1 the it will change the name of the column in the new DataTable ?

I think I have to do Full Join Right ???

1 Like

Only when they aren’t matching :smiley:

and yes Full Join only :dancer:

1 Like

Fine this looks good either
Let’s do one thing
—use a assign activity and mention as
“In_TAXFilepath = “yourfolderpath/“
—let’s get the array of files from a folder say for PO alone now
—name that array variable as out_POfiles
—now use a for each loop and pass the above variable as input and inside the loop use. Excel application scope and pass as item as input
—inside that scope use a read range and get the output with a variable of datatable named dt1
—while still being inside the for each loop now use another excel application scope next to this scope and mention as
In_TAXFilepath+Split(item,”/“)(Split(item,”/“).Length-1)
Where we will be getting the output as dt2
I would prefer to go for these steps because you want to make an update with a new column

Cheers @balkishan