Talend community forum / 'N/A' columns to NULL using Tmap

vikram · 2011-04-25 11:44:41

Tags: [bug, context, CSV file, database, date, delimited file, deployment, development, error, error management, java, positional file, PostgreSQL, schema]

If a row has 'N/A' , how do i convert it into NULL using tmap or for that matter any other function
Can we apply some IF condition ??

shong · 2011-04-25 11:58:22

Hi

Yes, it is possible, what's the data type of column?

Best regards
Shong

Vikram · 2011-04-25 12:03:38

There are 2 columns with the same problem , One has Varchar and the second one has date.
Source is a csv , which has rows containing 'N/A' along with data ...
i am using version 3.2.1 .....

Should i use any other function ??

Thanks
Vikram

vikram · 2011-04-25 13:27:37

shong wrote:
Hi

Yes, it is possible, what's the data type of column?

Best regards
Shong

There are 2 columns with the same problem , One has Varchar and the second one has date.
Source is a csv , which has rows containing 'N/A' along with data ...
i am using version 3.2.1 .....

Should i use any other function ??

Thanks
Vikram

vikram.mk · 2011-04-25 15:32:47

Hi Shong,

I used row1.City.equals("N/A") ? null : row1.City for the varchar column ,
but for the date column its not working , also the date column is in dd-MMM format

Thanks
Vikram

Vikram · 2011-04-26 12:10:27

Anybody ???

lijolawrance · 2011-04-26 13:25:49

Hi

You can use TalendDate.parseDate for parsing the date column to your required format
eg
TalendDate.parseDate("yyyy-MM-dd HH:m:ss",Date)
You can change the format according to your use

Vikram · 2011-04-26 19:44:23

Hi Lijo,

I have a csv file which contains 5 columns
2 columns have 'N/A' mentioned in them along with data , i am trying to convert 'N/A' to NULL

Of those 2 columns , one is DATE TYPE and other one is VARCHAR

I am using t_Map

For the VARCHAR the problem was solved with " row1.City.equals("N/A") ? null : row1.City "

For the Date column which contains dates along with 'N/A' , am unable to convert them 'N/A' to NULL.

I am populating data from CSV file to SQL server ...
SQL server has has same columns 5 columns , with the same data types.

Should i use any other function apart t_Map or add any other function ??

PS: Shong any suggestion ...

Thanks

Vikram

alevy · 2011-04-27 02:46:54

How can a date variable contain the value 'N/A'? If you mean that's the value in your file, then you need to read the file with that column defined as a String in the tFileInputDelimited schema, then use an expression like you've already discovered to remove the 'N/A' values and convert the result to a Date using Lijo's expression to write to MS SQL e.g. row1.myDate.equals("N/A")?null:TalendDate.parseDate("dd/MM/yyyy",row1.myDate).

Vikram · 2011-05-09 13:33:12

Thanks Alevy and Lijo..

Announcement

Unanswered posts

#1 2011-04-25 11:44:41

'N/A' columns to NULL using Tmap

#2 2011-04-25 11:58:22

Re: 'N/A' columns to NULL using Tmap

#3 2011-04-25 12:03:38

Re: 'N/A' columns to NULL using Tmap

#4 2011-04-25 13:27:37

Re: 'N/A' columns to NULL using Tmap

shong wrote:

#5 2011-04-25 15:32:47

Re: 'N/A' columns to NULL using Tmap

#6 2011-04-26 12:10:27

Re: 'N/A' columns to NULL using Tmap

#7 2011-04-26 13:25:49

Re: 'N/A' columns to NULL using Tmap

#8 2011-04-26 19:44:23

Re: 'N/A' columns to NULL using Tmap

#9 2011-04-27 02:46:54

Re: 'N/A' columns to NULL using Tmap

#10 2011-05-09 13:33:12

Re: 'N/A' columns to NULL using Tmap

Board footer