How to remove all those extra lines other than columns using sed and uniq, like the result given at the end?

Question

.

                             THE EAST INDIA COMPANY PVT LTD                                  
                             THE EAST INDIA COMPANY PVT LTD                                  

            Date : 14/03/2016           Time : 12:45:15          Page :     1

Office  : INDIANA.  Code  :  101      Description  :  TSHIRTS                              Month : 03/2016
Office  : INDIANA.  Code  :  101      Description  :  TSHIRTS                           Month : 03/2016

+=====================================================================================================+
! Slno ! CrId   ! Name Of Customer         ! Item Code & Descrptn     !       Amount  !               !
  Slno   CrId     Name Of Customer           Item Code & Descrptn             Amount                  !
+=====================================================================================================+
!    2 ! 234567 ! CHARLES DICKENS          ! 101   /   TSHIRTS        !    65,805.00  !               !
!    3 ! 345678 ! ROOSEVOLT HUGAS          ! 101   /   TSHIRTS        !    50,140.00  !               !
!    4 ! 456789 ! RICH HILLSIDE            ! 101   /   TSHIRTS        !    48,130.00  !               !
!    5 ! 567890 ! SAMUEL PETER             ! 101   /   TSHIRTS        !    51,750.00  !               !
+-----------------------------------------------------------------------------------------------------+




Prepared by  :                               :                                                  MANAGER
                    THE EAST INDIA COMPANY PVT LTD       
                    THE EAST INDIA COMPANY PVT LTD       

                Date : 14/03/2016          Time : 12:45:14           Page :  2                                                                                                       
Office  : INDIANA.  Code : 102             Description  :  PANTS                        Month : 03/2016
Office  : INDIANA.  Code : 102             Description  :  PANTS                        Month : 03/2016

+=====================================================================================================+
! Slno ! CrId   ! Name Of Customer         ! Item Code & Descrptn     !       Amount  !               !
  Slno   CrId     Name Of Customer           Item Code & Descrptn             Amount                  !
+=====================================================================================================+
!    1 ! 234567 ! CHARLES DICKENS          ! 102   /   PANTS          !       915.00  !               !
!    2 ! 456789 ! RICH HILLSIDE            ! 102   /   PANTS          !     1,610.00  !               !
+-----------------------------------------------------------------------------------------------------+




Prepared by  :                  :                                               MANAGER

I have a file like above. Using sed / uniq / awk how to delete all the headers, banner text etc., as below :

! Slno ! CrId   ! Name Of Customer         ! Item Code & Descrptn     !       Amount  !               ! 
!    2 ! 234567 ! CHARLES DICKENS          ! 101   /   TSHIRTS        !    65,805.00  !               !
!    3 ! 345678 ! ROOSEVOLT HUGAS          ! 101   /   TSHIRTS        !    50,140.00  !               !
!    4 ! 456789 ! RICH HILLSIDE            ! 101   /   TSHIRTS        !    48,130.00  !               !
!    5 ! 567890 ! SAMUEL PETER             ! 101   /   TSHIRTS        !    51,750.00  !               !
! Slno ! CrId   ! Name Of Customer         ! Item Code & Descrptn     !       Amount  !               !
!    1 ! 234567 ! CHARLES DICKENS          ! 102   /   PANTS          !       915.00  !               !
!    2 ! 456789 ! RICH HILLSIDE            ! 102   /   PANTS          !     1,610.00  !               !

Its a lengthy file, but I've posted only a sample here. One more question regarding this, I want to add the values of each customer from the entire list and print Name of Customer and Total Amount, using Awk. Please provide a solution.

Sorry it is a list pertaining to my office, but I've created all dummy items, like replacing Basic pay for TSHIRTS and Fixed Personal Allowance as Pants, etc., in order to hide the identity of my office. I never thought it would give out a false pretext. — adam1969in, Mar 15 '16 at 16:59

score 1 · Answer 1 · answered Mar 15 '16 at 16:24

1

grep '^\!.*\!' < theFile.txt

answered Mar 15 '16 at 16:24

rob mayoff

375,296
67
796
848

Thank you for fast reply. It works fantastic. Please answer to my second question also, given at the end – adam1969in Mar 15 '16 at 16:27
@adam1969in, SO is not a coding service! – user3439894 Mar 15 '16 at 16:31
Sorry, I am a beginner in awk. I don't know the right command to use 'for' loop to repeatedly search for the pattern (in this case, Customer id) and add its values. – adam1969in Mar 15 '16 at 16:36

score 1 · Answer 2 · answered Mar 15 '16 at 16:43

1

awk to the rescue!

$ awk -F! '/^!/{gsub(",","",$6); 
                if($6==$6+0)a[$4]+=$6;
                else{h1=$4;h2=$6}}  
            END{print h1,h2; for(k in a) print k,a[k]}' mess

 Name Of Customer                 Amount
 SAMUEL PETER              51750
 RICH HILLSIDE             49740
 CHARLES DICKENS           66720
 ROOSEVOLT HUGAS           50140
 ADOLF HITLER              57080

perhaps you can look into how to format the amount (hint printf)

answered Mar 15 '16 at 16:43

karakfa

66,216
7
41
56

Very nice one. But why is it printing in reverse order of Customer id. Thank you sir. – adam1969in Mar 15 '16 at 16:55
If you can answer your question about ordering I'll post an solution as a motivation for you to learn more about `awk`. – karakfa Mar 15 '16 at 17:16

How to remove all those extra lines other than columns using sed and uniq, like the result given at the end?

2 Answers2