Five Stars

Replace N/A with integer

Hello Team,

 

I have a excel file like :

 

IDNameProjectID ProjectName
1test1Banking
2test12Healthcare
3test22Healthcare
4test31Banking
5test4N/AN/A

 

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
1Banking
2Healthcare

 

Thanks,

Arvind Jha

  • Data Integration
4 REPLIES
Nine Stars TRF
Nine Stars

Re: Replace N/A with integer

Hi,

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:

Capture.PNG

 

or using tMap:

Capture.PNG

 


TRF
Ten 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.

Rilhia Solutions
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