topic Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message in Talend Studio

[resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Sat, 16 Nov 2024 10:59:39 GMT

Hi there,
I have a tFileInputPositional with a schema set that defines string, date and integer columns. I am reading this into a tJavaRow.
In the tJavaRow I want to check if there is an error for this row. Eg; for an integer when I supply the value "12a" from the source file for a test, I see in the log the following message for that row:
For input string: "12a"
As a side note it would be magnificent if the above error message actually said something like the following as this is a message you could send back to the user:
Invalid Integer in column: "Col1" with value: "12a" on line# 3
Having said that, the output schema of tJavaRow has a couple of additinal columns from the input. These columns are RawText and ErrorMessage.
What I need to be able to to is:
- For every row I need to write the entire line value into the RawText field of that row.
- For every row check to see if there was an error reading in the current row and if there was an error, then create an error message like the one above that quotes column name, value, and line#, and write the error message to the ErrorMessage field of that row.
What is the best way to do this?
Thank you for your time,
Scott

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Tue, 13 Oct 2015 02:20:15 GMT

Hi Scott
Actually, this error is thrown on tFileInputPositional component, if you check the 'die on error' box, the component will the throw out the Java exception and display the error message.
You can read all columns with string type, and then link tFileInputPositional to a tSchemaComplianceCheck, and use a custom schema to check the data, if the data does not match the schema, it will be rejected with external columns including error code and error message.
Best regards
Shong

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Tue, 13 Oct 2015 23:37:56 GMT

Shong,
Thanks for the response mate. Ok so I created a test file as follows:

001Name Value here120151013Description Value Here1789190
002Name Value here220151035Description Value Here2789190
03aName Value here320151013Description Value Here3789190

So this screen shot demonstrates how the columns are defined:

For testing purposes line 2 has an invalid date in Col3, and line 3 has an invalid integer in Col1
The tFileInputPositional is configured as follows:

The tSchemaComplianceCheck is configured as follows:

But it shows this warning:

And when I run the job I get the following error:

Starting job TestValidationForPositionalFileImport at 09:35 14/10/2015.
 connecting to socket on port 3711
 connected
Exception in thread "main" java.lang.Error: Unresolved compilation problems: 
	Type mismatch: cannot convert from String to Integer
	Type mismatch: cannot convert from String to Date
	Type mismatch: cannot convert from String to Integer
	at test.testvalidationforpositionalfileimport_0_1.TestValidationForPositionalFileImport.tFileInputPositional_1Process(TestValidationForPositionalFileImport.java:1238)
Job TestValidationForPositionalFileImport ended at 09:35 14/10/2015.

There is a tLogRow component connected via row/main to the tSchemaComplianceCheck and it has the same schema as the output schema of the tSchemaComplianceCheck
What have I done wrong here?
Thanks for your time,
Scott

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 02:15:15 GMT

It is a compliance error in the generated code of job, because the output schema is different from input schema. The schema of tSchemaComplianceCheck should be same as the schema of tFileInputPositional, but check the 'custom defined' box and define custom schema to check the input data.

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 03:01:27 GMT

Shong,
Thanks for the reply mate. So I did a Sync Columns on the tSchemaComplanceCheck component and now it runs without error.

And the output of the job is:

Starting job TestValidationForPositionalFileImport at 12:54 14/10/2015.
 connecting to socket on port 3500
 connected
001|Name Value here1|20151013|Description Value Here1|789190
 disconnected
Job TestValidationForPositionalFileImport ended at 12:54 14/10/2015.

Which is correct it has only loaded the one line that was without error. But how do I get the error messages for the other lines?
Basically for every line that has an error I need to get the error for that line and put it in a field in an output schema of a tJavaRow that I would connect to the tSchemaComplianceCheck component.
I also need to get the entire line of the file being read as a string and assign that to a field in the output of the tJavaRow.
How can I do this mate.
Regards,
Scott

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 03:37:22 GMT

The problematic lines are rejected and put them in the reject flow, right click on tSchemaComplianceCheck and select 'main-->reject' and link it to a output component such as tLogRow.

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 04:40:07 GMT

Shong,
That's is perfect mate! Thanks for your efforts 😉
Is there a definition of the error codes somewhere?

Starting job TestValidationForPositionalFileImport at 14:35 14/10/2015.
 connecting to socket on port 3416
 connected
.----+----------------+--------+-----------------------+------.
|                          tLogRow_2                          |
|=---+----------------+--------+-----------------------+-----=|
|Col1|Col2            |Col3    |Col4                   |Col5  |
|=---+----------------+--------+-----------------------+-----=|
|001 |Name Value here1|20151013|Description Value Here1|789190|
'----+----------------+--------+-----------------------+------'
.----+----------------+--------+-----------------------+------+---------+------------------------------------------.
|                                                    tLogRow_3                                                     |
|=---+----------------+--------+-----------------------+------+---------+-----------------------------------------=|
|Col1|Col2            |Col3    |Col4                   |Col5  |errorCode|errorMessage                              |
|=---+----------------+--------+-----------------------+------+---------+-----------------------------------------=|
|002 |Name Value here2|20151035|Description Value Here2|789190|2        |Col3:wrong DATE pattern or wrong DATE data|
|03a |Name Value here3|20151013|Description Value Here3|789190|2        |Col1:wrong type                           |
'----+----------------+--------+-----------------------+------+---------+------------------------------------------'
 disconnected
Job TestValidationForPositionalFileImport ended at 14:35 14/10/2015.

Thanks for your time,
Scott

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 04:46:24 GMT

Oh actually one more thing... how do I get the complete line of text for each line in the file. In the output schema of tJavaRows that I will connect to the row main and row reject there is a field called RawText where I have to put the raw text from each line in the corresponding row in the database table. We are staging data from these import text files in the database so I need to import the data into fields, record an error for each line if there is one, (which we have sorted now), record the raw text from each line in a field of the import row.
How can I do this?
Regards,
Scott

Re: [resolved] tFileInputPositional > tJavaRow check if row has error and map message

Anonymous — Wed, 14 Oct 2015 04:49:58 GMT

Actually you have already provided a solution for that here: https://community.talend.com/t5/Design-and-Development/resolved-Get-input-file-row-by-row-as-a-string/td-p/110268
Thanks again,
Scott