topic validate data in a csv file before insertion in database in Talend Studio

validate data in a csv file before insertion in database

Anonymous — Sat, 16 Nov 2024 13:11:12 GMT

Hello,
here my needs :
I have a csv file containing data. I want to insert or update this data in a postgresql database but I need to validate some condition before :
the column number
the column names
the corresponding column type
at least 1 line of data
If one of this conditions are missing. I have to stop the job.
So I create a sub job :
tFileInputDelimited link to a tSchemaComplianceCheck
the ifileInputDelimited Schema is a generic schema corresponding to my csvFile
I my main job :
I link a tFileInpuDelimited to my sub job.
On error I send a tDie.
On success I use a tMap to send row to my database.
On reject from my database I use another tmap to send line and errors in a reject file.
Here my problems :
my sub job send error only where column type are wrong.
if a csv file line contains least data that it should, empty data are inserted instead of genereting a reject

Thanks for your help.

Re: validate data in a csv file before insertion in database

alevy — Wed, 08 Dec 2010 22:25:09 GMT

I think what you want is to check the advanced option on the tFileInputDelimited "Check each row structure against schema". If not, put up a screenprint with more detail of exactly what the problem is.

Re: validate data in a csv file before insertion in database

Anonymous — Fri, 10 Dec 2010 08:47:17 GMT

Indeed, that is what I was looking for.
Thank you.
I have another question :
How could I now the current line number when I'm reading a file ?
I need It to put it in a reject file when my filterRow reject data.

Re: validate data in a csv file before insertion in database

alevy — Sun, 12 Dec 2010 22:07:04 GMT

Best would probably be to have a tJavaRow or tMap after tFileInputDelimited, in which you can add a column with the expression Numeric.sequence("RowCount",1,1).