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

newbie_01 · May 29, 2019, 1:29pm

Hi,

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

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:

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:

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.

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
Advice and tips much appreciated. Thanks.

vgersh99 · May 29, 2019, 1:48pm

how about:

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}' OFS='|' myFile

RudiC · May 29, 2019, 3:57pm

How about

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.

Scrutinizer · May 30, 2019, 4:50am

try:

awk 'A[$1]++{$1=x}1' FS=\| OFS=\| file2

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