Replace N/A with integer

[Anonymous]

Hello Team,

I have a excel file like :

ID	Name	ProjectID	ProjectName
1	test	1	Banking
2	test1	2	Healthcare
3	test2	2	Healthcare
4	test3	1	Banking
5	test4	N/A	N/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
1	Banking
2	Healthcare

Thanks,

Arvind Jha

[TRF]

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:

or using tMap:

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

It got resolved through casting