Replace N/A with integer

Five Stars

Replace N/A with integer

Hello Team,


I have a excel file like :


IDNameProjectID ProjectName


I am creating dimension table out of it for Project details through SCD component , projectid should be integer there but i am getting error as we have 'N/A' in the column. 


I tried tFilterRow component and expressions in tmap to replace N/A with integer but it dint work.


Basically entry in destination database should be :


ProjectID ProjectName



Arvind Jha

Forteen Stars TRF
Forteen Stars

Re: Replace N/A with integer


What happens when you open the file in Excel?

Do you have a "N/A" - which means you have the cell is a string - or do you have '#N/A' - which means a formula is used in this cell but returns an error?

On the Talend side, how did you try to filter undesirable lines?

Using tFilterRow:



or using tMap:



Sixteen Stars

Re: Replace N/A with integer

What error are you getting? I assume it is an error indicating that the column type is not Integer. If that is the case you will have to treat the column as a String, check to see if the value can be converted to an integer and then deal with filtering using that logic. Casting between String to Integer is pretty easy.

Five Stars

Re: Replace N/A with integer

Thanks for the response.


Actually what i observed was in source we have Data type as String[N/A] and at destination database it is integer.


So tfilterRow doesn't work as column still remains string so i converted to integer using (Integer.parseInt()) , then passed it onto database .The same was accomplished using tmap.


So even if there was no N/A and source data type would have been string i would have to parse it.

Five Stars

Re: Replace N/A with integer

It got resolved through casting