topic Re: Excel Schema validation in Talend Studio

Excel Schema validation

Anonymous — Sat, 16 Nov 2024 13:02:10 GMT

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.

Re: Excel Schema validation

Anonymous — Wed, 16 Mar 2011 12:19:05 GMT

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

Re: Excel Schema validation

Anonymous — Wed, 16 Mar 2011 12:41:21 GMT

Re: Excel Schema validation

Anonymous — Thu, 17 Mar 2011 15:45:51 GMT

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

Re: Excel Schema validation

Anonymous — Thu, 08 Aug 2013 15:09:17 GMT

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.

Re: Excel Schema validation

Anonymous — Thu, 08 Aug 2013 18:02:00 GMT

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

Re: Excel Schema validation

Anonymous — Thu, 08 Aug 2013 18:48:21 GMT

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.

Re: Excel Schema validation

Anonymous — Sun, 11 Aug 2013 15:55:47 GMT

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

Re: Excel Schema validation

Rahul_Lohar — Wed, 31 Oct 2018 07:46:08 GMT

Hi Shong,

How can we display number of reject rows on tJavaRow component? Is there any global variable to do so?

Regards

Nilesh