Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Not sure how to describe it but how to format this list so that it print the 1st field once only

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Not sure how to describe it but how to format this list so that it print the 1st field once only
# 1  
Old 05-29-2019
Not sure how to describe it but how to format this list so that it print the 1st field once only
Hi,

Not sure how to describe the problem. But basically, I have this file listing here

Code:
app01_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest10|HOST=10.123.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
app02_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
app03_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
app03_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
app04_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|

Doing sort and uniq on it gives:
Code:
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest10|HOST=10.123.12.23|
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app01_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
app02_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
app02_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
app03_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
app04_app.test.com.ph|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|

I usually import this in MS-Excel and then clear up the first field so that the first field is printed once only in the group of rows/records. This is a manual process, I think I could write a macro to do it but can't work out how.

To avoid having to do this, after sort | uniq, I want the file to be as below:

Code:
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest10|HOST=10.123.12.23|
|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
|PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
|PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|
|PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|

For clarity, this is what it is supposed to look like. The leading spaces from the list below before the | is just an illustration to show what the preferred output is.

Code:
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest10|HOST=10.123.12.23|
                     |PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
                     |PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
                     |PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
                     |PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
                     |PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|
                     |PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|

At the moment, I can only think of reading each line and then comparing current and previous lines and then print the required detail accordingly. The file could be huge though so pretty sure it will take a long time having to do it this way.

Not sure how else to do it. Currently doing so manually doing the import in MS-Excel Smilie
Advice and tips much appreciated. Thanks.
# 2  
Old 05-29-2019
how about:
Code:
awk -F'|' '{a[$1]=($1 in a)?a[$1] OFS substr($0,index($0,OFS)+1):substr($0,index($0,OFS)+1)} END {for(i in a) print i, a[i]}' OFS='|' myFile

# 3  
Old 05-29-2019
How about
Code:
awk -F'|' '($1 in a) {gsub (/./, " ", $1)} !($1 in a) {a[$1]} 1' OFS='|' file2
app01_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest10|HOST=10.123.12.23|
                     |PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
                     |PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
                     |PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
                     |PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
                     |PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
                     |PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|
                     |PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|

Remove the space in the gsub function (setting it empty) if you don't want the indentation.
# 4  
Old 05-30-2019
try:
Code:
awk 'A[$1]++{$1=x}1' FS=\| OFS=\| file2

Code:
|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app02_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\batch.exe|HOST=AKL0TS100|USER=Admtest09|HOST=10.111.12.35|
|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.100.12.23|
|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
app03_app.test.com.ph|PROGRAM=D:\interface\apps\bin32\splogin.exe|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.23|
|PROGRAM=sqlplus|HOST=AKL0TS200|USER=Admtest02|HOST=10.111.12.16|
|PROGRAM=sqlplus|HOST=AKL0TS300|USER=Admtest03|HOST=10.109.12.22|
app04_app.test.com.ph|PROGRAM=plsqldeveloper|HOST=AKL0TS600|USER=Admtest01|HOST=10.123.12.23|
|PROGRAM=SQL Developer|HOST=AKL0TS100|USER=Admtest01|HOST=10.111.12.22|
app05_app.test.com.ph|PROGRAM=Toad|HOST=AKL0TS100|USER=Admtest33|HOST=10.101.12.11|
|PROGRAM=Toad|HOST=AKL0TS500|USER=Admtest01|HOST=10.111.12.23|
app06_app.test.com.ph|PROGRAM=sqlplus|HOST=AKL0TS100|USER=Admtest01|HOST=10.110.12.23|

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Combine Similar Output from the 2nd field w.r.t 1st Field

Hi, For example: I have: HostA,XYZ HostB,XYZ HostC,ABC I would like the output to be: HostA,HostB: XYZ HostC:ABC How can I achieve this? So far what I though of is: (1 Reply)
Discussion started by: alvinoo
1 Replies

2. Shell Programming and Scripting

Command/script to match a field and print the next field of each line in a file.

Hello, I have a text file in the below format: Source Destination State Lag Status CQA02W2K12pl:D:\CAQA ... (10 Replies)
Discussion started by: pocodot
10 Replies

3. UNIX and Linux Applications

Lpr send to print a4 format and print letter format

Hi! How we are? I have an A4 PDF in my server, and i must send it to phisically printer. I use the comand: lpr -P printername -o media=A4 archive.pdf And the printer prints it in letter format, i don't know why. ¿Have ideas or solution? Thanks, my best regards. (6 Replies)
Discussion started by: dcastellini
6 Replies

4. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

5. Shell Programming and Scripting

How to print 1st field and last 2 fields together and the rest of the fields after it using awk?

Hi experts, I need to print the first field first then last two fields should come next and then i need to print rest of the fields. Input : a1,abc,jsd,fhf,fkk,b1,b2 a2,acb,dfg,ghj,b3,c4 a3,djf,wdjg,fkg,dff,ggk,d4,d5 Expected output: a1,b1,b2,abc,jsd,fhf,fkk... (6 Replies)
Discussion started by: 100bees
6 Replies

6. Shell Programming and Scripting

[Solved] Need help changing a field from MM/DD/YY to DD/MM/YY format

Hi, I need help changing a field from MM/DD/YY to DD/MM/YY format. Suppose a file a.csv. The record is "11/16/09","ABC"," 1","EU","520892414","1","600","31351000","1234567","ANR BANK CO. LTD" "11/16/09","PQR"," 2","EU","520892427","1","600","31351000","5467897","ANR BANK CO.... (4 Replies)
Discussion started by: Gangadhar Reddy
4 Replies

7. Shell Programming and Scripting

print whole line if the 1st field contains...

i want to print lines in a file that the 1st field of each line has a Date shape such: yy/mm/dd or on the other hand contains slash "/" . (1 Reply)
Discussion started by: oreka18
1 Replies

8. UNIX for Dummies Questions & Answers

Match pattern in a field, print pattern only instead of the entire field

Hi ! I have a tab-delimited file, file.tab: Column1 Column2 Column3 aaaaaaaaaa bbtomatoesbbbbbb cccccccccc ddddddddd eeeeappleseeeeeeeee ffffffffffffff ggggggggg hhhhhhtomatoeshhh iiiiiiiiiiiiiiii ... (18 Replies)
Discussion started by: lucasvs
18 Replies

9. UNIX for Dummies Questions & Answers

[awk] print from field n to field x

Hi, I'm trying to print every line from first field to the fourth from a file containing more. $ cat input a b c d e f g a b c d e f gI'm trying awk '{for (i=1; i <= NF-3; i++) print $i}' awkTest.datbut it printsa b c d a b c dSo, I easily guess I'm wrong. :) Of course, I want:a b... (5 Replies)
Discussion started by: daPeach
5 Replies

10. 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
Login or Register to Ask a Question