Often there is a need to compare data in Excel file to data from other data sources such as a relational database. This can done using ETL Validator's Component Test Case.
For the purposes of this post, I picked a simple Excel file with two sheets as shown below:
Step1 : Create an Excel/Flat File Data Source and place the Excel File in that directory.
Step2 : Create a Component Test Case and add a Flat File Component. Select the Data Source and Excel file that you want to compare. In the properties step of the Flat File component, enter the Sheet name as shown below. Navigate to the Columns step and load the excel data:
Step 3 : Add a new DB Component and Mapping Compare Component as shown below so that data from the Excel file can be compared with the data from the DB Component
Step 4 : Add a Table Validation to the Mapping Compare Component. Convert data types as needed:
Common Excel data type conversion issues:
- Dates from Excel are shown in a string format. We can convert these to Date format in the Table Validation as shown below:
- Large numbers are shown in the Scientific Notation. We can convert these numbers to Float as shown below:
