Day in Day out while testing we do a comparison of these files which could be very complex and contain a large number of key-value pairs (out of order). It is a repetitive task and is very time consuming.
Reconciliation of Data present in XML's / JSON / CSV.
Previously the comparison was being done manually. Here we have built a reusable UIPath Activity.
- Cost Saving and time saving (XML with 10M records processing in under 3 minutes)
- Reporting
- Reusability and open to use for all
-
In a bank, a trade booking system is being migrated on to a new version and you need to compare/test the publication of trades. You need to be sure that all the attributes being sent are present and correctly mapped.
-
E-Commerce exchanges their data using XML with different applications and services. Hence the schema of the data changes. This tool can be used to validate the data before the data gets consumed.
User needs to drop an email with the location of the file which needs to be compared. BOT would be polling the inbox and would trigger the process which will read the file and do a comparion. In case there are mismatches it will reply back on the same email with an html report highlighting the gaps. If there are no discrepancies encountered a success mail would be sent out.
- The tool monitors the number of unread emails with subject containing the word compare
- Picks the files contained in the email compares the source and target files
- Source and Target files are specified by the user sending the email
- Creates a result file after reconciliation
- User gets back and email with the comparison result
- The tool handles malformed files
- The tool can handle cases where the order of keys is not the same across the two files
- It can handle cases where the key names are not same across files. This is achieved using a mapping file supplied by the user
- If source or target is not present the comparison fails
- In case some different files format is provided, a failure mail is sent
- The report renders as an HTML, hence making it available anywhere
- The report provides advance search and filters to narrow down results
- .Net 4.6.1
- UIPath Studio 2019.7.0
- UIPath Robot
- Configure the user email in the Activity
- Publish the activity
- Go to UIPath platform and run the job using the name ReconActivity
Measureing the quality of data is one imporant step, data quality is measured on the following parameters
- Accuracy : Ratio of the data to errors, (there is no industry standard, depends on the size and nature of the data)
- Consistency checks: Making sure that the data is consistent across the two files, i.e. sum of prices of products in the two files is same
- Completeness: The data is complete
- Timeliness: The event of file process comparison can be automatically triggered, the system will monitor such events and triggered if the events deviate from the regular cadence
- Common error patterns will be recognised and the system owners will be notified of the same to rectify any problems in the upstream data processing. Common patterns might include: a) The cadence of the comparison event b) The pattern of the
- Descriptive analytics on the error logs provide a overall picture
- Integration with GCP,Azure for storing and reading the data on storage services (WIP)
- Integration with other popular file formats like protobuf, yml etc
- Maintaining record of all the comparisons in a NoSQL db
- Self containing runs