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]

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

[Anonymous]

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?

[Anonymous]

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.

[Anonymous]

Hi,

Thank you so much for your guidance.

[cterenzi]

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.

[Anonymous]

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?

[Anonymous]

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)

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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?