If you read your sourcefile with a definition of just one column then a simple derivation of "TRIM(In.BigColumn,";","T") to remove extraneous delimiters. In the second case define all of the columns explicitly in your input column descriptions and then output the fields as numeric
Thanks for all the help. The problem I have is I am not able to read my source file through datastage. The file is in the format below with carriage return (^M) at the end of each line and all the rows are not with the same number of columns
I tried couple of ways to read the data like removed the ^M using dos2unix command, tried to put everything into one single string making the format to fixed width but none of the techniques worked. Most of the time it throws error as too many columns and sometime datasource is empty. I tried to use the sed command to remove the trailing ';' but couldn't get it right.
you would need to define this file as having one column that is defined as longer than the maximum data length. You can set your line terminators to use just CR and not <CR><LF> (use Unix file type).
Then use the OCONV in a transform as listed above. No need to use UNIX commands.