Visit Our UNIX and Linux User Community


Sort two columns in a field, one of them being a date


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort two columns in a field, one of them being a date
# 1  
Old 07-28-2009
Sort two columns in a field, one of them being a date

Hi,

I have a set of columns in a csv file, my first row being an integer and 2nd being a date. I want to first sort it using the first column and then by the second.

for e.g. i have ,

1234,09/05/2009,hi
5678,01/01/2008,hi
1234,11/03/2006,hello
5678,28/07/2010,hello

i tried this but wont work because the separator is "," :::

sort -t "," -k1 -k2.7,2.10 -k2.4,2.5M -k2.1,2.2 test.csv > hi.csv

Thanks in advance,
SD
# 2  
Old 07-28-2009
perl may be easy to handle it.

Code:
my @arr=<DATA>;
print map {$_->[0]}
	sort {$a->[1] <=> $b->[1] || $a->[2] <=> $b->[2]}
		map {
			my @tmp=split(",",$_);
			$tmp[1]=~s:([0-9]{2})/([0-9]{2})/([0-9]{4}):$3.$2.$1:e;
			[$_,$tmp[0],$tmp[1]];
		} @arr;
__DATA__
1234,09/05/2009,hi
5678,01/01/2008,hi
1234,11/03/2006,hello
5678,28/07/2010,hello

# 3  
Old 07-28-2009
If I'm not missing something:

Code:
sort -t/ -k1n -k3n -k2n  infile


Last edited by radoulov; 07-28-2009 at 07:18 AM.. Reason: corrected, the previous version was wrong ...
# 4  
Old 07-28-2009
Code:
sort  -t/ -k1.1,1.4n -k3n -k2n -k1.6,1.8n test.txt

This should also works fine.. rite..

Please correct me if i am wrong.

Last edited by radoulov; 07-28-2009 at 07:07 AM.. Reason: added code tags
# 5  
Old 07-28-2009
Quote:
Originally Posted by mac4rfree
Code:
sort  -t/ -k1.1,1.4n -k3n -k2n -k1.6,1.8n test.txt

This should also works fine.. rite..

Please correct me if i am wrong.
Hi ,

This will just use the "/" as a separator .. what i am trying to figure out is first sort the 1st column so lets say all 1234 together then within those, the date should be sorted.

Regards
SD
# 6  
Old 07-28-2009
Code:
 sort -t"," -k1.1,1.4  -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 file

or


Code:
sort -t"," -k1,1n  -k2.7,2.10 -k2.4,2.5 -k2.1,2.2 file


Previous Thread | Next Thread
Test Your Knowledge in Computers #780
Difficulty: Easy
Bill Gates was born in Seattle, Washington, on October 28, 1945.
True or False?

10 More Discussions You Might Find Interesting

1. AIX

Sort by date field in AIX

I wanted to sort the below data on 4th field(comma seperator) based on month and date and time on AIX OS. Input data: 3,AJ,30 Jul 06:30,30 Jul 06:30 5,AJ,30 Jul 06:30,30 Jul 06:49 10,AJ,30 Jul 06:30,02 Jan 05:41 4,AJ,30 Jul 06:30,30 Jul 06:36 2,AJ,30 Jul 06:30,28 Jul 06:45 9,AJ,30 Jul... (2 Replies)
Discussion started by: Amit Joshi
2 Replies

2. UNIX for Dummies Questions & Answers

UNIX Sort on a field that spans multiple columns

New to unix. I need to sort the records of a file by a control number field. That field is in POS 16 through 28. How do I do that? There are no delimiters, or spaces to separate fields. See example below. Each line is a record. REC1CCYYMMDD0018888888888888ABCDE... (1 Reply)
Discussion started by: jclanc8
1 Replies

3. Shell Programming and Scripting

Sort help: How to sort collected 'file list' by date stamp :

Hi Experts, I have a filelist collected from another server , now want to sort the output using date/time stamp filed. - Filed 6, 7,8 are showing the date/time/stamp. Here is the input: #---------------------------------------------------------------------- -rw------- 1 root ... (3 Replies)
Discussion started by: rveri
3 Replies

4. UNIX for Advanced & Expert Users

Sort in fields date and columns.

Hi colleagues, I have this output, i need sort for 7th and 8th columns, the column 7th is date mm/dd/yyyy format and column 8th is time hh:mm:ss.number PRUEBA 1263 0007 1 0 7131292 03/25/2013 16:43:04.159976 PROCESS PRUEBA1 666 0146 1 0 11600064 ... (1 Reply)
Discussion started by: systemoper
1 Replies

5. Shell Programming and Scripting

Sorting Date Field with Sort -k :/

SOLVED : (17 Replies)
Discussion started by: Glitch100
17 Replies

6. UNIX for Dummies Questions & Answers

Sort field by date mm/dd/yyyy

Hello Group, I would like to sort the below file by date (first year then month and day) and I used the following command but it does not work sort -n -t"/" -k3 -k1 -k2 "sample original file" 12/28/2009,1.0353 12/31/2009,1.0357 12/30/2009,1.0364 12/29/2009,1.0366 12/24/2009,1.0386... (6 Replies)
Discussion started by: csierra
6 Replies

7. Shell Programming and Scripting

Sort alpha on 1st field, numerical on 2nd field (sci notation)

I want to sort alphabetically on the first field and sort in descending numerical order on the 2nd field. With a normal "sort -r -n" it does this: abc ||| 5e-05 ||| bla abc ||| 3 ||| ble def ||| 1 ||| abc def ||| 0.2 ||| def As you can see it ignores the fact that 5e-05 is actually 0.00005... (1 Reply)
Discussion started by: FrancoisCN
1 Replies

8. Shell Programming and Scripting

How to Sort files on date field

:cool: Hi all, I have a pecular issue in sorting these files in Solaris environment. All the below files are modified on November 4th, but I want to sort these files as per date column (eg: 01May07_1623 = ddmmmyy_hhmm) Nov 4 18:27 SONYELEC00.GI22973.01May07_1623.gpg Nov 4 18:27... (4 Replies)
Discussion started by: shivaastrogun
4 Replies

9. Shell Programming and Scripting

How to sort a field in a file having date values

Hi All, I am having a pipe delimited file .In this file the 3rd column is having date values.I need to get the min date and max date from that file. I have used cut -d '|' test.dat -f 3|sort -u But it is not sorting the date .How to sort the date column using unix commands Thanks ... (4 Replies)
Discussion started by: risshanth
4 Replies

10. Shell Programming and Scripting

sort columns by field

hello, i have a table contain many columns delimited by blank. i want to sort this table by the 2 columns and 3 one and i want to keep the first line inchanged? how can i do using the sort command? thanks table like : field1 field2 field3 field4 x y z b t h r n .. (4 Replies)
Discussion started by: kamel.seg
4 Replies

Featured Tech Videos