1

I have full list of files which contains this below texts.

Sun Aug 22 19:00:00 2021
        User-Name = "407359687"
        Acct-Status-Type = Interim-Update
        Acct-Output-Octets = 3263901190
        Acct-Session-Id = "PPP3092201SSG0001006b0a55AABODS"
        Acct-Session-Time = 1146851
        Acct-Output-Gigawords = 15
        Event-Timestamp = "Aug 22 2021 18:55:32 +08"
        Timestamp = 1629630000

My goal is to get the important rows and save it into new CSV file and i'm using this below AWK command to sort the values from text but I don't know to write it into CSV file.

awk '{if ($1 == "User-Name")
    {start=1; wholeLine=""; wholeLine = wholeLine$3;}
    if ($1$2$3 =="Acct-Status-Type=Interim-Update"||$1$2$3 =="Acct-Status-Type=Stop")
    {wholeLine=wholeLine","$3;}
    else if ($1$2$3 =="Acct-Status-Type=Start")
    {start=0;wholeLine=""}
    if (($1=="Acct-Output-Octets")&&(start==1))
    {wholeLine=wholeLine","$3;}
    if (($1=="Acct-Session-Id")&&(start==1))
    {wholeLine=wholeLine","$3;}
    if (($1=="Acct-Session-Time")&&(start==1))
    {wholeLine=wholeLine","$3;}
    if (($1=="Acct-Output-Gigawords")&&(start==1)) 
    {wholeLine=wholeLine","$3;} 
    if (($1=="Event-Timestamp")&&(start==1))
    {timeStamp="";timeStamp=$3" "$4" "$5" "$6" "$7;wholeLine=wholeLine","timeStamp}
    if (($1=="Timestamp")&&(start==1))
    {wholeLine=wholeLine","$3;}
    if (($1=="")&&(start==1))
    {start=0;print wholeLine}}' /home/file/detail-20210822

My expected CVS result should be look like this.

"405947674",Interim-Update,1079493624,"PPP3082110SSG000100be4a72AAAk5Y",25440,0,"Aug 22 2021 19:00:43 +08",1629630315
4
  • 2
    Not sure if I understand you. Are you saying that the program produces the correct output, but you need the output to go to a file? In this case, use the shell's output redirection: awk ... > output.csv. If that is not your problem, what is it? Sep 8, 2021 at 5:19
  • 4
    By the way, rather than a chain of if ... else if ... else if ..., use awk's patterns, e.g. /$1=="User-Name"/ { start=$1; ... }. Sep 8, 2021 at 5:21
  • 2
    The most important thing in your input file in regards to parsing it is whatever separates the records but you haven't shown us that as you only posted 1 record. Please edit your question to show at least 2 records so we can see what separates them.
    – Ed Morton
    Sep 8, 2021 at 14:11
  • 1
    Can you explain your filtering requirements? Are you not interested in extracting each 8-line record into a CSV row, only some 8-line records? Sep 9, 2021 at 15:04

5 Answers 5

2

Assuming that the eight fields of each record are always present, are in the correct order, and do not need further processing to be valid in a CSV file (i.e. no extra quoting or escaping is needed):

sed -n 's/^[^=]*= //p' file | paste -d , - - - - - - - -

This removes any line that does not contain any equal signs followed by space (or lines where the = substring doesn't contain the first equal sign on the line), then strips off any text up to the space after the first equal sign.

It then uses paste to create eight comma-delimited columns of the remaining data.

Testing on a file containing your example data twice:

$ sed -n -e 's/^[^=]*= //p' file | paste -d , - - - - - - - -
"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000
"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000

Removing any line with Start in the second column (to filter out sections that had Acct-Status-Type = Start in the original data) could be done by piping the result through

awk -F , '$2 != "Start"'
2
  • This is elegant, but the the original question has the requirement that blocks with "Acct-Status-Type=Start" are ignored. Sep 9, 2021 at 13:47
  • @MarkStosberg Good catch. See update at the end.
    – Kusalananda
    Sep 9, 2021 at 14:28
1

Here's how to really convert your file to CSV, assuming the only thing that separates records is that timestamp at the top of your sample input and that every record contains all the same tags (aka names/keys/the left side of the = signs in your data):

$ cat tst.awk
BEGIN { OFS="," }
/=/ {
    gsub(/^[[:space:]]+|[[:space:]]+$/,"")
    tag = val = $0
    sub(/[[:space:]]*=.*/,"",tag)
    sub(/[^=]*=[[:space:]]*/,"",val)
    if ( !(tag in tag2val) ) {
        tags[++numTags] = tag
    }
    tag2val[tag] = val
    next
}
NR>1 { prt() }
END { prt() }

function prt(   tagNr, tag, val) {
    if ( !doneHdr++ ) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            tag = sanitize(tags[tagNr])
            printf "%s%s", tag, (tagNr<numTags ? OFS : ORS)
        }
    }
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        tag = tags[tagNr]
        val = sanitize(tag2val[tag])
        printf "%s%s", val, (tagNr<numTags ? OFS : ORS)
    }
    numTags = 0
    delete tag2val
}

function sanitize(inStr,        outStr) {
    outStr = inStr
    if ( outStr ~ ("[" OFS "\"]") ) {
        gsub(/^"|"$/,"",outStr)
        gsub(/"/,"\"\"",outStr)
        outStr = "\"" outStr "\""
    }
    return outStr
}

$ awk -f tst.awk file
User-Name,Acct-Status-Type,Acct-Output-Octets,Acct-Session-Id,Acct-Session-Time,Acct-Output-Gigawords,Event-Timestamp,Timestamp
"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000

and to write it to a file is just the same as writing any other commands output to a file:

awk -f tst.awk file > output.csv

The above will output correct, valid CSV even if your input values or tags contain =, ", ,s, or any other characters except newlines.

If you don't actually want the header line then just remove the if ( !doneHdr++ ) block from the prt() function.

2
  • Thank you for your response. Your solution working best for my case but I got one thing to ask from you. There's three types of "Acct-Status-Type" which is Start, Stop and Interim-Update. When the type is "Start" then I need to ignore the whole text and I kind a stuck on this one. Can you help me on this one.
    – Dulguun
    Sep 15, 2021 at 3:24
  • Yes, accept an answer(unix.stackexchange.com/help/someone-answers) to this question and ask a new question with updated sample input/output and updated requirements. Chameleon questions are discouraged.
    – Ed Morton
    Sep 15, 2021 at 11:47
1

I would start with

awk -F= 'NF==2{printf "%s%s",comma,substr($2,2);comma=","} END {printf "\n" }' source > dest

where

  • -F= use = as seprator
  • NF==2 select line with 2 fields
  • substr($2,2) strip leading space
  • source and dest are source and destination files.

if you want to keep you program, you can replace

if (($1=="Acct-Session-Id")&&(start==1))
{wholeLine=wholeLine","$3;}

by

$1 ~ /Acct-Session-Id/ && (start==1) {wholeLine=wholeLine","substr($2,2);}

and remove enclosing { ... } as @berndbausch pointed out.

1

In the following, I assume your input data is always sorted the right way, i.e. each block from "User-Name" to "Timestamp" has the data you wish to extract in the right order, and "Event-Timestamp" is second to last.

In that case you may just use awk's capability of working on ranges, defined by pattern matches: /start/,/end/.

'BEGIN { FS=" = " }
/User-Name/,/Event-Timestamp/ {printf "%s,",$2}
/ Timestamp/ {print $2}'

Note that the pattern match "/ Timestamp/" needs a space in the beginning for not matching "Event-Timestamp", too. Other appearances of the matched patterns are not considered and might break the script.

Sample input:

Sun Aug 22 19:00:00 2021
        User-Name = "407359687"
        Acct-Status-Type = Interim-Update
        Acct-Output-Octets = 3263901190
        Acct-Session-Id = "PPP3092201SSG0001006b0a55AABODS"
        Acct-Session-Time = 1146851
        Acct-Output-Gigawords = 15
        Event-Timestamp = "Aug 22 2021 18:55:32 +08"
        Timestamp = 1629630000
RANDOM ANNOYANCE
AND AN EMPTY LINE

Sun Aug 22 19:00:00 2021
        User-Name = "407359687"
        Acct-Status-Type = Interim-Update
        Acct-Output-Octets = 3263901190
        Acct-Session-Id = "PPP3092201SSG0001006b0a55AABODS"
        Acct-Session-Time = 1146851
        Acct-Output-Gigawords = 15
        Event-Timestamp = "Aug 22 2021 18:55:32 +08"
        Timestamp = 1629630000

sample output:

"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000
"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000

EDIT:

Following the requirement for excluding "Acct-Status-Type = Start", I'd suggest running a second implementation of awk removing such lines:

awk ' ...above code...' file | awk -F, '$2!="Start"'
2
  • Elegant! It looks like the original question had a goal to ignore blocks where 'Acct-Status-Type=Start' though. Perhaps the input could be filtered in two passes. The first pass would filter out such blocks and the second pass would run this code. Sep 9, 2021 at 13:51
  • @MarkStosberg - aah, yes, I overlooked that. However, it seems simpler to first process all blocks, then sieve out the "Start"-ones.
    – FelixJN
    Sep 13, 2021 at 6:19
0

Using Raku (formerly known as Perl_6)

raku -e 'my @array; for lines() {@array.push($_) if /User\-Name/ fff /<!after Event\-> Timestamp/}; 
         @array>>.split(/^^ .+? " = "/, :skip-empty).batch(8).map(*.join(",")).join("\n").put;'

Sample input (after @FelixJN):

Sun Aug 22 19:00:00 2021
        User-Name = "407359687"
        Acct-Status-Type = Interim-Update
        Acct-Output-Octets = 3263901190
        Acct-Session-Id = "PPP3092201SSG0001006b0a55AABODS"
        Acct-Session-Time = 1146851
        Acct-Output-Gigawords = 15
        Event-Timestamp = "Aug 22 2021 18:55:32 +08"
        Timestamp = 1629630000
RANDOM ANNOYANCE
AND AN EMPTY LINE

Sun Aug 22 19:00:00 2021
        User-Name = "407359687"
        Acct-Status-Type = Interim-Update
        Acct-Output-Octets = 3263901190
        Acct-Session-Id = "PPP3092201SSG0001006b0a55AABODS"
        Acct-Session-Time = 1146851
        Acct-Output-Gigawords = 15
        Event-Timestamp = "Aug 22 2021 18:55:32 +08"
        Timestamp = 1629630000

sample output:

"407359687",Start,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000
"407359687",Interim-Update,3263901190,"PPP3092201SSG0001006b0a55AABODS",1146851,15,"Aug 22 2021 18:55:32 +08",1629630000

While I recognize the OP asked for an awk solution, the Perl family of languages is famous for text processing. The 'one-liner' Raku code above uses the fff "flip-flop" operator to capture text between two sentinel lines, the first matching the "User-Name" line and the second matching the "Timestamp" line. A negative lookbehind <!after Event\-> is used to make sure that the regex doesn't recognize the "Event-Timestamp" line by mistake.

The selected lines are pushed onto @array and then split() is used to get rid of everything to the left of the desired values. Records are batch()-ed in groups (columns) of 8, and a map() call is used to join() values with commas. Successive record rows are joined by newlines.

Removing any CSV rows with Start in the second column can be accomplished by piping the above one-liner through the following:

raku -ne '.put unless .split(",")[1] eq "Start";'

This is a good place to start if CSV is going to be a major part of your computing life. Raku has a number of CSV modules to help you with more complicated CSV cases.

https://modules.raku.org/search/?q=CSV
https://raku.org/

2
  • 1
    This is fun, although I thought the problem might be a good fit to show off Raku's support for text processing using grammars. Sep 9, 2021 at 13:39
  • @MarkStosberg you might find this answer interesting as it uses a Raku Grammar on a large XML file: stackoverflow.com/a/66540269/7270649 . Cheers! Sep 9, 2021 at 14:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .