file formatting using awk

Question

My input file contains data in the following format:

1503668542862176    manager=10001|Bounced=999|Analyst=10004|Business Analyst=10005|Programmer=10003
1552024948590636    manager=10001|Bounced=999|Analyst=10004
1551728916565460    Bounced=999|Analyst=10004
1553617087089790    Analyst=10004
1538058487418963    manager=10001|Architect=10002|Analyst=10004

I have to convert second column where every pair of key=value should be double quoted as "key"="value" and | should be replaced with , as below using awk.

1503668542862176    "manager"="10001","Bounced"="999","Analyst"="10004","Business Analyst"="10005","Programmer"="10003"
1552024948590636    "manager"="10001","Bounced"="999","Analyst"="10004"
1551728916565460    "Bounced"="999","Analyst"="10004"
1553617087089790    "Analyst"="10004"
1538058487418963    "manager"="10001","Architect"="10002","Analyst"="10004"

Answer 1

$ sed -e 's/|/","/g' -e 's/=/"="/g' -e 's/\t/\t"/' -e 's/$/"/' input.txt

This will:

• replace any | with ,
• replace any = with "="
• replace the first tab stop with \t"
• append a "and the line end

The most easiest way with awk is to change to field separators:

$ awk -v FS="|" -v OFS='","' '{$1=$1}1' \
  | awk -v FS="=" -v OFS='"="' '{$1=$1}1'\
  | awk -v FS="\t" '{print $1,"\""$2"\""}' input.txt

Answer 2

Using sed:

sed -e 's/|/","/g;s/=/"="/g;s/ /"/4;s/$/"/g' file

1503668542862176   "manager"="10001","Bounced"="999","Analyst"="10004","Business Analyst"="10005","Programmer"="10003"
1552024948590636   "manager"="10001","Bounced"="999","Analyst"="10004"
1551728916565460   "Bounced"="999","Analyst"="10004"
1553617087089790   "Analyst"="10004"
1538058487418963   "manager"="10001","Architect"="10002","Analyst"="10004"

Answer 3

If you can use GNU awk (which is typically available on any standard Linux distribution), you can use sub-expressions with the gensub() function:

< input_data awk -- '{gsub("\\|", ","); print gensub("([[:alpha:]][^=]*)=([^,]+)", "\"\\1\"=\"\\2\"", "g")}'

Assuming | appears only as key-value-pair separator, the first gsub() converts each | in , and then the gensub() function does the rest.

If you must use a POSIX awk then you may still obtain the same with a single script using a sequence of (indeed awkward..) gsub()s:

< input_data awk -- '{gsub("=", "\""); gsub("([[:alpha:]][^\"]*)", "\"&\"="); gsub("\"[^|]*", "&\""); gsub("\\|", ","); print;}'

Broken down (only the awk script part):

{
    gsub("=", "\"");
    gsub("([[:alpha:]][^\"]*)", "\"&\"=");
    gsub("\"[^|]*", "&\"");
    gsub("\\|", ",");
    print;
}

The first gsub() replaces each = into a ", paving the way for the subsequent couple of gsub()s, the first one looking for keys up to (but excluding) the first " and replacing that whole string with a leading " plus the found key plus a trailing "=, and the second gsub() looking for values starting with " (which originally was the =) up to (but excluding) the first | (if present) and replacing that string with itself plus a trailing ".

Basically this second solution uses " as ancillary key-value separator, therefore requires that it does not appear in either keys or values.

The final gsub() replaces all | in , just as in the first solution.

Answer 4

You could do this with a sleek regular expression, or you could do it by parsing the fields, splitting them on the appropriate delimiters, and reconstructing them in the way you want using awk:

BEGIN {
    # Assume the two fields are tab-delimited.
    OFS = FS = "\t"
}

NF > 1 {
    # Split the 2nd field into sub-fields on "|" in the array a.
    n = split($2, a, "|")

    for (i = 1; i <= n; ++i)
        # Split each sub-field on "=" and quote the two bits,
        # and put them together again.
        if (split(a[i], b, "=") == 2)
            a[i] = sprintf("\"%s\"=\"%s\"", b[1], b[2])
        else {
            # Bail out on bad fields.
            print >"/dev/stderr"
            printf("Error in field %d on line %d\n", i, FNR) >"/dev/stderr"
            exit 1
        }

    # Reconstruct current record.
    $0 = $1
    $2 = a[1]

    for (i = 2; i <= n; ++i)
        $2 = $2 "," a[i]

    # Done, output
    print
}

Testing:

$ awk -f script.awk file
1503668542862176        "manager"="10001","Bounced"="999","Analyst"="10004","Business Analyst"="10005","Programmer"="10003"
1552024948590636        "manager"="10001","Bounced"="999","Analyst"="10004"
1551728916565460        "Bounced"="999","Analyst"="10004"
1553617087089790        "Analyst"="10004"
1538058487418963        "manager"="10001","Architect"="10002","Analyst"="10004"

Answer 5

Done by awk command

command:

awk '{gsub(/\|/,",",$0);print $0}' filename | awk '{$2="\""$2;print $0}'| awk '{gsub(/\=/,"\"=\"",$0);print $0}'| awk '{gsub(/\,/,"\",\"",$0);print $0}'| awk '{$NF=$NF"\"";print $0}'

output

 awk '{gsub(/\|/,",",$0);print $0}' filename| awk '{$2="\""$2;print $0}'| awk '{gsub(/\=/,"\"=\"",$0);print $0}'| awk '{gsub(/\,/,"\",\"",$0);print $0}'| awk '{$NF=$NF"\"";print $0}'

1503668542862176 "manager"="10001","Bounced"="999","Analyst"="10004","Business Analyst"="10005","Programmer"="10003"
1552024948590636 "manager"="10001","Bounced"="999","Analyst"="10004"
1551728916565460 "Bounced"="999","Analyst"="10004"
1553617087089790 "Analyst"="10004"
1538058487418963 "manager"="10001","Architect"="10002","Analyst"="10004"