FlatFile to Database Table comparison

One Star

FlatFile to Database Table comparison

Hi,
I am new to Talend. Actually just downloaded it today and started looking in to it.
What I would like to know is if it is possible to do a flat file to database table comparison using Talend DI. I want to be able to compare the flat file and the db table row by row and would like it to point out any difference in any row/column. Is this possible? If yes, please let me know how.
Moderator

Re: FlatFile to Database Table comparison

Hi,
What I would like to know is if it is possible to do a flat file to database table comparison using Talend DI. I want to be able to compare the flat file and the db table row by row and would like it to point out any difference in any row/column

For your requirement, you should use: tFileInputDelimited-->tMap-->tFileOutDelimited
t<DB>Input-->lookup-->
In tMap, make inner join for your comparison source(a flat file and table) and set the Catch lookup inner join reject value as "true". The output result should be difference/changed data.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: FlatFile to Database Table comparison

Thanks Sabrina for the reply. I tried it and it seems to work. Attached below are the screenshots. I just wanted to check with you if we need to link every column in the main and lookup tables between which we want data differences to be identified. If columns are not linked then the data differences between them wont be captured in the reject is it? Please clarify.

Also, is there a way in DI where in i can generate a comparison report whih says how much % of data between 2 tables match? I did a bit of research and found out that i can do this in DQ. But, I wasnt able to find a way to import this job from DI in to DQ or create a new job in DQ for comparing flat file to table comparison and then generate a report out of it. Please help.
Regards,
Aks
One Star

Re: FlatFile to Database Table comparison

For some weird reason i am not able to upload screenshots. I am logged in with my username, but still its considering me as guest.
Moderator

Re: FlatFile to Database Table comparison

Hi,
For your login issue, have you checked forum 29406NOTICE TO ALL TALENDFORGE USERS: maintenance completed, please let me know if it is working with you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: FlatFile to Database Table comparison

Thanks Sabrina. I tried logging in on firefox and it worked.
I tried the solution you posted for my query for flat file to db table comparison and it seems to work. Attached are the screenshots. I just tried with 3 columns, in actual my table has more than 40 columns. What i wanted to confirm with you is that if i want any differences between flat file and db table to be caught, i need to link each of the 40 columns in the main and lookup right? If any of the columns are not linked then the data differences between them wont be captured in the reject. Correct? Please clarify.

Also, is there a way in DI where in i can generate a comparison report whih says how much % of data between 2 tables match? I did a bit of research and found out that i can do this in DQ. But, I wasnt able to find a way to import this job from DI in to DQ or create a new job in DQ for comparing flat file to table comparison and then generate a report out of it. Please help.
Regards,
Aks
Moderator

Re: FlatFile to Database Table comparison

Hi,
What i wanted to confirm with you is that if i want any differences between flat file and db table to be caught, i need to link each of the 40 columns in the main and lookup right? If any of the columns are not linked then the data differences between them wont be captured in the reject. Correct? Please clarify.

If you want any differences between flat file and db table to be caught, you have to link all columns in table and all column in flat file.
Also, is there a way in DI where in i can generate a comparison report whih says how much % of data between 2 tables match? I did a bit of research and found out that i can do this in DQ. But, I wasnt able to find a way to import this job from DI in to DQ or create a new job in DQ for comparing flat file to table comparison and then generate a report out of it

.
There is no Analysis Results in Talend Data Integration Product.
For Talend Data Quality Product, it is impossible export DI job into DQ. You should load data of flat file into a table and then make comparing between two difference tables(DB table, FlatFile).
See the online document TalendHelpCenter:Comparing identical columns in different tables. Does this Analysis Results satisfy your needs?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.