How to Compare Flat File with Table?
In this article, we will explore the comparison between a flat file and a database table using the reconciliation feature in iceDQ. We will learn how to create a reconciliation rule and verify data consistency between the source flat file and the target database table.
Steps
Below steps have been followed in the video.
Creating a Reconciliation Rule
To start, select a folder in iceDQ and create a new reconciliation rule. Give the rule an appropriate name.
Setting Up Source
Choose the connection type as "File" and the connection as "Flat File Native". Set the file type as "Delimited" and select the desired file, in this case, "customer.csv". Preview the data, and make necessary configurations, like skipping the header row and handling null values.
Setting Up Target
Choose the connection type as "Database" and the connection as "AdventureWorks2019". Select the schema as "dbo" and the table name as "Customer". Preview the data in the target to ensure consistency with the source file.
Diffjoin and Mapping Columns
Set up Diffjoin and map columns with auto-mapping by column name. Pin the primary key "CustomerID" and add the rest of the columns as checks.
Handling Data Type Discrepancies
Observe data type discrepancies between the source and target, such as "customertype" and "Date Of Birth". Use the ".tostring()" function to convert data types and ensure consistency between the source and target.
Publish and Run
Publish the rule and execute it to compare the data in the flat file and the database table.
Reviewing the Results
After execution, review the results to identify any discrepancies between the source and target data. Investigate further by clicking on the instance ID to view specific mismatches.
Video: How to Compare Flat File with Table?
Conclusion
By following this step-by-step guide, we successfully compared data from the flat file "Customers.csv" with the database table "Customer" using iceDQ's reconciliation feature. This process ensures data consistency and accuracy, helping organizations maintain data integrity and make informed decisions. Thank you for reading!