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

Sort a file on third field

I need to sort a file based on the thrid field; it is time. The fields are delimited by !  So this is the orginial file

act.txt.asc!3!04:01!345!none!act_.txt!act_1.txt
pos_.txt.asc!3!00:01!345!none!pos_eur_.txt!pos_eur_.txt
pos_b.txt.asc!3!09:35!345!none!pos_.txt!pos_eur_ml.txt
bal_.txt.asc!3!00:01!345!none!bal_eur_.txt!bal_eur_.txt
reb_.txt.asc!3!19:01!345!none!reb_eura.txt!reb_eur_.txt
exc_.txt.asc!3!00:01!345!none!exc__e50.txt!exc_eur_.txt

Sort the output file should look like

bal_.txt.asc!3!00:01!345!none!bal_eur_.txt!bal_eur_.txt
pos_.txt.asc!3!00:01!345!none!pos_eur_.txt!pos_eur_.txt
exc_.txt.asc!3!00:01!345!none!exc__e50.txt!exc_eur_.txt
act.txt.asc!3!04:01!345!none!act_.txt!act_1.txt
pos_b.txt.asc!3!09:35!345!none!pos_.txt!pos_eur_ml.txt
reb_.txt.asc!3!19:01!345!none!reb_eura.txt!reb_eur_.txt
0
MatthewF
Asked:
MatthewF
  • 4
  • 3
  • 2
2 Solutions
 
MorcalavinCommented:
Assuming it's always 24 hour time:

%data = ();
while(<DATA>)
{
      chomp($_);
      @array = split(/\!/, $_);
      $time = $array[2];
      $time =~ s/\://;
      push(@{$data{$time}}, $_ . "\n");
      
}


foreach $record (sort keys %data) {
   print "@{$data{$record}}";
}

__DATA__
act.txt.asc!3!04:01!345!none!act_.txt!act_1.txt
pos_.txt.asc!3!00:01!345!none!pos_eur_.txt!pos_eur_.txt
pos_b.txt.asc!3!09:35!345!none!pos_.txt!pos_eur_ml.txt
bal_.txt.asc!3!00:01!345!none!bal_eur_.txt!bal_eur_.txt
reb_.txt.asc!3!19:01!345!none!reb_eura.txt!reb_eur_.txt
exc_.txt.asc!3!00:01!345!none!exc__e50.txt!exc_eur_.txt
0
 
MatthewFAuthor Commented:
No luck.

$opera_exp="opera_exp.log";
open(DATA,"<$opera_exp") || die "Can't open $opera_exp $!\n";
 

%data = ();
while(<DATA)>)
{
      chomp($_);
      @array = split(/\!/, $_);
      $time = $array[2];
      $time =~ s/\://;
      push(@{$data{$time}}, $_ . "\n");
     
}

foreach $record (sort keys %data) {
   print "@{$data{$record}}";
}

close (DATA);


prints DATA)
0
 
MorcalavinCommented:
You have a typo:

while(<DATA)>)
                    !  extra paren!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MatthewFAuthor Commented:
I tried it on a large file where only 1 file was out of order. The file entry out of order was the first, it should have been the last based on the time.  The output prints out most of the lines with a space in front of the line.   The first line was sorted correctly, however, it placed an entry with a 08:00 time phrase directory before
0
 
MorcalavinCommented:
print @{$data{$record}};

use print without the quotes and it should get rid of the blank space.

Can you post the file that isn't sorting correctly?
0
 
MatthewFAuthor Commented:
pos_eur_ml__e203.txt.asc!3!00:01!345!none!pos_eur_ml_mm-dd-yyyy_e203.txt!pos_eur_ml_mm-dd-yyyy_e203.txt
pos_eur_ml__e203b.txt.asc!3!00:01!345!none!pos_eur_ml_mm-dd-yyyy_e203b.txt!pos_eur_ml_mm-dd-yyyy_e203b.txt
bal_eur_ml__e204.txt.asc!3!00:01!345!none!bal_eur_ml_mm-dd-yyyy_e204.txt!bal_eur_ml_mm-dd-yyyy_e204.txt
reb_eur_ml__e10a.txt.asc!3!00:01!345!none!reb_eur_ml_mm-dd-yyyy_e10a.txt!reb_eur_ml_mm-dd-yyyy_e10a.txt
exc_eur_ml__e50.txt.asc!3!00:01!345!none!exc_eur_ml_mm-dd-yyyy_e50.txt!exc_eur_ml_mm-dd-yyyy_e50.txt
bor_eur_ml__e52.txt.asc!3!00:01!345!none!bor_eur_ml_mm-dd-yyyy_e52.txt!bor_eur_ml_mm-dd-yyyy_e52.txt
cor_eur_ml__e54.txt.asc!3!00:01!345!none!cor_eur_ml_mm-dd-yyyy_e54.txt!cor_eur_ml_mm-dd-yyyy_e54.txt
swap_perfs_eur_ml__e501c.txt.asc!3!00:01!345!none!swap_perfs_eur_ml_mm-dd-yyyy_e501c.txt!swap_perfs_eur_ml_mm-dd-yyyy_e501c.txt
swap_perf_eur_ml__e503c.txt.asc!3!00:01!345!none!swap_perf_eur_ml_mm-dd-yyyy_e503c.txt!swap_perf_eur_ml_mm-dd-yyyy_e503c.txt
swap_actd_eur_ml__e505c.txt.asc!3!00:01!345!none!swap_actd_eur_ml_mm-dd-yyyy_e505c.txt!swap_actd_eur_ml_mm-dd-yyyy_e505c.txt
swap_res_eur_ml__e507c.txt.asc!3!00:01!345!none!swap_res_eur_ml_mm-dd-yyyy_e507c.txtt!swap_res_eur_ml_mm-dd-yyyy_e507c.txt
swap_pay_eur_ml__e508c.txt.asc!3!00:01!345!none!swap_pay_eur_ml_mm-dd-yyyy_e508c.txt!swap_pay_eur_ml_mm-dd-yyyy_e508c.txt
swap_payd_eur_ml__e510c.txt.asc!3!00:01!345!none!swap_payd_eur_ml_mm-dd-yyyy_e510c.txt!swap_payd_eur_ml_mm-dd-yyyy_e510c.txt
swap_payr_eur_ml__e511c.txt.asc!3!00:01!345!none!swap_payr_eur_ml_mm-dd-yyyy_e511c.txt!swap_payr_eur_ml_mm-dd-yyyy_e511c.txt
swap_payi_eur_ml__e512c.txt.asc!3!00:01!345!none!swap_payi_eur_ml_mm-dd-yyyy_e512c.txt!swap_payi_eur_ml_mm-dd-yyyy_e512c.txt
swap_val_eur_ml__e516c.txt.asc!3!00:01!345!none!swap_val_eur_ml_mm-dd-yyyy_e516c.txt!swap_val_eur_ml_mm-dd-yyyy_e516c.txt
swap_vali_eur_ml__e519c.txt.asc!3!00:01!345!none!swap_vali_eur_ml_mm-dd-yyyy_e519c.txt!swap_vali_eur_ml_mm-dd-yyyy_e519c.txt
swap_pos_eur_ml__e520c.txt.asc!3!00:01!345!none!swap_pos_eur_ml_mm-dd-yyyy_e520c.txt!swap_pos_eur_ml_mm-dd-yyyy_e520c.txt
swap_valsl_eur_ml__e522c.txt.asc!3!00:01!345!none!swap_valsl_eur_ml_mm-dd-yyyy_e522c.txt!swap_valsl_eur_ml_mm-dd-yyyy_e522c.txt
swap_vald_eur_ml__e518c.txt.asc!3!00:01!345!none!swap_vald_eur_ml_mm-dd-yyyy_e518c.txt!swap_vald_eur_ml_mm-dd-yyyy_e518c.txt
nylife-tdcash..txt.pgp!3!00:01!03:00!none!nylife-tdcash.ddmmyy.hhmm.txt.pgp!nylife-tdcash.ddmmyy.hhmm.txt.pgp
nylife-sdcash..txt.pgp!3!00:01!03:00!none!nylife-sdcash.ddmmyy.hhmm.txt.pgp!nylife-sdcash.ddmmyy.hhmm.txt.pgp
ms130_availability_confirm_.txt.pgp!3!00:01!510!none!ms130_availability_confirm_yyyymmdd.txt.pgp!ms130_availability_confirm_yyyymmdd.txt.pgp
nylifedom-ms8010sw..txt.pgp!3!00:01!510!none!nylifedom-ms8010sw.ddmmyy.hhmm.txt.pgp!nylifedom-ms8010sw.ddmmyy.hhmm.txt.pgp
nylifedom-tdcash..txt.pgp!3!00:01!510!none!nylifedom-tdcash.ddmmyy.hhmm.txt.pgp!nylifedom-tdcash.ddmmyy.hhmm.txt.pgp
nylifedom-sdcash..txt.pgp!3!00:01!510!none!nylifedom-sdcash.ddmmyy.hhmm.txt.pgp!nylifedom-sdcash.ddmmyy.hhmm.txt.pgp
imagnyl_eur.csv.pgp!3!00:01!420!none!imagnyl_eur.csv.pgp!imagnyl_eur.csv.pgp
bor_eur_ml__e52.txt.asc!3!00:01!240!none!bor_eur_ml_mm-dd-yyyy_e52.txt.asc!bor_eur_ml_mm-dd-yyyy_e52.txt.asc
swap_actd_eur_ml__e506c.txt.asc!3!00:01!345!none!swap_actd_eur_ml_mm-dd-yyyy_e506c.txt.asc!swap_actd_eur_ml_mm-dd-yyyy_e506c.txt.asc
xnylftpac1__e201.txt.asc!3!00:01!03:00!none!xnylftpac1_mm-dd-yyyy_e201.txt.asc!xnylftpac1_mm-dd-yyyy_e201.txt.asc
xnylftpac1__e204.txt.asc!3!00:01!03:00!none!xnylftpac1_mm-dd-yyyy_e204.txt.asc!xnylftpac1_mm-dd-yyyy_e204.txt.asc
viteos_crd_daily_ca_eafe30_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_eafe30_yyyymmdd.csv.pgp!viteos_crd_daily_ca_eafe30_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mleurmn_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mleurmn_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mleurmn_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mpeurmn_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mpeurmn_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mpeurmn_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mseurmn_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mseurmn_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mseurmn_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mlasiamn_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mlasiamn_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mlasiamn_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mpasiamn_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mpasiamn_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mpasiamn_yyyymmdd.csv.pgp
viteos_crd_daily_ca_mseafe70_.csv.pgp!3!01:00!420!none!viteos_crd_daily_ca_mseafe70_yyyymmdd.csv.pgp!viteos_crd_daily_ca_mseafe70_yyyymmdd.csv.pgp
xnylftpac1__e203b.txt.asc!3!1:01!03:00!none!xnylftpac1_mm-dd-yyyy_e203b.txt.asc!xnylftpac1_mm-dd-yyyy_e203b.txt.asc
short_rebate.nyl_eur..csv.pgp!3!02:00!420!none!short_rebate.nyl_eur.yyyymmdd.csv.pgp!short_rebate.nyl_eur.yyyymmdd.csv.pgp
financing.nyl_eur..csv.pgp!3!02:00!420!none!financing.nyl_eur.yyyymmdd.csv.pgp!financing.nyl_eur.yyyymmdd.csv.pgp
short_rebate.nyl_asia..csv.pgp!3!03:00!360!none!short_rebate.nyl_asia.yyyymmdd.csv.pgp!short_rebate.nyl_asia.yyyymmdd.csv.pgp
cost_of_carry.nyl_asia..csv.pgp!3!03:00!360!none!cost_of_carry.nyl_asia.yyyymmdd.csv.pgp!cost_of_carry.nyl_asia.yyyymmdd.csv.pgp
financing.nyl_asia..csv.pgp!3!03:00!360!none!financing.nyl_asia.yyyymmdd.csv.pgp!financing.nyl_asia.yyyymmdd.csv.pgp
bo_req_asia_.txt.asc!3!03:00!400!none!bo_req_asia_yyyymmdd.txt.asc!bo_req_asia_yyyymmdd.txt.asc
bo_req_eur_.txt.asc!3!03:00!400!none!bo_req_eur_yyyymmdd.txt.asc!bo_req_eur_yyyymmdd.txt.asc
ml_nylifeemn_outgoing.txt.asc!3!03:00!03:00!none!ml_nylifeemn_outgoing.txt.asc!ml_nylifeemn_outgoing.txt.asc
cost_of_carry.nyl_eur..csv.pgp!3!04:00!420!none!cost_of_carry.nyl_eur.yyyymmdd.csv.pgp!cost_of_carry.nyl_eur.yyyymmdd.csv.pgp
nylife-stockloanextracts..zip.pgp!3!04:30!03:00!none!nylife-stockloanextracts.ddmmyy.hhmm.zip.pgp!nylife-stockloanextracts.ddmmyy.hhmm.zip.pgp
pfpctran.txt!3!04:30!360!none!pfpctranyyyymmdd.txt!pfpctranyyyymmdd.txt
pfpchold.txt!3!04:30!360!none!pfpcholdyyyymmdd.txt!pfpcholdyyyymmdd.txt
bo_req_eur_.txt.asc!3!05:00!130!none!bo_req_ml_eur_yyyymmdd.txt!bo_req_eur_yyyymmdd.txt.asc
viteos_crd_daily_positions_mpeurmn_.csv.pgp!3!05:00!420!none!viteos_crd_daily_positions_mpeurmn_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mpeurmn_yyyymmdd.csv.pgp
viteos_crd_daily_positions_mlasiamn_.csv.pgp!3!05:00!420!none!viteos_crd_daily_positions_mlasiamn_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mlasiamn_yyyymmdd.csv.pgp
viteos_crd_daily_positions_mpasiamn_.csv.pgp!3!05:00!420!none!viteos_crd_daily_positions_mpasiamn_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mpasiamn_yyyymmdd.csv.pgp
viteos_crd_daily_transactions_mlasiamn_.txt.pgp!3!05:00!420!none!viteos_crd_daily_transactions_mlasiamn_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mlasiamn_yyyymmdd.txt.pgp
viteos_crd_daily_transactions_mpasiamn_.txt.pgp!3!05:00!420!none!viteos_crd_daily_transactions_mpasiamn_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mpasiamn_yyyymmdd.txt.pgp
viteos_crd_daily_positions_mseafe70_.csv.pgp!3!05:00!420!none!viteos_crd_daily_positions_mseafe70_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mseafe70_yyyymmdd.csv.pgp
viteos_crd_asia_positions_.txt.pgp!3!07:00!360!none!viteos_crd_asia_positions_yyyymmdd.txt.pgp!viteos_crd_asia_positions_yyyymmdd.txt.pgp
viteos_crd_asia_prices_.txt.pgp!3!07:00!360!none!viteos_crd_asia_prices_yyyymmdd.txt.pgp!viteos_crd_asia_prices_yyyymmdd.txt.pgp
viteos_crd_daily_positions_mleurmn_.csv.pgp!3!07:00!420!none!viteos_crd_daily_positions_mleurmn_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mleurmn_yyyymmdd.csv.pgp
viteos_crd_daily_positions_mseurmn_.csv.pgp!3!07:00!420!none!viteos_crd_daily_positions_mseurmn_yyyymmdd.csv.pgp!viteos_crd_daily_positions_mseurmn_yyyymmdd.csv.pgp
imagnyl_asia.csv.pgp!3!8:00!420!none!imagnyl_asia.csv.pgp!imagnyl_asia.csv.pgp
intl_xnylftpac1__e52.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e52.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e52.txt.asc
intl_xnylftpac1__e54.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e54.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e54.txt.asc
intl_xnylftpac1__e201.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e201.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e201.txt.asc
intl_xnylftpac1__e203.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e203.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e203.txt.asc
intl_xnylftpac1__e203b.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e203b.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e203b.txt.asc
intl_xnylftpac1__e204.txt.asc!3!08:30!400!none!intl_xnylftpac1_mm-dd-yyyy_e204.txt.asc!intl_xnylftpac1_mm-dd-yyyy_e204.txt.asc
viteos_crd_daily_transactions_mleurmn_.txt.pgp!3!09:00!420!none!viteos_crd_daily_transactions_mleurmn_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mleurmn_yyyymmdd.txt.pgp
viteos_crd_daily_transactions_mpeurmn_.txt.pgp!3!09:00!420!none!viteos_crd_daily_transactions_mpeurmn_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mpeurmn_yyyymmdd.txt.pgp
viteos_crd_daily_transactions_mseurmn_.txt.pgp!3!09:00!420!none!viteos_crd_daily_transactions_mseurmn_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mseurmn_yyyymmdd.txt.pgp
pos.nyl_asia.csv.pgp!3!11:00!60!none!pos.nyl_asia.csv.pgp!pos.nyl_asia.csv.pgp
viteos_crd_europe_positions_.txt.pgp!3!11:00!03:00!none!viteos_crd_europe_positions_yyyymmdd.txt.pgp!viteos_crd_europe_positions_yyyymmdd.txt.pgp
viteos_crd_europe_prices_.txt.pgp!3!11:00!03:00!none!viteos_crd_europe_prices_yyyymmdd.txt.pgp!viteos_crd_europe_prices_yyyymmdd.txt.pgp
nyl_asia.trades..csv.pgp!3!13:00!03:00!none!nyl_asia.trades.yyyymmdd.csv.pgp!nyl_asia.trades.yyyymmdd.csv.pgp
nyl_asia.adjustments..csv.pgp!3!13:00!03:00!none!nyl_asia.adjustments.yyyymmdd.csv.pgp!nyl_asia.adjustments.yyyymmdd.csv.pgp
pos.nyl_eur.csv.pgp!3!13:00!03:00!none!pos.nyl_eur.csv.pgp!pos.nyl_eur.csv.pgp
viteos_crd_daily_positions_eafe30_.csv.pgp!3!14:00!480!none!viteos_crd_daily_positions_eafe30_yyyymmdd.csv.pgp!viteos_crd_daily_positions_eafe30_yyyymmdd.csv.pgp
viteos_crd_daily_transactions_eafe30_.txt.pgp!3!14:00!480!none!viteos_crd_daily_transactions_eafe30_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_eafe30_yyyymmdd.txt.pgp
viteos_crd_daily_transactions_mseafe70_.txt.pgp!3!14:00!420!none!viteos_crd_daily_transactions_mseafe70_yyyymmdd.txt.pgp!viteos_crd_daily_transactions_mseafe70_yyyymmdd.txt.pgp
viteos_crd_us_positions_.txt.pgp!3!16:00!03:00!none!viteos_crd_us_positions_yyyymmdd.txt.pgp!viteos_crd_us_positions_yyyymmdd.txt.pgp
viteos_crd_us_prices_.txt.pgp!3!16:00!03:00!none!viteos_crd_us_prices_yyyymmdd.txt.pgp!viteos_crd_us_prices_yyyymmdd.txt.pgp
imagrestricted.txt.pgp!3!17:00!03:00!none!imagrestricted.txt.pgp!imagrestricted.txt.pgp
imagrestricted_deltas.txt.pgp!3!17:00!03:00!none!imagrestricted_deltas.txt.pgp!imagrestricted_deltas.txt.pgp
nyl_eur.trades..csv.pgp!3!18:00!420!none!nyl_eur.trades.yyyymmdd.csv.pgp!nyl_eur.trades.yyyymmdd.csv.pgp
nyl_eur.adjustments..csv.pgp!3!18:00!420!none!nyl_eur.adjustments.yyyymmdd.csv.pgp!nyl_eur.adjustments.yyyymmdd.csv.pgp
newyorklife-swapextract..txt.pgp!3!19:00!03:00!none!newyorklife-swapextract.ddmmyy.hhmm.txt.pgp!newyorklife-swapextract.ddmmyy.hhmm.txt.pgp
nylife-ms8010sw..txt.pgp!3!20:00!505!none!nylife-ms8010sw.ddmmyy.hhmm.txt.pgp!nylife-ms8010sw.ddmmyy.hhmm.txt.pgp
act_eur_ml__e201.txt.asc!3!00:01!345!none!act_eur_ml_mm-dd-yyyy_e201.txt!act_eur_ml_mm-dd-yyyy_e201.txt
0
 
MorcalavinCommented:
The reason it is not sorting correctly is the time stamp is inconsistent.  You have 24 hour time(08:00) until that record, which simply has 8:00(no trailing 0);

Try adding this line before the "push" line:
$time = sprintf("%4d", $time);
0
 
Adam314Commented:
Here is another method (replace data1.txt with the name of your original file, and data2.txt with the name for your sorted data):



#Read data
open(IN,"<data1.txt") or die "input: $!\n";
my @data=<IN>;
close(IN);

#Sort data
@data = map {join("!", @{$_})} sort {$a->[2] cmp $b->[2]} map {[split(/!/, $_)]} @data;

#Write sorted data
open(OUT,">data2.txt") or die "output: $!\n";
print OUT @data;
close(OUT);
0
 
Adam314Commented:
I should have asked... what OS are you on?
If a unix type OS, you can use the sort command.  It would probably be faster.

0

Featured Post

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.

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