Sorting help

mmittiga17 has asked for the wisdom of the Perl Monks concerning the following question:

I having a problem sorting file. I need to sort by first and last field. I am wondering if the problem is that the first field is alphanumeric? if you see last filed 17 comes before Thanks for any suggestions or help

require Text::CSV_XS;
use File::DosGlob 'glob';
use Text::CSV_XS;
#use strict;
use warnings;
#use Carp;



open (IN,"EIS TR2.CSV");
open(OUT ,">EISTR2.NEW.csv") || die("cant open out");   


       while (defined($line = <DATA>)){
           chomp($line);
           @fields =split( /,/, $line );
           #$newLine=$fields[0-44];           
           $fields[44] =~s/^"00/"/g;           
           $result = join (",", @fields);
           push (@row,$result);
         }
         foreach my $rows ( sort { $a->[0] <=> $b->[0] || $a->[44] <=>
+ $b->[44] } @row ) {
           print OUT "$rows\n";
        }
         exit;
                 
close(OUT);
close(IN);     
        
__DATA__
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"45669NAA4"
+,"45669NAA4","INDYMAC MB 07-AR-CL1A","SEL",(5411000),(4468053.82),55.
+25000,2485584.23,0.00,2485584.23,1.000000,2485584.23,"9309-IN477","93
+83","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.
+00,,"US45669NAA46",55.25000,55.25000,2485584.23,"USD",,,,"INDYMAC MB 
+07-AR-CL1A",,,"SEL","Sell Trade",,"USD","11"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"43741BAA7"
+,"43741BAA7","HMBT 2007- 2007--CL11","XSL",12000000,8306632.44,73.250
+00,(6113135.15),0.00,(6113135.15),1.000000,(6113135.15),"9309-IO584",
+"7256","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC.",
+,,0.00,,"US43741BAA70",73.25000,73.25000,(6113135.15),"USD",,,,"HMBT 
+2007- 2007--CL11",,,"XSL","Cancel Sell Trade",,"USD","12"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"43741BAA7"
+,"43741BAA7","HMBT 2007- 2007--CL11","SEL",(12000000),(8285497.44),73
+.25000,6097518.88,0.00,6097518.88,1.000000,6097518.88,"9309-IO584","7
+256","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC.",,,
+0.00,,"US43741BAA70",73.25000,73.25000,6097518.88,"USD",,,,"HMBT 2007
+- 2007--CL11",,,"SEL","Sell Trade",,"USD","13"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"86360BAL2"
+,"86360BAL2","SARM 2006- 2006--CL6A","XSL",8790285,6025840.40,73.5000
+0,(4479475.85),0.00,(4479475.85),1.000000,(4479475.85),"9309-IN357","
+9383","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,
+0.00,,"US86360BAL27",73.50000,73.50000,(4479475.85),"USD",,,,"SARM 20
+06- 2006--CL6A",,,"XSL","Cancel Sell Trade",,"USD","14"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"86360BAL2"
+,"86360BAL2","SARM 2006- 2006--CL6A","SEL",(8790285),(6025840.40),73.
+50000,4451737.91,0.00,4451737.91,1.000000,4451737.91,"9309-IN357","93
+83","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.
+00,,"US86360BAL27",73.50000,73.50000,4451737.91,"USD",,,,"SARM 2006- 
+2006--CL6A",,,"SEL","Sell Trade",,"USD","15"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"32053BAB0"
+,"32053BAB0","FIRST HORI 2007--CL1A","XSL",15000000,9625272,54.25000,
+(5259596.33),0.00,(5259596.33),1.000000,(5259596.33),"9309-IM262","93
+83","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.
+00,,"US32053BAB09",54.25000,54.25000,(5259596.33),"USD",,,,"FIRST HOR
+I 2007--CL1A",,,"XSL","Cancel Sell Trade",,"USD","16"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-05-2009,Nov-24-2009,"32053BAB0"
+,"32053BAB0","FIRST HORI 2007--CL1A","SEL",(15000000),(9579529.95),54
+.25000,5234556.97,0.00,5234556.97,1.000000,5234556.97,"9309-IM262","9
+383","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0
+.00,,"US32053BAB09",54.25000,54.25000,5234556.97,"USD",,,,"FIRST HORI
+ 2007--CL1A",,,"SEL","Sell Trade",,"USD","17"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JGB4"
+,"05532JGB4","BCAP LLC 2 09-RR-CL14","XSL",374094,374094,47.31038,(17
+8690.48),0.00,(178690.48),1.000000,(178690.48),"9331-IB374","0973","U
+SD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,4
+7.31038,47.31038,(178690.48),"USD",,,,"BCAP LLC 2 09-RR-CL14",,,"XSL"
+,"Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBN3"
+,"05532JBN3","BCAP 2009- 09-RR-CL10","XSL",674957,674957,98.00000,(66
+4017.07),0.00,(664017.07),1.000000,(664017.07),"9331-IB632","0973","U
+SD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,"U
+S05532JBN37",98.00000,98.00000,(664017.07),"USD",,,,"BCAP 2009- 09-RR
+-CL10",,,"XSL","Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JCE2"
+,"05532JCE2","BCAP LLC 2 09-RR-CL14","XSL",748172,748172,47.31038,(73
+6045.38),0.00,(736045.38),1.000000,(736045.38),"9331-IB802","0973","U
+SD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,4
+7.31038,47.31038,(736045.38),"USD",,,,"BCAP LLC 2 09-RR-CL14",,,"XSL"
+,"Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JCA0"
+,"05532JCA0","BCAP LLC 2 09-RR-CL13","XSL",2870283,2870283,98.00000,(
+2823760.50),0.00,(2823760.50),1.000000,(2823760.50),"9331-IB354","097
+3","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.0
+0,,,98.00000,98.00000,(2823760.50),"USD",,,,"BCAP LLC 2 09-RR-CL13",,
+,"XSL","Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBA1"
+,"05532JBA1","BCAP LLC 2 09-RR-CL7A","XSL",794212,794212,98.00000,(78
+1339.15),0.00,(781339.15),1.000000,(781339.15),"9331-IB607","0973","U
+SD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,9
+8.00000,98.00000,(781339.15),"USD",,,,"BCAP LLC 2 09-RR-CL7A",,,"XSL"
+,"Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBJ2"
+,"05532JBJ2","BCAP LLC 2 09-RR-CL9A","XSL",1057751,1057751,98.00000,(
+1040606.62),0.00,(1040606.62),1.000000,(1040606.62),"9331-IB708","097
+3","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.0
+0,,,98.00000,98.00000,(1040606.62),"USD",,,,"BCAP LLC 2 09-RR-CL9A",,
+,"XSL","Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBW3"
+,"05532JBW3","BCAP LLC 2 09-RR-CL12","XSL",1374994,1374994,98.50000,(
+1359582.61),0.00,(1359582.61),1.000000,(1359582.61),"9331-IB579","097
+3","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.0
+0,,,98.50000,98.50000,(1359582.61),"USD",,,,"BCAP LLC 2 09-RR-CL12",,
+,"XSL","Cancel Sell Trade",,"USD","2"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JGB4"
+,"05532JGB4","BCAP LLC 2 09-RR-CL14","SEL",(374094),(374094),47.31038
+,178685.52,0.00,178685.52,1.000000,178685.52,"9331-IB374","0973","USD
+","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,47.
+31038,47.31038,178685.52,"USD",,,,"BCAP LLC 2 09-RR-CL14",,,"SEL","Se
+ll Trade",,"USD","3"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JCE2"
+,"05532JCE2","BCAP LLC 2 09-RR-CL14","SEL",(748172),(747212.10),47.31
+038,736044.63,0.00,736044.63,1.000000,736044.63,"9331-IB802","0973","
+USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,
+47.31038,47.31038,736044.63,"USD",,,,"BCAP LLC 2 09-RR-CL14",,,"SEL",
+"Sell Trade",,"USD","3"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JCA0"
+,"05532JCA0","BCAP LLC 2 09-RR-CL13","SEL",(2870283),(2737889.47),98.
+00000,2693512.85,0.00,2693512.85,1.000000,2693512.85,"9331-IB354","09
+73","USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.
+00,,,98.00000,98.00000,2693512.85,"USD",,,,"BCAP LLC 2 09-RR-CL13",,,
+"SEL","Sell Trade",,"USD","3"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBA1"
+,"05532JBA1","BCAP LLC 2 09-RR-CL7A","SEL",(794212),(776543.39),98.00
+000,763956.91,0.00,763956.91,1.000000,763956.91,"9331-IB607","0973","
+USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,,
+98.00000,98.00000,763956.91,"USD",,,,"BCAP LLC 2 09-RR-CL7A",,,"SEL",
+"Sell Trade",,"USD","3"
"737EP0","CANYON CBMF",Dec-07-2009,Nov-27-2009,Nov-27-2009,"05532JBN3"
+,"05532JBN3","BCAP 2009- 09-RR-CL10","SEL",(674957),(634736.10),98.00
+000,624448.09,0.00,624448.09,1.000000,624448.09,"9331-IB632","0973","
+USD","Margin","SECURITY",0.00,"DEBT","BARCLAYS CAPITAL INC",,,0.00,,"
+US05532JBN37",98.00000,98.00000,624448.09,"USD",,,,"BCAP 2009- 09-RR-
+CL10",,,"SEL","Sell Trade",,"USD","3"
[download]

Comment on Sorting help Download Code

Replies are listed 'Best First'.
Re: Sorting help by kennethk (Abbot) on Dec 10, 2009 at 16:47 UTC
There are two built in sorting operators in Perl - `<=>` and `cmp` - which are discussed in Equality Operators. `<=>` is used for numerical comparison and `cmp` is used for string comparison. Since the data from your first column are string data, you should use `cmp`. If neither operator fits your requirements, you can design your own comparison function, following the instructions in sort. On a side note, why are you using Text::CSV_XS if you are not using any of the code? Why are you both requiring and using the module when these are redundant operations? Why are you manually stripping leading zeros and why are you still feeding the numeric comparison operator values with leading quotes, even after I pointed out (Re^2: How do I sort a CSV file on multiple columns?) that it doesn't work the way you expect? Why are you posting code that has `use strict` commented out? See How do I post a question effectively?. Update: I also just noticed that your `@rows` array contains strings, not array refs like your sort function expects. The line `$result = join (",", @fields);` concatenates all of the values in the array `@fields` into a single, comma-separated string. You then attempt to access each of those strings as if they were array refs, an issue that would have been caught by `use strict;`. To understand how to use references in Perl, read perlref, perlreftut and/or perllol. Compare your code to the code I've already referenced above to see a prototype of how you should be accessing these data structures.	[reply] [d/l] [select]
Re: Sorting help by gmargo (Hermit) on Dec 10, 2009 at 19:55 UTC
From yesterday's thread How do I sort a CSV file on multiple columns?, I thought you were using the Text::CSV or Text::CSV_XS module. In this code it looks like you're parsing it by hand. The reason your sort does not work is because you `join()` the fields into one long string before pushing it onto the array. Then you try to sort on the separated fields that no longer exist. However even if you remove the join you'll run into problems because of all the quoting left in the fields. Here's how you could be doing this using Text::CSV_XS: #!/usr/bin/perl -w use strict; use warnings; use diagnostics; use Text::CSV_XS; my @rows; my $csv = Text::CSV_XS->new ( { binary => 1 } ) # should set binary a +ttribute. or die "Cannot use CSV: ".Text::CSV->error_diag (); my $expcol = 45; # Expect this many columns my $lineno = 0; while (<DATA>) { my $line = $_; chomp($line); $lineno++; #print "$lineno: $line\n"; if (! $csv->parse($line)) { warn "$lineno: skip due to parse error: ".$csv->input_error(); next; } my @columns = $csv->fields(); warn "$lineno: unexpected number of columns=".scalar(@columns).", +was expecting $expcol" if scalar(@columns) != $expcol; push (@rows, \@columns); } $lineno = 0; foreach my $row ( sort { $a->[0] cmp $b->[0] \|\| $a->[44] <=> $b->[44] } @rows ) { $lineno++; print "$lineno: ".join(", ", @$row)."\n"; } [download]	[reply] [d/l] [select]
Re: Sorting help by cdarke (Prior) on Dec 10, 2009 at 17:06 UTC
I started writing a custom sort function for you, but discovered that the first field is the same on all the records, so the actual sort is just on the last field (easier to use `[-1]` by the way). Not only that, I have no idea if the first field is fixed length, or what. What are the possible formats? With the final field, is it only numeric (will have to remove the quotes characters)?	[reply] [d/l]
Re^2: Sorting help by mmittiga17 (Scribe) on Dec 10, 2009 at 17:09 UTC
Thanks, that was just a short snippet of code. the data itself is very long and has many different values in field one. Thanks for your help	[reply]
Re^3: Sorting help by GrandFather (Saint) on Dec 10, 2009 at 22:21 UTC
You will get better results from us if you expend just a little more effort yourself. In this case winnowing your sample data down to just enough data to show the important test cases (probably three records in this case) would have helped a lot. Even better would be to reduce the problem to the essential components (sort by two fields) and provide suitable data for the sample problem rather than a mass of 'real' data that obscures the problem and the solution. True laziness is hard work	[reply]


Syntactic Confectionery Delight
	PerlMonks