One Star

Check file and upload file structure in database

hi
I have to validate the data of a tab-delimited files. If all is well in the office database. What I have to do is
1 - Validate if the correct number of fields in each record
2 - Validate the length of the field
3 - Validate that if they meet a decimal formats.
4 - Validate that certain fields have values that are in database tables
5 - If no date fields have to satisfy a pattern
6 - Report of the errors of each record
I can not use tschemaconpliancecheck and delimited file option to verify the structure of each row against the schema
Assuming that the field delimiter is the tab end of the file I have a number of lines that are not fixed at the end as
-->-->-->-->-->
-> ->
->
->->->->->->
I can come sometimes 3 lines, other 4 and other n.
I also want to know how to load these files when satisfied that everything is correct
Regards,
Oscar Manso
11 REPLIES
One Star

Re: Check file and upload file structure in database

Hi
Can anyone answer me

Regards,
Oscar Manso
One Star

Re: Check file and upload file structure in database

Why can't you use tschemaconpliancecheck and delimited file option?
One Star

Re: Check file and upload file structure in database

OK...
1) Validate if the correct number of fields in each record
This can be done using tFileInputDelimited itself : In tFileInputDelimited --> Advanced Settings check the Check each row against schema.This will check if the row structure matches the defined structure for the component.If there is an error you have use Reject link of tFileInputDeimited to get the error row it also give the reject reason !!!
2 - Validate the length of the field
I think tFileInputDelimited wont check the length of the field against your defined structure.So for that link your tFileDelimited to tSchemaComplianceCheck component.The tSchemaComplianceCheck will check the length if it matches...Else just like tFileInputDelimited there is a Reject link for tSchemaComplianceCheck which will give you ejected records which exceed length and also the reason is shown.
3 - Validate that if they meet a decimal formats.
What kind of format is expected >>>> ?

4 - Validate that certain fields have values that are in database tables
For this you will have to do an inner join with a tMap between your source file and the Database(tOracleInput) .Here you can simply forward rows which match.(Optionally : you can also catch the inner join rejected rows,which in your case does not seem applicable).
5 - If no date fields have to satisfy a pattern
This again can be done using tFileInputDelimited where you mention your pattern in the Schema in the Date pattern column e.g dd-MM-yyyy

6 - Report of the errors of each record
Each rejected record can be taken as i said in a Rejected links component(e.g tLogRow)
One Star

Re: Check file and upload file structure in database

hi
Although not validate the fields and the file is good in principle, how do I load it into database? Remember that the file you end up with a variable number of lines
Regards,
Oscar Manso
One Star

Re: Check file and upload file structure in database

tOracleOutput component to load it into a Oracle DB
One Star

Re: Check file and upload file structure in database

If u want to check the value of decimal then you can match it to a particular format using java`s regex in Filterrow component and also get the rejected rows .
One Star

Re: Check file and upload file structure in database

hi
Forget validations and tell me how to read a file that ends with a variable number of lines like the following to load it into database. I must load the entire file or nothing. These control lines are used but they are never fixed and my file would be correct.
-->-->-->-->-->
-> ->
->
->->->->->->
Regards,
Oscar Manso Sanz
One Star

Re: Check file and upload file structure in database

Replace them with ""
One Star

Re: Check file and upload file structure in database

i m not able to understand what you are saying !
i think you can check your validations and then OnSubJobOk you can load them.
One Star

Re: Check file and upload file structure in database

i m not able to understand what you are saying with Replace them with ""
One Star

Re: Check file and upload file structure in database

Hi
Can you show 2-3 sample rows in this thread for better understanding