• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Perl script logic help

Can anyone please advise on following-
The input file is in the following format(data is in double quotes with pipe seperator)-
header1
header2
header3
blank line
header5
"abc"|"123"|"2010-09-03"|" "|"344"|"jpu"
"dfg"|"456"|"2010-09-03"|" "|"567"|""usd"
and so on...

The output file we want is as follows(columns swapped from input file with some dummy columns at end separated by pipe and no double quotes)Below is the standard format---
col1|col0|col2(in  'yyyymmdd' format)|col4|null|88

Eg -to create output file from above input file-
123|abc|20100903|344|null|88
456|dfg|20100903|567|null|88

How can we create this output file using above input file through perl script. The perl script will be passed with input file name as a parameter.Please advise

Thx in advance

0
sunilbains
Asked:
sunilbains
  • 8
  • 5
  • 3
  • +2
1 Solution
 
jeromeeCommented:
try this...

Good luck!
use strict;

my $file = $ARGV[0];
open(FILE, $file) || die "Can't open $file: $!\n";	
<FILE> for 1..5;	# skip the first 5 lines

while( <FILE> ) {
   chomp;
   s/(^")|("$)//;	 # Get rid of leading/traing doublequotes
   my @fields = split /"\|"/;
   $fields[2] =~ s/-//g;
   print join("|", @fields[1,0,2,4], qw(null 88))."\n";
}
close(FILE);

Open in new window

0
 
ozoCommented:
perl -lne 'print "$2|$1|$3$4$5|$6|null|88" if/"(.*?)"\|"(.*?)"\|"(\d+)-(\d+)-(\d+)"\|.*?\|"(.*?)"\|/' <<ENDHERE
header1
header2
header3
blank line
header5
"abc"|"123"|"2010-09-03"|" "|"344"|"jpu"
"dfg"|"456"|"2010-09-03"|" "|"567"|""usd"
and so on...
ENDHERE
0
 
adelaraCommented:
Here it is ready to go.
Save it to something like ...  replaceMe.pl  and use it like this: (works on Windows and Unix)

c:/> replaceMe.pl  fileInput.txt  fileOut.txt

# -------------- snip ---------------------------
#!/bin/perl

open( FILE_IN,  "<", "$ARGV[0]" ); # open file for input
open( FILE_OUT, ">", "$ARGV[1]" ); # open the output file

while(my $line = <FILE_IN>)
{
    next if ( $line =~ /header1/ );       # skip if line starts with # (comments ...)
    next if ( $line =~ /header2/ );       # skip if line starts with # (comments ...)
    next if ( $line =~ /header3/ );       # skip if line starts with # (comments ...)
    next if ( $line =~ /^$/ );            # skip blank lines
    chomp( $line );                       # remove newline so it won't go to last value

    my @col = split( /\|/, $line );       # now you have every field in an array's record
    $col[2] =~ tr/\-//d;                  # fix date (remove dashes)

    # now re-assemble the line in the order you want
    my $newLine = $col[1] . '|' . $col[0] . '|' . $col[2] . '|' . $col[4] . '|null|88';
    $newLine    =~ tr/\"//d;                   # delete whatever quotes are left

    print FILE_OUT  "$newLine\n";  # write modified line to output file

}

close( FILE_IN );
close( FILE_OUT );
# -------------- snip ---------------------------


0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sunilbainsAuthor Commented:
Hello Jeromee..
The requirement is slight change...Please advsie on how can i accomplish this-

The input file is in the following format-
20090915|AAAAA|T E S T  B R O K E R  T E S T  T E S T |FUND|XXXXXXX|984121103|  |XEROX CORP|  |  |DTC|9300.000|US9841211033|ISN|
20090916|AAAAB|T E S T  B R O K E R  T E S T  T E S T |FUND|XXXXXXX|984121103|  |XEROX CORP|  |  |DTC|9300.000|US9841211033|ISN|
and so on...

The output file we want is as follows(columns swapped from input file with some dummy columns at end separated by pipe )Below is the standard format---
col1|CU|col0|col5

Eg -to create output file from above input file-
AAAAAA|CU|20100915|984121103
AAAAAB|CU|20100916|984121103

How can we create this output file using above input file through perl script. The perl script will be passed with input file name as a parameter.Please advise
0
 
jeromeeCommented:
Hi sunilbains,
Here's a solution to your new problem

$ perl -ne'@f=split/\|/; print join("|", $f[1], "CU", @f[0,5])."\n"' input_file
AAAAA|CU|20090915|984121103
AAAAB|CU|20090916|984121103
0
 
sentnerCommented:
Note you can also do this with other unix utilites, not even requiring perl...

For example:

$ awk -F"|" '{print $2 "|CU|" $1 "|" $6}' broker.txt
AAAAA|CU|20090915|984121103
AAAAB|CU|20090916|984121103

0
 
jeromeeCommented:
Here's awk-like version of what I offered above:
perl -F'\|' -ane'print join ( "|", $F[1], "CU", @F[0,5] ) ."\n"' input_file
AAAAA|CU|20090915|984121103
AAAAB|CU|20090916|984121103

Note the -a and -F to replace the @f=split
0
 
ozoCommented:
perl -F'\|' -aple '$"="|";$F[2]="CU";$_="@F[1,2,0,5]"'
0
 
jeromeeCommented:
cryptic but nice, ozo!
0
 
sunilbainsAuthor Commented:
Hello Jeromee..
The requirement is again change...Please advsie on how can i accomplish this-

The input file is in the following format-
kAccount Code|Account Type|Position Date|SEC ID|Quantity|Currency
AAAAAA||07-Sep-2010|00826T108|586|JPY
AAAAAA||07-Sep-2010|00826T108|586| and so on...

The output file we want is as follows(columns swapped from input file with col5 default to USD in case it is null and date format to convert to YYYYMMDD from dd-mon-yyyy)Below is the format we want---
col0|CU|col2(in format 'yyyymmdd')|col5(if column 5 is null, then default to USD)

Eg -to create output file from above input file-
AAAAAA|CU|20100907|JPY
AAAAAA|CU|20100907|USD

How can we create this output file using above input file through perl script. The perl script will be passed with input file name as a parameter.Please advise
0
 
sunilbainsAuthor Commented:
Can somebody help?
0
 
jeromeeCommented:
Looking into it...
BTW, please provide the name of your employer to make sure that I never work there... too many changes... :-)

Give a couple of minutes.
0
 
jeromeeCommented:
Try this for size...
use strict;

my @monthToMM = qw(jan feb mar apr may jun jul aug sep oct nov dec);
my %monthToMM = map{ $monthToMM[$_] => $_+1 } 0..11;

while( <> ) {
   chomp;
   my($acct, $date, $cty) = (split '\|')[0, 2, 5];
   my($dd, $month, $ccyy) = split /-/, $date;
   $cty ||= 'USD';
   print join("|", $acct, 'CU', sprintf("%d%02d%02d", $ccyy, $monthToMM{lc($month)}, $dd), $cty)."\n";
}

Open in new window

0
 
sunilbainsAuthor Commented:
HI Jerome,
Thanks for reply!
When i run this script, am getting--
Account Code|CU|00000|Currency
P 02134|CU|20100907|USD
How can i remove this line--Account Code|CU|00000|Currency which seems to be header.

thx in advance
0
 
ozoCommented:
perl -F'\|' -lane 'BEGIN{@m{qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec)}=('01'..'12')}
print "$F[0]|CU|$3$m{$2}$1|$F[5]" if $F[2]=~ /(\d+)-(\w+)-(\d+)/' << ENDHERE
kAccount Code|Account Type|Position Date|SEC ID|Quantity|Currency
AAAAAA||07-Sep-2010|00826T108|586|JPY
AAAAAA||07-Sep-2010|00826T108|586| and so on...
ENDHERE
0
 
jeromeeCommented:
use strict;

my @monthToMM = qw(jan feb mar apr may jun jul aug sep oct nov dec);
my %monthToMM = map{ $monthToMM[$_] => $_+1 } 0..11;

<>;
while( <> ) {
   chomp;
   my($acct, $date, $cty) = (split '\|')[0, 2, 5];
   my($dd, $month, $ccyy) = split /-/, $date;
   $cty ||= 'USD';
   print join("|", $acct, 'CU', sprintf("%d%02d%02d", $ccyy, $monthToMM{lc($month)}, $dd), $cty)."\n";
}
0
 
sunilbainsAuthor Commented:
Thanks All.. Its working now.
0
 
jeromeeCommented:
Good to hear.
Happy Perling!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 8
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now