Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have along list that created in /var/tmp/file.txt from some script (in Solaris machine) the following list have 4 fields

please advice how to sort the list according to the following TIMESTAMP ( by sort command or other solaris command )

for example the date & time 15-10-2009 08:29:18 should be before 15-10-2009 08:29:10 ... etc

example of file.txt ( not sorted file )

  PHONE_NUMBER         TIMESTAMP                   ID  TYPE
  -------------------- -------------------        ---- -------------- 
  972544111222         15-10-2009 08:29:18         20  sharp_gx10
  33633333333          24-09-2009 16:17:45         20  other_mm_phone
  841990000043         08-10-2009 09:04:38         60  other_mm_phone
  972541230001         08-10-2009 14:23:48         20  other_mm_phone

. . . .

I try the sort command but not clearly why file.txt not sorted

sort -t' ' -k2.7,2.10n -k2.4,2.5n -k2.1,2.2n -k3 /var/tmp/file.txt

we get the output:

  5938123456789141     12-10-2009 13:09:22         20 other_phone
  5511223322332233     07-03-2012 08:13:43         20 other_phone


  888888               10-02-2012 14:13:58         60 LegacyPhone
  111111               10-02-2012 14:13:59         60 LegacyPhone
  777777               10-02-2012 14:13:59         60 LegacyPhone
  999999               16-02-2012 14:07:32         10 other_phone
  87654321             11-10-2009 09:39:37         10 other_phone
share|improve this question

2 Answers 2

up vote 3 down vote accepted
sort -t' ' -k2.7,2.10n -k2.4,2.5n -k2.1,2.2n -k3

If you have control of the original script so you can print the date as YYYY-MM-DD then it would sort naturally so we would not need to pick apart the year, month and date fields in the sort command, but could just use -k2 in the same way we just use -k3 for the timestamp.

Or if I have counted your fixed width format correctly,

sort -k1.28,1.31n -k1.25,1.26n -k1.22,1.23n -k3

Or if you are not using fixed widths and have variable white space, then (-b to ignore spaces but note no -n)

sort -b -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 -k3

Or to sort only part of the file (and really it would be a lot easier simply to alter the program creating this data in the first place):

(head -2 file; /usr/xpg4/bin/tail -n +3 file |sort -b -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 -k3)
share|improve this answer
    
see my update in my question ( this isnt work well ) –  yael Apr 17 '12 at 8:11
    
The problem is the -t' ' which says use ' ' as the field separator. Clearly you are not using a space. You could simply adjust the values I have given since you seem to have a fixed width format. –  ramruma Apr 17 '12 at 8:20
    
what you mean using? , I type exactly the sort -t' ' -k2.7,2.10n -k2.4,2.5n -k2.1,2.2n -k3 /var/tmp/file.txt ( in my solaris machine ) –  yael Apr 17 '12 at 8:24
    
See update, since you seem to have fixed-width columns. –  ramruma Apr 17 '12 at 8:38
    
ok now its work - just last question if the date and time in the first field - what change need to do in your syntax ? –  yael Apr 17 '12 at 8:47

With complex cases like this I tend to create a temporary MySQL database and let that do the sorting or other filtering. Often it's easier to just do something like

CREATE TABLE temp_sort_table(phone_number VARCHAR(16), timestamp DATETIME, id INT, type VARCHAR(16))

or so and then just import the data from the text file to your database:

LOAD DATA INFILE 'file.txt' INTO TABLE temp_sort_table

Then you can easily do things like

SELECT * FROM temp_sort_table ORDER BY timestamp DESC;

Sure, you can pass all kinds of parameters to sort command or apply some awk/Perl/Python voodoo to get all this done without a database. With complex text files it's just easier to use a database, in my opinion.

share|improve this answer
    
did you have example with sort command - I think its better –  yael Apr 17 '12 at 7:32
    
If you're going to load this into a database, you might as well use an SQLite one. Its a smaller footprint, and it doesn't require all of the security bits arround it. –  monksy Feb 8 '13 at 0:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.