Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sort on mm/dd/yyyy csv

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort on mm/dd/yyyy csv
# 1  
Old 04-18-2013
Sort on mm/dd/yyyy csv
I have the following csv data that I would like to sort on the 4th field by date
from oldest to newest.

data:
Code:
,,,02/06/2012,Carry H,Add,Add,Sys_RTH,111.222.333.777
4/7/12,,,03/03/2013,Parry H,Add,Add,Sys_RTH,111.222.333.776
,,,12/06/2012,Harry H,Add,Add,Sys_RTH,111.222.333.778
4/5/13,B.Bee,,09/03/2012,Marry H,Add,Add,Sys_RTH,111.222.333.770

I use the following sort command but I cannot get it to work properly:
Code:
sort -t"," +3  -k1.7,1.10 -k1.1,1.2 -k1.4,1.5  data

- Delimeter is ","
- Skip the first 3 fields to sort on the 4th field
- At 4th field, sort "k"olumn 1 for chars 7 thru 10 ( yyyy )
- Next sort key is "k"olumn 1 for chars 1 and 2 ( mm )
- Final key is "k"olumn 1 for chars 4 and 5 ( dd )

Here's the result I get:

Code:
,,,02/06/2012,Carry H,Add,Add,Sys_RTH,111.222.333.777
4/7/12,,,03/03/2013,Parry H,Add,Add,Sys_RTH,111.222.333.776
4/5/13,B.Bee,,09/03/2012,Marry H,Add,Add,Sys_RTH,111.222.333.770
,,,12/06/2012,Harry H,Add,Add,Sys_RTH,111.222.333.778

Desired result:
Code:
,,,02/06/2012,Carry H,Add,Add,Sys_RTH,111.222.333.777
4/5/13,B.Bee,,09/03/2012,Marry H,Add,Add,Sys_RTH,111.222.333.770
,,,12/06/2012,Harry H,Add,Add,Sys_RTH,111.222.333.778
4/7/12,,,03/03/2013,Parry H,Add,Add,Sys_RTH,111.222.333.776

Anyone see what I'm doing wrong?
Thanks,
wbrunc
# 2  
Old 04-18-2013
I wouldn't trust + to work with modern versions of sort, it's an archaic syntax that's not always documented or even implemented now. Try leaving it off and doing -k4 instead of -k1
# 3  
Old 04-18-2013
As Corona688 said, get rid of the old syntax (+n), just use the fourth field:
Code:
sort -t, -k4.7,4.10n -k4.1,4.2n -k4.4,4.5n infile

# 4  
Old 04-18-2013
Thanks, it's closer but now just off by one line.

Code:
sort -t , -k4.7,1.10 -k4.1,1.2 -k4.4,1.5  data
,,,02/06/2012,Carry H,Add,Add,Sys_RTH,111.222.333.777
,,,12/06/2012,Harry H,Add,Add,Sys_RTH,111.222.333.778
4/5/13,B.Bee,,09/03/2012,Marry H,Add,Add,Sys_RTH,111.222.333.770
4/7/12,,,03/03/2013,Parry H,Add,Add,Sys_RTH,111.222.333.776

---------- Post updated at 03:53 PM ---------- Previous update was at 03:50 PM ----------

Thanks that fixed it!!

Code:
sort -t, -k4.7,4.10n -k4.1,4.2n -k4.4,4.5n data

,,,02/06/2012,Carry H,Add,Add,Sys_RTH,111.222.333.777
4/5/13,B.Bee,,09/03/2012,Marry H,Add,Add,Sys_RTH,111.222.333.770
,,,12/06/2012,Harry H,Add,Add,Sys_RTH,111.222.333.778
4/7/12,,,03/03/2013,Parry H,Add,Add,Sys_RTH,111.222.333.776

This User Gave Thanks to wbrunc For This Post:
Corona688
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Date format YYYY/MM/DD to DD/MM/YYYY

I am getting output of YYYY-MM-DD and want to change this to DD/MM/YYYY. When am running the query in 'Todd' to_date(column_name,'DD/MM/YYYY') am getting the required o/p of DD/MM/YYYY, But when am executing the same query(Netezza) in linux server(bash) am getting the output of YYYY-MM-DD file... (3 Replies)
Discussion started by: Roozo
3 Replies

2. Shell Programming and Scripting

Converting date DD MM YYYY to DD MON YYYY

Hello, I am writing a script that parses different logs and produces one. In the source files, the date is in DD MM YYYY HH24:MI:SS format. In the output, it should be in DD MON YYY HH24:MI:SS (ie 25 Jan 2010 16:10:10) To extract the dates, I am using shell substrings, i.e.: read line ... (4 Replies)
Discussion started by: Adamm
4 Replies

3. Shell Programming and Scripting

change date format from yyyy/mm/dd to dd/mm/yyyy

(Attention: Green PHP newbie !) I have an online inquiry form, delivering a date in the form yyyy/mm/dd to my feedback form. If the content passes several checks, the form sends an e-mail to me. All works fine. I just would like to receive the date in the form dd/mm/yyyy. I tried with some code,... (6 Replies)
Discussion started by: keyboarder
6 Replies

4. 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

5. Shell Programming and Scripting

Sed: zero-padding dates (or: convert d/m/yyyy to dd/mm/yyyy)

Hi all I have some pipe-separated data in the form: 5/12/2008 00:00:00|31/1/2009 00:00:00|SOMESTUFF|OTHERSTUFF 12/31/2008 00:00:00|15/1/2009 00:00:00|MORESTUFF|REMAININGSTUFF 1/1/1023 00:00:00|16/5/2047 00:00:00|THEREST|YETMORE I need to zero-pad the single-digit days and months, using... (3 Replies)
Discussion started by: jgrogan
3 Replies

6. Shell Programming and Scripting

sort for timestamp information-YYYY-MM-DD HH:MM:SS

Dear all, Please advice how do I sort a file based on timestamp information. I want to sort the second column in asc/desc order which has timestamp information in format YYYY-MM-DD HH:MM:SS Example File - Input.txt contains cat ss.txt 100|2009-03-30 11:38:43 141|2009-06-01 12:12:01... (1 Reply)
Discussion started by: sureshg_sampat
1 Replies

7. Shell Programming and Scripting

Convert DD-MMM-YYYY to MM-DD-YYYY

I have a file which has 100k+ records like this abc,05-JUN-1974,def,lkj,aaa def,11-SEP-1975,ghj,dis,dea I want to convert ex 05-JUN-1974 to 06/05/1974 Please help me with awk script to convert the whole file into MM-DD-YYYY Thank you! (2 Replies)
Discussion started by: nuthalapati
2 Replies

8. Shell Programming and Scripting

converting the date field from dd/mm/yyyy to yyyy/mm/dd

How to convert the date field from dd/mm/yyyy to yyyy/mm/dd in unix my script will generate text file which have two fields one is date and another is name of the server for example this is sample date which I have to sort based on older to newer date the problem is when I found out sort will... (4 Replies)
Discussion started by: pareshan
4 Replies

9. Shell Programming and Scripting

Change Date from dd-mmm-yyyy to mm/dd/yyyy

I want to change a date from format dd-mmm-yyyy to mm/dd/yyyy. Is there a way to do this with sed or do you have to write a case statement to convert JAN to 01? Thanks (9 Replies)
Discussion started by: stringzz
9 Replies
Login or Register to Ask a Question