topic Re: compiler error in Talend open studio for DI in Talend Studio

compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 08:24:59 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 08:30:47 GMT

error screenshot

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 09:59:21 GMT

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?

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 10:08:45 GMT

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 10:23:13 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 10:30:43 GMT

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"

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 10:36:44 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 10:45:14 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 13:29:46 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 13:38:29 GMT

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.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 13:51:43 GMT

In excel file for all the data types like string, integer, float, big decimal,date has both null string and some data .

for example:

in source file:

col1 col2 col2

a 12.3 05/23/2016

null null null

b null null

expected output: sql server table

col1 col2 col2

a 12.3 05/23/2016

null null null

b null null

I really don't know to use this java inbuilt convert types like

Integer.parseInt(),Float.parseFloat() in my expression builder

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 13:59:18 GMT

I hope you understood my requirements!

If you don't mind can you send me some demo to load data with integer, float, date, string, bigdecimal type?

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 16:24:04 GMT

You can refer to this Screenshot,

These are the conversions happening,

Col1 : string to integer

Col2 : string to float

Col3 : string to date

Col4 : string to string

Col5 : string to bigdecimal

Be careful though. Here I am assuming that your input data matches the datatypes. If it doesn't then the job will fail.

So you have to make sure that you filter out the values which are invalid before converting them.

Re: compiler error in Talend open studio for DI

Anonymous — Wed, 10 May 2017 16:47:03 GMT

Hi,

Thank you so much for your guidance.

Re: compiler error in Talend open studio for DI

cterenzi — Wed, 10 May 2017 17:16:02 GMT

If you specify a non-String type on a column and it contains the text "null" the row will not conform to the schema and be rejected. You can see these rows by sending a secondary (reject) flow from your xls input component to tLogRow. If you need to handle the text "null" in every column, you'll need all columns in your input schema to be Strings and handle the conversion in your tMap.

Re: compiler error in Talend open studio for DI

Anonymous — Thu, 11 May 2017 03:27:24 GMT

Hi,
I followed job process like below to check whether data is coming or not.

Excel input ----->tmap-------->tlogrow

I can see all my data which is coming from excel .
Problem is in date column there are different date formats.
Can anyone say how to validate those formats and modify those format into our desired format.
Say for an example
Column 1
5-12-2016 which is mm-dd-yyyy format
Tue Jul 08 00:00:00 IST 2014 (which is another format)
11/25/2016 ( another format)

These all data are presented in the input file.I would like to validate these formats and change into MM-dd-yyyy format .

Is this possible ? Can anyone say how to do it?

Re: compiler error in Talend open studio for DI

Anonymous — Thu, 11 May 2017 04:13:21 GMT

here I have attached the error while I'm trying this validation method

TalendDate.parseDate("dd/MM/yyyy",row1.ExcelDateCol)

(row1.ExcelDateCol!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.ExcelDateCol):null

facing the following error:

[statistics] connecting to socket on port 3901
[statistics] connected
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Mon Nov 04 00:00:00 IST 2013"
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at newproject.stellarexcel_tbl_0_1.Stellarexcel_tbl.tFileInputExcel_1Process(Stellarexcel_tbl.java:3899)
at newproject.stellarexcel_tbl_0_1.Stellarexcel_tbl.runJobInTOS(Stellarexcel_tbl.java:4690)
at newproject.stellarexcel_tbl_0_1.Stellarexcel_tbl.main(Stellarexcel_tbl.java:4547)
Caused by: java.text.ParseException: Unparseable date: "Mon Nov 04 00:00:00 IST 2013"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)

Re: compiler error in Talend open studio for DI

Anonymous — Thu, 11 May 2017 06:52:02 GMT

The format that you are using to parse the date should match the incoming value.

In your case the format should be "EEE MMM dd HH:mm:ss 'IST' yyyy" instead of "dd/MM/yyyy".

Also you should mention the same format as the output column date pattern.

Re: compiler error in Talend open studio for DI

Anonymous — Thu, 11 May 2017 06:54:08 GMT

If I use this format for validation then what about the other date which is also present in the same column.

Re: compiler error in Talend open studio for DI

Anonymous — Thu, 11 May 2017 07:09:52 GMT

I guess we are almost done.

My question is can we write expression with AND operator to check for all the date formats from the file other than checking only null?