[resolved] Talend _Vertica_Bulk Load - String _Date handling

[Anonymous]

Hi,
while try to load the below data using tVerticaoutputbulkexec,  record 3, with null values for d1 date column is getting rejected.
ID|d1|d2|Val
1|20071231|20071226|dt
2|20070302|20171226|dt
3||20171231|dt3
Output:
[statistics] connected
1|12-31-2007|12-26-2007|8
2|03-02-2007|12-26-2017|8
3||12-31-2017|0
Number of records inserted into table hls_others.test_Date by tVerticaOutputBulkExec_2_tVBE: 2
[statistics] disconnected

Error :
COPY: Input record 3 has been rejected (Invalid date format '' for column 2 (d1).Invalid input syntax for date: "").  Please see /Out/TestDate_Rej.dlm, record 1 for the rejected record. This record was read from STDIN
i am using below logic to handle null but still this record is getting error out
(row2.d1.equals(null)||row2.d1.equals("") ||row2.d1.length()==0)? null :
TalendDate.parseDate("yyyyMMdd",row2.d1) 
to avoid this, i am handling null  and still it is throwing an error.
 Mapping Design

mapping 
(row2.d1.equals(null)||row2.d1.equals("") ||row2.d1.length()==0)? null :
TalendDate.parseDate("yyyyMMdd",row2.d1) 


Pl Advise how to handle this situation ?

[vapukov]

try this:

(row2.d1=null||row2.d1.equals("") ||row2.d1.length()==0)? null :TalendDate.parseDate("MM-dd-yyyy",row2.d1) 

You can read function descriptions in tMap 
for the TalendDate.parseDate - yyyyMMdd is expected pattern of source string

[Anonymous]

the issue is due to null; verticabulkload  is using copy table STDIN <path> and if the column Is null then it is getting rejected to error file.
resolved with following workaround.
TalendDate.isDate(row1.d1,"yyyyMMdd")?TalendDate.parseDate("yyyyMMdd",row1.d1):TalendDate.parseDate("yyyyMMdd","99991231")
But i am eager to hear from our community for the solution " how to populate nulls for date columns while using verticabulkloadexe ".

[Anonymous · Accepted solution]

I was able to load and handle the null values for dates column. Its my understanding issue on the property settings in VerticaBulkExec.

Here is the answer for loading null values for date columns in Vertica using tVerticaBulkExec component. 
To check the input column and assign the null values.[ there is many way to check and assign. couple of options is here] 
TalendDate.isDate(row1.d2,"yyyyMMdd")?TalendDate.parseDate("yyyyMMdd",row1.d2):null 
or  row2.d1=null||row2.d1.equals("") ||row2.d1.length()==0)? null :TalendDate.parseDate("MM-dd-yyyy",row2.d1) or
[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] (!Relational.ISNULL(row1.d1) &&
!"".equalsIgnoreCase(row1.d1) )? TalendDate.parseDate("yyyyMMdd",row1.d1): null [/font]


[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] this will give an empty string for null columns. and you should handle this in advanced setting Null String "". [/font]
[font=consolas, monaco, bitstream vera sans mono, courier new, courier, monospace] Default value for this setting is "null"[/font]




Thanks