sorting in UX

[TrustNo1]

I have played around with sorting this but I can't seem to get it. I think it requires "cut" and "paste" but I have VERY little experience wtih them and I'm under the gun....I'm trying to sort by "Col#1" - "Year" - "Mo" - "Day" - "Time".
The input is;
CIMCCV001;Dec 30, 1999;13:25:51;Dec 30, 1999;13:25:51
CIMCCV002;Jan 12, 2000;06:47:21;Jan 12, 2000;06:47:21
CSICCD002;Dec 29, 1999;03:38:12;Dec 29, 1999;03:41:00
CSICCD002;Dec 31, 1999;04:00:44;Dec 30, 1999;04:32:44
CSICSY000;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY001;Jan 4, 2000;12:05:45;Jan 4, 2000;12:06:22
CSICSY002;Jan 2, 2000;12:06:24;Jan 2, 2000;12:06:24
output should be;
CSICCD002;Dec 29, 1999;03:38:12;Dec 29, 1999;03:41:00
CIMCCV001;Dec 30, 1999;13:25:51;Dec 30, 1999;13:25:51
CSICCD002;Dec 31, 1999;04:00:44;Dec 30, 1999;04:32:44
CSICSY002;Jan 2, 2000;12:06:24;Jan 2, 2000;12:06:24
CSICSY000;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY001;Jan 4, 2000;12:05:45;Jan 4, 2000;12:06:22
CIMCCV002;Jan 12, 2000;06:47:21;Jan 12, 2000;06:47:21

Thanks ~jdk

[TrustNo1]

Darn.....
Output should be;
CIMCCV001;Dec 30, 1999;13:25:51;Dec 30, 1999;13:25:51
CIMCCV002;Jan 12, 2000;06:47:21;Jan 12, 2000;06:47:21
CSICCD002;Dec 29, 1999;03:38:12;Dec 29, 1999;03:41:00
CSICCD002;Dec 31, 1999;04:00:44;Dec 30, 1999;04:32:44
CSICSY000;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY001;Jan 4, 2000;12:05:45;Jan 4, 2000;12:06:22
CSICSY002;Jan 2, 2000;12:06:24;Jan 2, 2000;12:06:24
~jdk

[Michael Tully]

I'm not sure that I understand what you want.

You input from message 1 and the output from message 2 are indentical....

[TrustNo1]

Geeze I must be getting tired, sorry...
The first line should end up being the fifth line on output.
input;
CSICCD002;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CIMCCV001;Dec 30, 1999;13:25:51;Dec 30, 1999;13:25:51
CIMCCV002;Jan 12, 2000;06:47:21;Jan 12, 2000;06:47:21
CSICCD002;Dec 29, 1999;03:38:12;Dec 29, 1999;03:41:00
CSICCD002;Dec 31, 1999;04:00:44;Dec 30, 1999;04:32:44
CSICSY000;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY001;Jan 4, 2000;12:05:45;Jan 4, 2000;12:06:22
CSICSY002;Jan 2, 2000;12:06:24;Jan 2, 2000;12:06:24

output;
CIMCCV001;Dec 30, 1999;13:25:51;Dec 30, 1999;13:25:51
CIMCCV002;Jan 12, 2000;06:47:21;Jan 12, 2000;06:47:21
CSICCD002;Dec 29, 1999;03:38:12;Dec 29, 1999;03:41:00
CSICCD002;Dec 31, 1999;04:00:44;Dec 30, 1999;04:32:44
CSICCD002;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY000;Jan 3, 2000;12:05:30;Jan 3, 2000;12:06:11
CSICSY001;Jan 4, 2000;12:05:45;Jan 4, 2000;12:06:22
CSICSY002;Jan 2, 2000;12:06:24;Jan 2, 2000;12:06:24
~jdk

[Michael Tully]

okay... let's try this. Assume your input is in file1 and you want line 1 from that file to end up in file2 on line 5. Because basically we are doing an alphabetical sort and remove duplicate lines....

cat file1 | sort -u >file2

HTH
Michael

[James R. Ferguson]

Hi:

Try this:

# sort -t\; -k1,9 -kb2.9,2.12 -kM2,3 -kb2.5,2.6 -k3 filein > fileout

Regards!

...JRF...

[TrustNo1]

James & Michael, they both output the same, although James's is a bit more interesting!
outputs;
CSICSY001 9-Nov-2000 18:00:46 9-Nov-2000 18:01:25
CSICSY001 9-Oct-1999 12:46:12 9-Oct-1999 12:46:46
CSICSY001 9-Oct-2000 18:00:46 9-Oct-2000 18:01:19
CSICSY001 9-Sep-1999 18:24:06 9-Sep-1999 18:24:41
CSICSY001 9-Sep-2000 12:47:10 9-Sep-2000 12:47:43
CSICSY002 1-Apr-2000 12:00:34 1-Apr-2000 12:00:34
CSICSY002 1-Aug-2000 18:02:46 1-Aug-2000 18:02:46

But I need to gather them by year, as well as the first column

CSICSY001 9-Sep-1999 18:24:06 9-Sep-1999 18:24:41
CSICSY001 9-Oct-1999 12:46:12 9-Oct-1999 12:46:46
CSICSY001 9-Sep-2000 12:47:10 9-Sep-2000 12:47:43
CSICSY001 9-Oct-2000 18:00:46 9-Oct-2000 18:01:19
CSICSY001 9-Nov-2000 18:00:46 9-Nov-2000 18:01:25
CSICSY002 1-Apr-2000 12:00:34 1-Apr-2000 12:00:34
CSICSY002 1-Aug-2000 18:02:46 1-Aug-2000 18:02:46

Thanks again! ~jdk

[James R. Ferguson]

Hi:

You've presented a differnt input format, so applying the 'sort' I suggested in my first post will *not* do as you first asked.

I suggest you look at the man page for 'sort'.

Regards!

...JRF...

[TrustNo1]

Dang it, I picked up output from an excel experiment James.
Can we try this one more time?
input:
CSICSY001 Nov 9, 2000 18:00:46 9-Nov-2000 18:01:25
CSICSY001 Oct 9, 1999 12:46:12 9-Oct-1999 12:46:46
CSICSY001 Oct 9, 2000 18:00:46 9-Oct-2000 18:01:19
CSICSY001 Sep 19, 1999 18:24:06 9-Sep-1999 18:24:41
CSICSY001 Sep 9, 2000 12:47:10 9-Sep-2000 12:47:43
output;
CSICSY001 Sep 19, 1999 18:24:06 9-Sep-1999 18:24:41
CSICSY001 Oct 9, 1999 12:46:12 9-Oct-1999 12:46:46
CSICSY001 Sep 9, 2000 12:47:10 9-Sep-2000 12:47:43
CSICSY001 Oct 9, 2000 18:00:46 9-Oct-2000 18:01:19
CSICSY001 Nov 9, 2000 18:00:46 9-Nov-2000 18:01:25
~jdk

[TrustNo1]

James, never mind, I messed up again, forgot the delimeters + messed up the "right" side dates.

I'll look at sort again in the morning.......sorry for the waste of time :( ~jdk

[Rodney Hills · Accepted solution]

You need to convert the date into something sortable.

Create a file called sortdate.pl to hold a smal perl script-
BEGIN{@d{qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/}=(1,2,3,4,5,6,7,8,9
,10,11,12);}
{chop;split;($dd,$mth,$yy)=split("-",$_[5]);
printf "%4d%2.2d%2.2d%8s %s\n",$yy,$d{$mth},$dd,$_[6],$_;
}

Then you can do the following-

cat inputfile | perl -n sortdate.pl | sort -kn1,1 | cut -f 2- >outputfile

Hope this helps...

-- Rod Hills

[TrustNo1]

This morning it makes sense.
While asking you guys the "sort" question, a "helper" was importing and exporting the file to/from Excell and saving the results in the UX system under the original name.
The Excell activity had changed the date format!
Man, I knew I was tired last night, but I thought I was loosing it!.
A restore, and the use of your scripts did the trick!

Thanks again for all the help,
I promise to do some serious looking at the "sort" man page.

Have a GREAT weekend!
~jdk