Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sort data in text file in particular format

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort data in text file in particular format
# 1  
Old 08-21-2017
Sort data in text file in particular format
I have to sort below output in text file in unix bash
Code:
20170308
DA,I,113
20170308
PM,I,123
20170308
DA,U,22
20170308
PM,U,123
20170309
DA,I,11
20170309
PM,I,23
20170309
DA,U,123
20170309
PM,U,233

New format should be like below values may change sequence of name will remain same
Code:
20170308
PM,I,123
PM,U,123
DA,I,113
DA,U,22
20170309
PM,I,23
PM,U,233
DA,I,11
DA,U,123

Last edited by vbe; 08-21-2017 at 10:40 AM.. Reason: square brakets for code tags...
# 2  
Old 08-21-2017
maybe:
Code:
awk '/,/ || !a[$0]++' infile

# 3  
Old 08-21-2017
Wouldn't it be nice to tell people the sort criteria?
These 2 Users Gave Thanks to RudiC For This Post:
rbatte1 shamrock
# 4  
Old 08-21-2017
Hello Adfire,

As RudiC already mentioned criteria is not mentioned of sorting, so as per your shown first part of output for 20170308 I am considering that we have to sort by 3rd field of digits for each digit ids(or yyyymmdd). If your Input_file is same as sample shown then following may help you in same.
Code:
awk '/^[0-9]/{val=$0;next} {a[val]=a[val]?a[val] ORS $0:$0} END{for(i in a){print i;system("echo " s1 a[i] s1 " | sort -t, -k3nr")}}' s1="\""   Input_file

Output will be as follows.
Code:
20170308
PM,I,123
PM,U,123
DA,I,113
DA,U,22
20170309
PM,U,233
DA,U,123
PM,I,23
DA,I,11

EDIT: Adding a non-one liner form of solution too here.
Code:
awk '/^[0-9]/{
val=$0;
next
}
{
  a[val]=a[val]?a[val] ORS $0:$0
}
END{
  for(i in a){
    print i;system("echo " s1 a[i] s1 " | sort -t, -k3nr")
}
}
' s1="\""  Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 08-21-2017 at 05:24 PM.. Reason: Adding a non-one liner form of solution too here.
# 5  
Old 08-21-2017
Just guessing what might be the sort criteria, this gets you quite near your desired output:
Code:
awk '
!T[$0]++ && !/,/        {FN = $0
                         if (CMD) close (CMD)
                         CMD = "sort -t, -k1,1r -k2,2 - > " FN
                        }
$0 != FN                {print | CMD}
' file ; for x in 20*; do echo $x; cat $x; done
20170308
PM,I,123
PM,U,123
DA,I,113
DA,U,22
20170309
PM,I,23
PM,U,233
DA,I,11
DA,U,123

This User Gave Thanks to RudiC For This Post:
RavinderSingh13
# 6  
Old 08-23-2017
Quote:
Originally Posted by RavinderSingh13
Hello Adfire,

As RudiC already mentioned criteria is not mentioned of sorting, so as per your shown first part of output for 20170308 I am considering that we have to sort by 3rd field of digits for each digit ids(or yyyymmdd). If your Input_file is same as sample shown then following may help you in same.
Code:
awk '/^[0-9]/{val=$0;next} {a[val]=a[val]?a[val] ORS $0:$0} END{for(i in a){print i;system("echo " s1 a[i] s1 " | sort -t, -k3nr")}}' s1="\""   Input_file

Output will be as follows.
Code:
20170308
PM,I,123
PM,U,123
DA,I,113
DA,U,22
20170309
PM,U,233
DA,U,123
PM,I,23
DA,I,11

EDIT: Adding a non-one liner form of solution too here.
Code:
awk '/^[0-9]/{
val=$0;
next
}
{
  a[val]=a[val]?a[val] ORS $0:$0
}
END{
  for(i in a){
    print i;system("echo " s1 a[i] s1 " | sort -t, -k3nr")
}
}
' s1="\""  Input_file

Thanks,
R. Singh
Hi thanks for your reply it's working now.Now i need to add sum (Total).Example below


Code:
20170308
PM,Total,246
PM,I,123
PM,U,123
DA,Total,135
DA,I,113
DA,U,22
20170309
PM,Total,246
PM,I,23
PM,U,233
DA,Total,134
DA,I,11
DA,U,123

Last edited by jim mcnamara; 08-23-2017 at 09:35 AM..
# 7  
Old 08-24-2017
Quote:
Originally Posted by Adfire
Hi thanks for your reply it's working now.Now i need to add sum (Total).Example below
Code:
20170308
PM,Total,246
PM,I,123
PM,U,123
DA,Total,135
DA,I,113
DA,U,22
20170309
PM,Total,246
PM,I,23
PM,U,233
DA,Total,134
DA,I,11
DA,U,123

Hello Adfire,

Please always use code tags as per forum rules for your commands/codes/Input_files, could you please try following and let me know if this helps you.
Code:
awk -F, '
val && /^[0-9]+/ && val !~ $0{
    print val;
    for(i in b){
       if(a[i]){
         print c[i],a[i];
         delete a[i]
};
       print b[i]
};
    delete b;
    delete c
}
/^[0-9]+/{
    val=$0;
    next
}
{
    c[$1]=$1 FS $2;
    a[$1]+=$3;
    b[$1]=b[$1]?b[$1] ORS $0:$0
}
END{
    print val;
    for(i in b){
       if(a[i]){
         print c[i],a[i];
         delete a[i]
};
    print b[i]
}
}
'   Input_file

Output will be as follows.
Code:
20170308
PM,U 246
PM,I,123
PM,U,123
DA,U 135
DA,I,113
DA,U,22
20170309
PM,U 256
PM,I,23
PM,U,233
DA,U 134
DA,I,11
DA,U,123

Thanks,
R. Singh
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Convert text file data into XL file format

Hi i have a file containing below info and want it to put in xl format 2878042 455134 3333176 24.231979 23.81 2880246 453022 3333268 24.141338 23.81 2879677 453495 3333172 24.310986 23.81i want this data in XL file format and want that my linux system should send me that file on my mail.... (8 Replies)
Discussion started by: scriptor
8 Replies

2. Shell Programming and Scripting

Merge and Sort tabular data from different text files

I have 42 text files; each containing up to 34 lines with following structure; file1 H-01 23 H-03 5 H-05 9 H-02 14 . . file2 H-01 17 H-02 43 H-04 7 H-05 8 H-03 7 . . file3 (6 Replies)
Discussion started by: Syeda Sumayya
6 Replies

3. Shell Programming and Scripting

Sort data file by case

Hello, I'm trying to sort a large data file by the 3rd column so that all of the first words in the 3rd column that are in all uppercase appear before (or after) the non uppercase words. For example, Data file: xxx 12345 Rat in the house xxx 12345 CAT in the hat xxx 12345 Dog in the... (4 Replies)
Discussion started by: palex
4 Replies

4. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

5. Shell Programming and Scripting

Converting text files to xls through awk script for specific data format

Dear Friends, I am in urgent need for awk/sed/sh script for converting a specific data format (.txt) to .xls. The input is as follows: >gi|1234|ref| Query = 1 - 65, Target = 1677 - 1733 Score = 8.38, E = 0.6529, P = 0.0001513, GC = 46 fd sdfsdfsdfsdf fsdfdsfdfdfdfdfdf... (6 Replies)
Discussion started by: Amit1
6 Replies

6. UNIX for Advanced & Expert Users

Sort mixed data file

I have a text file and each field is separated by semicolon ( ; ). Field number 7 is internally separated by comma ( , ) and pipe ( | ) symbol. I want to sort file based on three different fields which are marked in BOLD. Here first BOLD field will have numbers upto the length of 9 characters,... (6 Replies)
Discussion started by: jnrohit2k
6 Replies

7. Shell Programming and Scripting

Sort a the file & refine data column & row format

cat file1.txt field1 "user1": field2:"data-cde" field3:"data-pqr" field4:"data-mno" field1 "user1": field2:"data-dcb" field3:"data-mxz" field4:"data-zul" field1 "user2": field2:"data-cqz" field3:"data-xoq" field4:"data-pos" Now i need to have the date like below. i have just... (7 Replies)
Discussion started by: ckaramsetty
7 Replies

8. Shell Programming and Scripting

Sort a big data file

Hello, I have a big data file (160 MB) full of records with pipe(|) delimited those fields. I`m sorting the file on the first field. I'm trying to sort with "sort" command and it brings me 6 minutes. I have tried with some transformation methods in perl but it results "Out of memory". I was... (2 Replies)
Discussion started by: rubber08
2 Replies

9. UNIX for Dummies Questions & Answers

Arrangeing Swap text data in format

Hi, Please guide me if there is any option of converting text from the following format using regexp etc, 1,a,b,c,d,e,f,d 2,aa,bb,cc,dd,ee,ff 1,6a,6b,6c,6d,6e,6c 2,7a,7b,7c,7d,7e,7f to be group into 1'ns together and 2's together as follows. 1,a,b,c,d,e,f,d 1,6a,6b,6c,6d,6e,6c... (1 Reply)
Discussion started by: shar_prabs
1 Replies

10. UNIX for Dummies Questions & Answers

write data into a text file in bold format

Hi, can anyone help to write data into a text file in bold format and rollback to actual format. Thanks, Regards, Milton Y. (1 Reply)
Discussion started by: miltony
1 Replies
Login or Register to Ask a Question