compiler error in Talend open studio for DI

[Anonymous]

Hello All,

My requirement is to load data from excel to sql server table.

Im suppose to validate my data in tmap to check for null values:

here are the my validations sample :

1. Integer (source and target) integer type. -> (row1.XXAPID == null)? null:row1.XXAPID 

2. for Date type .-> row1.INVOICE_DATE.equals("null")?null:TalendDate.parseDate("MM-dd-yyyy HH:mm:ss",row1.INVOICE_DATE) and checked with TalendDate.formateDate also.

3. string to string type-> (row1.LINE_TYPE_LOOKUP_CODE.equals("null")? null :row1.LINE_TYPE_LOOKUP_CODE 

4. float to float type --> (row1.DIST_ITEM_AMOUNT ==null) ? null : row1.DIST_ITEM_AMOUNT.floatValue() 

 I'm facing the following  error while running my job

Can any one please help me to resolve this.

It's very emergency.

[Anonymous]

error screenshot

[Anonymous]

From what I can tell, it looks like the schema defined in the input component is not correct.

Can you give a screenshot of the schema and a sample data?

[Anonymous]

[Anonymous]

Does your input data contain any values as "null"?

What I mean is, if your input contains "null" as a string, then its technically not a null value. It is a string with value as "null". 

And this data should not be coming in any of the integer or float or date fields that you have mentioned.

So you should either correct your input data so that it has empty values instead of string "null".

Or you need to read all the columns as string and then validate them and finally convert them to the required datatypes before loading them to the target.

[Anonymous]

ya in excel data it has null values like u have mentioned.For validating purpose I have handled the following ways

here are the my validations sample :

1. Integer (source and target) integer type. -> (row1.XXAPID == null)? null:row1.XXAPID 

2. for Date type .-> row1.INVOICE_DATE.equals("null")?null:TalendDate.parseDate("MM-dd-yyyy HH:mm:ss",row1.INVOICE_DATE) and checked with TalendDate.formateDate also.

3. string to string type-> (row1.LINE_TYPE_LOOKUP_CODE.equals("null")? null :row1.LINE_TYPE_LOOKUP_CODE 

4. float to float type --> (row1.DIST_ITEM_AMOUNT ==null) ? null : row1.DIST_ITEM_AMOUNT.floatValue() 

but I got an error as " For Input String :null"

[Anonymous]

can you please tell me the process to read all the columns as a string and then validate them and finally convert them to the required datatypes before loading them to the target.

[Anonymous]

Ideally, you will need to correct your input data if possible. That's the right way to do it.

But assuming you can't, you have to define all the column datatypes as string in your input component.

Once you do that you can filter out all the nulls using,

row.column.equals("null")

or better yet

(row.column==null?"":row.column).equals("null")

Note the difference between the two nulls. The last one enclosed in double quotes is a string and the first one is the actual null.

Once you filtered out the null values, you can use java inbuilt functions to convert the datatypes.

For example for integer columns, you can do Integer.parseInt(row.column) in a tMap component.

[Anonymous]

Hello Arvin,

Like you said I tried executing my talend job still am facing issues like";" expected.

can you tell me whether I'm going in the correct way.

here I have attached my job flow in tmap

the above screenshot is correct?

Tfileexcelinput----->main----------->tmap-------------->main------>tmssqloutput.

[Anonymous]

Do you want to filter out null values or do you need to mention a default value if you encounter a null?
If you want to filter out null values you can just give in the tMap filter condition,
(row1.CREATED_BY==null?"":row1.CREATED_BY).equals("null")

and after you filter out the values, you can use a tmap after the filter to convert the datatypes.

But if you want to give a default value when you encounter a null, then in the expression editor you need to use a ternary operator,
(row1.CREATED_BY==null?"":row1.CREATED_BY).equals("null")
?
<mention default value>:Integer.parseInt("mm/dd/yyyy",row1.CREATED_BY)

This is assuming that CREATE_BY is a integer column. Also your output column should be declared as a integer.