0

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"
  • Can you clarify what constitutes a “key value”?  Is “Apollo13” one value or two? How about “Maroon 5”? How about “read-only”, “read/write”, “I.B.M.” and “Unix & Linux”? – G-Man Apr 24 at 0:20
  • Fisrt column is a number and second column is Key Value pair ... where before = is Key and after = is Value. – Sandeep Apr 24 at 8:11
  • Is the first and second column delimited by a tab? – Kusalananda Apr 24 at 14:39
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
  • Thanks but can you please help with AWK – Sandeep Apr 24 at 8:13
  • Please see my edit. – finswimmer Apr 25 at 4:13
  • You're right. Have edited the answer. – finswimmer Apr 25 at 4:32
  • but we don't know field separator is Tab : ) – αғsнιη Apr 25 at 5:43
0

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

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.

0

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

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.