Split the input file using delimiter and output only few col



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Split the input file using delimiter and output only few col

Postby balaryan » Tue Oct 18, 2016 4:03 am

Hi All,

I am trying to read an input file which has a fixed length of 300 bytes and each field in a record are delimited by "|" pipe symbol. For instance

Input file:

1|232|this is great|chennai|TN|604023|04/12/2018|12321.00|12312.00|N|this is for testing only|external remarks|0|0|3|Y|N|454.00|454.00
1|1232|this is great-o-great|Pune|MA|604023|04/11/2015|321.00|122.01|Y|this is for testing only|external remarks with comments|0|0|4|Y|N|54.00|14.00
1|8059|this is amazing|banglore|KA|298923|28/04/2013|321.00|662.00|N|this is for testing|external remarks|0|0|5|Y|N|344.00|984.00
1|092334|this is outstanding data|Mumbai|MA|23908043|18/08/2016|23321.00|656262.23|Y|this is for purely for testing only|external remarks|0|0|5|N|Y|323.00|1284.00
1|332|this is super|Pune|MA|6023|06/11/2010|9032.12|1233.01|Y|this is for testing only|comments|0|0|5|Y|N|154.00|124.00


I want to include only specific FIXED columns of each record from the input file say 1st, 2nd, 4th, 5th, 6th,9th, 10th, 13th, 14th, 15th in the output file. Is there any option or way to select the records based on the column using the delimiter without fixed length record?

Kindly advice.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Split the input file using delimiter and output only few

Postby balaryan » Tue Oct 18, 2016 4:20 am

balaryan wrote:Hi All,

I am trying to read an input file which has a fixed length of 300 bytes and each field in a record are delimited by "|" pipe symbol. For instance

Input file:

1|232|this is great|chennai|TN|604023|04/12/2018|12321.00|12312.00|N|this is for testing only|external remarks|0|0|3|Y|N|454.00|454.00
1|1232|this is great-o-great|Pune|MA|604023|04/11/2015|321.00|122.01|Y|this is for testing only|external remarks with comments|0|0|4|Y|N|54.00|14.00
1|8059|this is amazing|banglore|KA|298923|28/04/2013|321.00|662.00|N|this is for testing|external remarks|0|0|5|Y|N|344.00|984.00
1|092334|this is outstanding data|Mumbai|MA|23908043|18/08/2016|23321.00|656262.23|Y|this is for purely for testing only|external remarks|0|0|5|N|Y|323.00|1284.00
1|332|this is super|Pune|MA|6023|06/11/2010|9032.12|1233.01|Y|this is for testing only|comments|0|0|5|Y|N|154.00|124.00


I want to include only specific FIXED columns of each record from the input file say 1st, 2nd, 4th, 5th, 6th,9th, 10th, 13th, 14th, 15th in the output file. Is there any option or way to select the records based on the column using the delimiter without fixed length record?

Kindly advice.


Also I missed one info. Last field in each record will be having either 'Y' or 'N'. Based on the value, if it is 'Y', we should place the record with specific column in output file 1 and if it is 'N' means output file 2.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Split the input file using delimiter and output only few

Postby BillyBoyo » Tue Oct 18, 2016 11:41 am

Look at PARSE.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Split the input file using delimiter and output only few

Postby enrico-sorichetti » Tue Oct 18, 2016 1:12 pm

Last field in each record will be having either 'Y' or 'N'

looks like You posted the wrong records...
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 3002
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Split the input file using delimiter and output only few

Postby NicC » Tue Oct 18, 2016 3:02 pm

Do you mean columns or fields? In each case in yuor sample the second column is the pipe character.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Split the input file using delimiter and output only few

Postby balaryan » Tue Oct 18, 2016 9:01 pm

Yeah Billy. I got it. with PARSE we can achieve it. Thanks for your time.

@ Nic, i meant fields. Each fields are delimited by pipe symbol.

Thanks anyways.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post