[resolved] How to check values if they are "null" and replace them (...)

One Star

[resolved] How to check values if they are "null" and replace them (...)

Hello everybody,
I wish everyone here a happy and talended new year! Smiley Wink
The year comes for me with the following question:
I want to replace empty values (they have the value "null") with a zero (0).
Firstly, a picture of the job tells much more:

In my TOS-job I connect values coming from an excel-sheet and an oracle-database. Before that happens in a tmap, I split the rows coming from the excel-sheet in a needed way.  
How can I find out, which cells, coming from that excel-sheet are "null"? Because not all of them are "null" and those who are, cause issues later, when I want to load the new data into another program. My problem would be solved, if I could implement a routine, which checks, if there is a value in a cell coming from the excel-sheet and if the result shows, that the value is "null", set a new value 0 (means "zero").
Can I implement that as a variable in the tmap? And how? Or is it better to use another component before the dataflow flows into the tmap, like tReplace?
I hope I described it understandable.
Does somebody of you experts have an idea for me? Smiley Happy
Best regards
Bexy

 

Accepted Solutions
Highlighted
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hey everybody,
I think, I`ve found a solution:
I use this in the tmap to check the cells coming from the excel-sheet:
(Excel.Value1 == null) || ("".equals(Excel.Value1)) ? "0" : Excel.Value1

All Replies
Seventeen Stars

Re: [resolved] How to check values if they are "null" and replace them (...)

Do you mean the String value "null" or the real null (nothing)?
Using a routine is the best method to deal with problematic content. This way you could develop the rule at one place and reuse it in various places. This is my favourite way to deal with such things. 
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hello Jlolling,
there is nothing written into some of the cells - so I think the answer to your question is "real null". These fields are empty.
So, do you have an idea for me? That would be great! Smiley Happy
Best regards
Bexy
Highlighted
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hey everybody,
I think, I`ve found a solution:
I use this in the tmap to check the cells coming from the excel-sheet:
(Excel.Value1 == null) || ("".equals(Excel.Value1)) ? "0" : Excel.Value1
Moderator

Re: [resolved] How to check values if they are "null" and replace them (...)

Hi,
Have you tried method:

row.ColumnName==null || row.ColumnName.isEmpty())?"Unknown":row.ColumnName(null string) in tMap?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: [resolved] How to check values if they are "null" and replace them (...)

Thanks Bexy, your proposed works for me!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now