my task is to compare between 2 files from data validation and business rules applies or not...

8/3/2019 My Task is to Compare Between 2 Files From Data Validation and Business Rules Applies or Not Perspective

1/6

Approach to automate data validation and business rules between files

Description:

My task is to compare between 2 files from data validation and business rules applies or not

perspective. To be specify in more details below

The flow is:

That there is an origin excel csv file (txt file with format file extension of .csv)

This file is being processed via an enging- data feeder

This process creating an xml text string into a cell in the database

With a simple sql command, this xml string being copied into an xml txt file

Then the idea is to compare and add some more calculations

The idea is to do it against different names of origin excel files

(maybe we need to parse the xml file and to store the details of each columns per each row by using

split vb command and then save them in varibales in order to check format-type and length of the

xml txt inside the elements)

The origin file is a flat txt file with CSV as the extension format:

Sample:

This file being transferred through an engine (data feeder importer)

And then being generated into a cell field in a MS SQL DB as xml scheme

So the output file is a XML FILE

--------------------------------------------------------------------------------------------------------------------------------------

To prepare a code in VB script that with QTP, can do the next things:

1. Will load/read/import the data from the origin EXCEL CSV FILE into QTP, into the a specific

debatable sheet, and then load the xml output file into another datatable, and then create

code that will compare them and report where they are not matching.

Cab be done in many different ways

To import the csv file into qtp,, then to import the output xml file, to conver it back to csv

file, and then compare them.

HDR 4 ######## PPR 2 F

PPR 4 ABL Adelaide Bank - CMTUnknown Unknown

EXT 4 ABL 50118

IPD 4 ABL 50118 CCL Property TUP Unknown T

IPP 4 ABL 50118 CCL 3.6456 ########

ADV 4 ABL 50118 50118 Loxton Nick Unknown Unknown

CLI 4 ABL 50118 50118 40751352 HOMBSCHDENISE Mrs Female ######## Unknown

CLI 4 ABL 50118 50118 40674421 AUST HENRY Mr Male ######## Unknown

CLI 4 ABL 50118 50118 70872910 AMOS JOHN Mr Male ######## Unknown


2/6

To import csv file to run time datatable sheet1, then to import xml outoutfile to a runtime

datatable sheet 2, and to compare them

---------------------------------------------------------------------------------------------------------------------------

-

This is a code that support the above and is working- just to confirm that both csv files areequal or not

Its still missing to highlight or report where there is a mismatch

Dim xmlFile, csvFile

xmlFile="C:\Users\Aviad.SarShalom\Desktop\origing and output file\outputXMLfile.xml"

csvFile="C:\Users\Aviad.SarShalom\Desktop\origing and output file\originfileCSVversion

4.0.csv"

Set xlsApp = CreateObject("Excel.Application")

Set xlswb = xlsApp.Workbooks.OpenXML(xmlFile,2)

xlswb.SaveAs csvFile, 6

xlswb.close false

xlsApp.quit

TrueOrFalse = "Same"

DataTable.Import("C:\Users\Aviad.SarShalom\Desktop\origing and output

file\originfileCSVversion 4.0.csv")

FirstTableCount = DataTable.GetRowCount

DataTable.Import(csvFile)

SecondTableCount = DataTable.GetRowCount

if (FirstTableCount SecondTableCount) then

msgbox "Both files are not Equal"

else

For I = 1 To FirstTableCount

DataTable.Import("C:\Users\Aviad.SarShalom\Desktop\origing and output

file\originfileCSVversion 4.0.csv")

Datatable.SetCurrentRow(I)

value1 = DataTable.Value(1)

MsgBox value1

DataTable.Import(csvFile)

Datatable.SetCurrentRow(I)value2 = DataTable.Value(1)

MsgBox value2

if(value1 value2) then

TrueOrFalse = "Different"

Exit For

end if

Next


3/6

end if

msgbox(TrueOrFalse)

-----------------------------------------------------------------------------------------------------------

2. Code VBScript to validate data, specifically :

-Dates

-Format (Alphanumeric, numeric)

-To check versus xml file if the elements tag that store the text, are consider being mandatory

or optional based on the EPI 4.0 each record structure rules (Advisor, Clients etc)

Test data structure of csv file and business rules

This is an explanation about the Structure of the CSV file-

Adviser

The Adviser record contains details about an adviser and the organisation for which they areassociated. An Adviser record must exist if the adviser (i.e. Adviser ID) is referenced in

another record type.

Field Name Format Mandatory /Optional

Description

Record Type A (3) Mandatory Abbreviation used to define record type - ADV

EPI Version Number A (10) Mandatory EPI Version - 4.0

EPI Platform Provider ID A (4) Mandatory A unique identifier for the Platform Provider. This field is

supplied to the Platform Provider by the External PlatformInterface custodians.

Extract ID A (60) Mandatory Platform Providers identifier for the group of advisers extractedin the file (as referenced in Extract Identification record).

Adviser ID A (60) Mandatory Platform Providers unique identifier for the Adviser.

FPS Adviser ID A (36) Optional Financial Planning Software identifier for Adviser.

Adviser APIR Code A (9) Optional APIR Participant Code for Adviser.

Format: AA99999AA

Last Name A (40) Mandatory Advisers last name as registered with Platform Provider.

First Name A (40) Mandatory Advisers first name as registered with Platform Provider.

Title A (15) Optional Advisers title.

Allowable values are:

y Mr

y Mrs

y Ms

y

Missy Dr

y Prof

y Sir

y Lady

y Father

y Rev

y Other.

Gender A (7) Mandatory Advisers Gender.


y Male


4/6

Field Name Format Mandatory /Optional

Description

y Female

y Unknown.

Business Name A (60) Optional Registered Name of business for which the adviser isassociated.

Organisation APIR Code A (9) Optional APIR Participant Code for organisation that the adviser isassociated.

Format: AAAA999AA

Branch Name A (60) Optional Branch name for which the adviser is associated.

A.B.N A (11) Optional Advisers Australian Business Number.

A.C.N / A.R.B.N A (11) Optional Australian Company Number / Australian Registered BodyNumber.

Preferred Contact Method A (10) Mandatory The preferred contact method for the Adviser.


y Address

y Phone

y Email

y

Weby Unknown.

Nominated contact method must have a correspondingContact Details record, unless method is Unknown.

An example to the file itself with just advisor table

HDR 4 24/04

PPR 4 AON

EXT 4 AON

ADV 4 AON

TRL 4


5/6

HDR,4,24/04/2010 9:03,PPR,1,F,,,,,,,,,,,

PPR,4,AON,Aon Consulting Pty

Ltd,APIR123AU,Address,Address,,,,,,,,,,

EXT,4,AON,Extract123,,,,,,,,,,,,,

ADV,4,AON,Extract123,Adviser-AON,VL-Adviser-

AON,4.2631E+12,Wallace,William,MR,Male,AON FINANCIAL

PLANNING AND PROTECTION,APIR123AU,Desk

95,53078119212,53078000000,Email

TRL,4,3,,,,,,,,,,,,,,


6/6

csv file

AON_20100424_Extr

act123_003.csv

using sql with the below command

(I just need to insert into an input box what is the advisor code numer)

select * from AdvisorDetails

where AdvisorCode='Adviser-AON'

and it will generate the below xml file string in the data base

outputXMLfile.xml

my task is to compare between 2 files from data validation and business rules applies or not...

Documents