Fail Import if columns of the file are not aligned with Schema
Hello,
I have to set up a validation task before loading the data (from excel) into database (MongoDb). So I have to verify if the excel file is in pre-defined format or not. If the format doesn't match, the import fails. I have stored the file format in Metadata -->File Excel. Currently I am using tFileInput (Schema from repository)-->tMap (for some calculations) --> tMongoDbOutput.
Note : I tried exchanging the file columns and importing file, it gets loaded in DB but doesn't map it to correct column. for example my file had 4 columns as defined in schema (id, name, addr, country) all string except id. I placed the file in different format (id,name,country,addr). It imported country name under addr field and vice verse.
Can someone guide me how can I validate and reject this file since it doesn't meet the pre-defined format ?
Thanks in advance!
Take a look at
tSchemaComplianceCheck component, this component is used to check the input data with a pre-defined schema. After tSchemaComplianceCheck, you can check the number of lines in the reject flow, if the reject flow has rows, it means the input file is invalid.
Best regards
Shong
Hi,
- Do you have tMap in between the source and target... in this case it will happen.
- Do you have a header in input file? If the header is not present, still you will face the same issue. Because how Talend would know that you are exchanging the data.
Can you provide the snapshot of your input file and talend job?
thanks
vaibhav
Thanks Shong. By placing tSchemaComplianceCheck before tMap helped to resolve the issue.
@Vaibhav- Yes there is a tMap between source and target, but I wanted this validation before tMap, and shong's suggestion seems to working fine. Also the file do have headers
Thanks,
npatel
I did some more testing and seems it fails to map the correct column. Of what I understand is, tSchemaComplianceCheck references the schema and checks data types or length of row against values. Attaching the example below for your reference.
Since the data type is string and lenght of data in within limit it populates email address in Name column and vice versa.
Schema_validate.zip.zip
I need data to move to correct column even if the order of columns is not as per schema or atleast fail the component. But not load "Email address" in "Name" column.
Is there any component to do this validation ??
