How to compare 2 excel file and store data into another file

Help Forum
, , ,
1

Hi all,
If 2 excel files are having same headers the files should compare and append the data to 2nd file.
image
test1 file
image

test2 file
Output should be stored in test2 file
image
Expected output
How to do this. Please guide me on this.
test1.xlsx (10.9 KB)
test2.xlsx (9.6 KB)

2

Hi @lakshmi.mp

You can use the Append Range workbook activity to append the data of test1 file to test2 file.

Follow the below steps for better understanding,
→ Use Read Range workbook activity to read the test1 file and store in a datatable called dt.
→ Then use the Append Range workbook activity to append the dt to the test2 excel file.

Hope it helps!!

3

@mkankatala , ya but how to compare columns from 2 different excels. Please check the sample files

4

Okay @lakshmi.mp

In this case it’s better to use the LINQ Expression and append range workbook.

Check the below steps,
→ Use Read Range workbook activity to read the test1 excel and store in a datatable called DT1.
→ Use another Read Range workbook activity to read the test2 excel and store in other datatable called DT2.
→ Then use the assign activity to write the below LINQ Expression, create a datatable datatype variable called Output_dt.

- Assign -> Output_dt = (From row In DT1.AsEnumerable 
                         Select DT2.Clone.LoadDataRow({row("City").ToString,row("Keywords"),row("Min Experience"),"",row("Max Experience")}, False)
                            	).CopyToDataTable()

→ After assign activity use the Append Range workbook activity to append the Output_dt to file2 file.

Check the below workflow for better understanding,
DateTime_Practice.xaml (13.6 KB)

DateTime_Practice
DateTime_Practice826×619 48.2 KB

Output Excel file-
test2.xlsx (9.1 KB)

Hope it helps!!

5

@mkankatala , in my actual file the headers are jumbled. Not on this manner. Keywords header is present in “j” column but its updating in column B.

6

Hi @lakshmi.mp

Try below approach to first check the header then add to DT2.

  1. Read both Excel - Here I have build two DataTable DT1 & DT2
  2. Take IF activity and use below expression to compare both DT has same header or not.

DT1.Columns.Cast(Of DataColumn)().Select(Function(c) c.ColumnName).OrderBy(Function(x) x).SequenceEqual(DT2.Columns.Cast(Of DataColumn)().Select(Function(c) c.ColumnName).OrderBy(Function(x) x))

  1. If TRUE, then you can use Append Range Activity or Merge Data Table Activity

image
image1440×880 35.3 KB

Hope it’ll helps you :slight_smile:
Cheers!!

7

@Nawazish_Ahmad , can you explain more on if condition…

8

Could you share the headers of two files then I’ll change the LINQ Expression according to the data… @lakshmi.mp

Hope you understand!!

9

test1.xlsx (11.9 KB)
test2.xlsx (10.0 KB)
Hi @mkankatala @Nawazish_Ahmad , please check the updated sample excel file. Need to match the columns and update in test2 file.

10

@lakshmi.mp

  • It’ll check the Headers are same or not in Both the DataTable
  • If the Headers sequence are change then also it’ll works

Then Use Merge Data Table Activity to append the data in the correct column.

Hope you understand :slight_smile:
Happy Automation :uipath:

11

Hi @lakshmi.mp

Follow the same process steps in the above reply, just change the LINQ Expression as below,

- Assign -> dt_Output = (From row In DT1.AsEnumerable 
                         Select DT2.Clone.Rows.Add({"","","","","","","","", row("Keywords"),"","","", row("Min Experience"), row("Max Experience"), row("Current Location of Candidate").ToString,"","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""}) 
                             	).CopyToDataTable()

I modified the Expression based on your column structure.

Check the below output file,
test2.xlsx (10.8 KB)

Hope it helps!!

1 Like
12

@mkankatala @Nawazish_Ahmad , able to compare and update the excel. Thank you.

13

Yes you can do that by using LINQ Expressions… @lakshmi.mp

Happy Automation!!

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.