topic Re: Replace N/A with integer in Talend Studio

Replace N/A with integer

Anonymous — Thu, 01 Jun 2017 11:12:12 GMT

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

Re: Replace N/A with integer

TRF — Thu, 01 Jun 2017 12:34:07 GMT

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:

Re: Replace N/A with integer

Anonymous — Thu, 01 Jun 2017 12:36:19 GMT

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.

Re: Replace N/A with integer

Anonymous — Thu, 01 Jun 2017 14:31:50 GMT

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.

Re: Replace N/A with integer

Anonymous — Thu, 01 Jun 2017 14:32:36 GMT

It got resolved through casting