Excel Schema validation

One Star

Excel Schema validation

Hi Guys,
Is it possible to validate the excel schema in component tFileInputExcel before the job moves futher?
I've some schema defined as
ReportDate -- Date,
Name -- String,
Code -- String,
Text1 -- String,
Text2 -- String
My file name is picked up from the context as Context.FileName (I guess it doesn't matter if the schema is Built-in or Repository).
My input excel file has some missing column and is something like below
ReportDate, Name, Code, Text2
The excel file is missing the Text1 column, but the component tFileInputExcel_1 doesn't validate the schema and assumes the column Text2 is Text1 and the column after Text2 as Text2 (as in the image, the column I marked in red).
Is there a way the component tFileInputExcel_1 could have validated the incoming information (schema) from the excel file itself or can we validate it using some other component, wherein the component tSchemaComplianceCheck) is not working for me and also 'die on error' in tFileInputExcel do not work either. I'm using TIS(4.1.1)
Thanks.
One Star

Re: Excel Schema validation

Hi
After tFileInputExcel use a tSchemaComplainceCheck for schema check.
tFileInputExcel ----main--->tSchemaComplainceCheck
One Star

Re: Excel Schema validation

Hi Lijo,
The first image shows how I've set the components and I get the below result.
.------------+-----------------+----+--------+-----.
| AcceptedRows |
|=-----------+-----------------+----+--------+----=|
|ReportDate |Name |Code|Text1 |Text2|
|=-----------+-----------------+----+--------+----=|
|Dec 31, 2010|Talend Component1|ABC |blahbbbb|null |
|Dec 31, 2010|Talend Component2|EFG |blahdddd|null |
|Dec 31, 2010|Talend Component3|HIJ |blahffff|null |
'------------+-----------------+----+--------+-----'
.----------+----+----+-----+-----+---------+------------.
| RejectedRows |
|=---------+----+----+-----+-----+---------+-----------=|
|ReportDate|Name|Code|Text1|Text2|errorCode|errorMessage|
|=---------+----+----+-----+-----+---------+-----------=|
'----------+----+----+-----+-----+---------+------------'
Second image shows the setting for tSchemaComplianceCheck_1 component.
I also had the Custom defined option selected as in image 3, but none is working. I also had unchecked Die on error option in the tFileInputExcel_1. Also I cannot validate on Nullable columns.
Any other suggestion?
Thanks.
One Star

Re: Excel Schema validation

Hi
In the third figure don't check anything as nullable. As you have checked it it will always go forward instead of being rejected.
hope the below screen shot will help you
One Star

Re: Excel Schema validation

Hi,
I am doing a Schema Validation using tSchemaComplicanceCheck against the tFileInputExcel. If the columns in the excel file changes then I am expecting the tSchemaComplicanceCheck to throw an error after doing a Schema check.
I am aware that I can make uncheck the columns not to be nullable. But this will not throw an error. Instead it will give you the rejected rows.

Can someone give me a suggestion on how to work with this?
Thanks,
Pooja.
Community Manager

Re: Excel Schema validation

Hi Pooja
You can read the reject rows on tJavaRow or check the number of reject rows if it greater than 1, and then fires a tDie or a tWarn component to send a message, and use tLogCatcher to capture the error message.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Excel Schema validation

Hi Shong,
I am reading the rejected rows in the tLogRow. For testing purpose I deleted the number of columns from the excel sheet and did run the job.
As I wanted it is throwing an error because of the change in the columns.
But the error it is throwing is :
Exception in component tFileInputExcel_1
java.lang.RuntimeException: Special sheets not exist!
Can you tell me what does this error mean?
If it is throwing due to the inconsistency with columns then what I am doing is correct because that is what I am expecting.
When I am using tWarn and tLogCatcher as you said, it is throwing the warning as well as the previous error (special sheets not exist)
Let me know what is that error about?
Thanks,
Pooja.
Community Manager

Re: Excel Schema validation

Hi Pooja
The special sheet name does not exist. You need to check the 'All sheet' option or input the sheet name in the Sheet list table and make sure the sheet name do exists. If there are multiple sheets in your excel file, and you want to read all sheets, check the 'All sheet' option.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business