Seven Stars

compiler error in Talend open studio for DI

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.

  • Data Integration
25 REPLIES
Seven Stars

Re: compiler error in Talend open studio for DI

error screenshot

sqlserver_talend.PNG

Seven Stars

Re: compiler error in Talend open studio for DI

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?

Seven Stars

Re: compiler error in Talend open studio for DI

schema.PNG

Seven Stars

Re: compiler error in Talend open studio for DI

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.

Seven Stars

Re: compiler error in Talend open studio for DI

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"

Seven Stars

Re: compiler error in Talend open studio for DI

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.

Seven Stars

Re: compiler error in Talend open studio for DI

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.

 

 

Seven Stars

Re: compiler error in Talend open studio for DI

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

tmap.PNG

 

the above screenshot is correct?

 

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

 

 

 

Seven Stars

Re: compiler error in Talend open studio for DI

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.

Seven Stars

Re: compiler error in Talend open studio for DI

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

Seven Stars

Re: compiler error in Talend open studio for DI

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?

 

Seven Stars

Re: compiler error in Talend open studio for DI

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

 

dataTypeConversion.PNG

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.

Seven Stars

Re: compiler error in Talend open studio for DI

Hi,

Thank you so much for your guidance.

Tags (1)
Nine Stars

Re: compiler error in Talend open studio for DI

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.
Seven Stars

Re: compiler error in Talend open studio for DI

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?
Seven Stars

Re: compiler error in Talend open studio for DI

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)

 

 

 

 

 

 

 

 

 

 

 

 

 

Seven Stars

Re: compiler error in Talend open studio for DI

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.

Seven Stars

Re: compiler error in Talend open studio for DI

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

Seven Stars

Re: compiler error in Talend open studio for DI

unparsedate.PNG

 

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?

 

Seven Stars

Re: compiler error in Talend open studio for DI

I would like to use something like below code

(row1.GL_DATE!=null && row1.GL_DATE.equals(TalendDate.formatDate("M-DD-yyyy")) && row1.GL_Date.equals(TalendDate.formatDate("MM-dd-yyyy"))?TalendDate.parseDate("EEE MMM dd HH:mm:ss 'IST' yyyy",row1.GL_DATE):null 

 

Is this possible?

using this format "EEE MMM dd HH: mm: ss 'IST' yyyy" this date issues got solved "Mon Nov 04 00:00:00 IST 2013"

then what is the possibilities there to solve the issues for all date formats in the same column?

Seven Stars

Re: compiler error in Talend open studio for DI

I would suggest using a routine to parse the dates in that case.

Take a look at the below thread in StackOverflow,

http://stackoverflow.com/questions/4024544/how-to-parse-dates-in-multiple-formats-using-simpledatefo...

Seven Stars

Re: compiler error in Talend open studio for DI

Is this is the only option?Smiley Sad

can't we validate via writing condition?

Seven Stars

Re: compiler error in Talend open studio for DI

I can't really think of anything else. Moreover using a routine here would be a safer and easier solution compared to anything else.

Five Stars

Re: compiler error in Talend open studio for DI

I think the pattern "M-d-YYYY" should do the job.

Five Stars

Re: compiler error in Talend open studio for DI

Sorry, read partially.

No you cannot, because one of the parse will fail with an exception and not witha a condition.

You need a routine that check the possible format or better try to understand which one use (maybe through regexp).

I suggest if possible to normalize the date format with a single format, if not will be a nightmare with some performance impact, if the data become huge.