Compare 2 excel file data

Get Help & Discuss Process Studio
1

Excel1 - FIrefNo_CC
Excel2 - FI Reference Number
Search excel2 with FIrefNo_CC, if matched validate TotalFunding vs CR amount is matched. Matched data will available in 1st row or 100th row. If data avail & matched, Output with MatchStatus

Pls suggest the workflow method to perform

image
image1997×514 52 KB

image
image975×828 48.8 KB

2

Hi @sManikandansampath,
Are you using any of our cloud instances(T3/T4/OnDemand)? If not, which version of AutomationEdge are you using? I need these details to send you a sample workflow

3

Currently Using T3.

  1. Tried with StreamLookup and received the output with extra decimal. Excel value for the amount column data type is number, ie., value 22.33 was received as 22.32888.Whether we need to change the data type from number into BigNumber…?Share me the reference workflow with numeric value concept.

  2. Search and select the dynamic file name inside the Input folder like “ CC.xlsx” .
    normally available “Axis CC 29June2019.xlsx”. next time “Axis CC 30July2019.xlsx”.

4

Hello,

As per your earlier requirement, I have designed a sample project . It has two dummy input files, an sample output file and the actual workflow for you to run it. Please import it as a Project in Process Studio and run it.

Comparing Excels.zip (21.1 KB)

For this, you can choose datat type as number, format as #.# and precision 2, that should help you

Use Regex along with your required directory:

image
image1173×726 21.9 KB

Hope this helps.

5

Dear Hemanshu,

More thanks for your support…

  1. Failed data, should be written in same excel with sheet2 name as “Mismatch” - Success (MS Excel Writer- Sheet Name)
  2. Failed data, should be written in same excel in sheet2 - Success (MS Excel Writer)
  3. If no Failed data, should not written in same excel(including Header details) in sheet2 - Fail . Its written the Header value. Tried with Filter row, If CC value available then only should write in sheet 2. But written the header value.
  4. Failed data row alone, should be written in red color. if used in same excel, need to written the unmatch data row in different color.

Autosearch and read -
“Get File Names” - Reg Exp (.*.) Received all files. Using MJS check the file name is available or not, instead of directly using Excel Input. Bcoz, if excel not available, will receive the error message. Correct me if am wrong.

If possible also share me the sample workflow of another one request Read mail from inbox