3

I have the following input, in which some of the values are comma delimited. I would like decouple each value and print it in the new line. I tried many ways in awk, but no luck. If I have three comma delimited values, the first value should be retained in the same row, but the rest of the values should be wrapped up in the new lines.

Input File
===========
key1|0|11881|0|0|0|0|11769|0|0|0
key2|2027|345,712|0|1|0|2040|364,729|0|1|0
key3|0|670944|0|0|0|0|495554|0|0|0
key4|1847|1,21|0|0|0|1814|1,22|0|0|0
key5|1880|11,402|0|154|0|1886|11,397|0|151|0
key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
key7|1851|11,757|0|202|0|1856|13,751|0|193|0

Expected Output
================


key1|0|11881|0|0|0|0|11769|0|0|0
key2|2027|345|0|1|0|2040|364|0|1|0
key2|-|712|-|-|-|-|729|-|-|-
key3|0|670944|0|0|0|0|495554|0|0|0
key4|1847|1|0|0|0|1814|1|0|0|0
key4|-|21|-|-|-|-|22|-|-|-
key5|1880|11|0|154|0|1886|11|0|151|0
key5|-|402|-|-|-|-|397|-|-|-
key6|1|65|0|8|0|16684|51|0|8|0
key6|1|4570|-|-|-|0|4176|-|-|-
key6|19137|-|-|-|-|-|-|-|-|-
key7|1851|11|0|202|0|1856|13|0|193|0
key7|-|757|-|-|-|-|751|-|-|-

EDIT #1

Per a comment left by @Avinash, here are my attempts. I'm new to the awk world, so may be beating around the bush. I followed the below steps. Please suggest me if any easier solution if you have in mind too. I am not getting the solution as expected.

step 1: $ awk -f test.awk a.txt > index.txt --> creates the index files
step 2: $ awk -f test2.awk a.txt > main.txt --> extracts the lines which are comma delimited and duplicate them number of times equal to the comma delimited values
step 3: $ awk -f updt_db1.awk index.txt main.txt --> updates the respective column
index.txt
=========
0|key2|3|345
1|key2|3|712
2|key2|8|364
3|key2|8|729
4|key4|3|1
5|key4|3|21
6|key4|8|1
7|key4|8|22
8|key5|3|11
9|key5|3|402
10|key5|8|11
11|key5|8|397
12|key6|2|1
13|key6|2|1
14|key6|2|19137
15|key6|3|65
16|key6|3|4570
17|key6|7|16684
18|key6|7|0
19|key6|8|51
20|key6|8|4176
21|key7|3|11
22|key7|3|757
23|key7|8|13
24|key7|8|751

main.txt
========
0|key2|2027|345,712|0|1|0|2040|364,729|0|1|0
1|key2|2027|345,712|0|1|0|2040|364,729|0|1|0
2|key2|2027|345,712|0|1|0|2040|364,729|0|1|0
3|key2|2027|345,712|0|1|0|2040|364,729|0|1|0
4|key4|1847|1,21|0|0|0|1814|1,22|0|0|0
5|key4|1847|1,21|0|0|0|1814|1,22|0|0|0
6|key4|1847|1,21|0|0|0|1814|1,22|0|0|0
7|key4|1847|1,21|0|0|0|1814|1,22|0|0|0
8|key5|1880|11,402|0|154|0|1886|11,397|0|151|0
9|key5|1880|11,402|0|154|0|1886|11,397|0|151|0
10|key5|1880|11,402|0|154|0|1886|11,397|0|151|0
11|key5|1880|11,402|0|154|0|1886|11,397|0|151|0
12|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
13|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
14|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
15|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
16|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
17|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
18|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
19|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
20|key6|1,1,19137|65,4570|0|8|0|16684,0|51,4176|0|8|0
21|key7|1851|11,757|0|202|0|1856|13,751|0|193|0
22|key7|1851|11,757|0|202|0|1856|13,751|0|193|0
23|key7|1851|11,757|0|202|0|1856|13,751|0|193|0
24|key7|1851|11,757|0|202|0|1856|13,751|0|193|0

Output

0|key2|2027|345|0|1|0|2040|364,729|0|1|0|
1|key2|2027|712|0|1|0|2040|364,729|0|1|0|
2|key2|2027|345,712|0|1|0|2040|364|0|1|0|
3|key2|2027|345,712|0|1|0|2040|729|0|1|0|
4|key4|1847|1|0|0|0|1814|1,22|0|0|0|
5|key4|1847|21|0|0|0|1814|1,22|0|0|0|
6|key4|1847|1,21|0|0|0|1814|1|0|0|0|
7|key4|1847|1,21|0|0|0|1814|22|0|0|0|
8|key5|1880|11|0|154|0|1886|11,397|0|151|0|
9|key5|1880|402|0|154|0|1886|11,397|0|151|0|
10|key5|1880|11,402|0|154|0|1886|11|0|151|0|
11|key5|1880|11,402|0|154|0|1886|397|0|151|0|
12|key6|1|65,4570|0|8|0|16684,0|51,4176|0|8|0|
13|key6|1|65,4570|0|8|0|16684,0|51,4176|0|8|0|
14|key6|19137|65,4570|0|8|0|16684,0|51,4176|0|8|0|
15|key6|1,1,19137|65|0|8|0|16684,0|51,4176|0|8|0|
16|key6|1,1,19137|4570|0|8|0|16684,0|51,4176|0|8|0|
17|key6|1,1,19137|65,4570|0|8|0|16684|51,4176|0|8|0|
18|key6|1,1,19137|65,4570|0|8|0|0|51,4176|0|8|0|
19|key6|1,1,19137|65,4570|0|8|0|16684,0|51|0|8|0|
20|key6|1,1,19137|65,4570|0|8|0|16684,0|4176|0|8|0|
21|key7|1851|11|0|202|0|1856|13,751|0|193|0|
22|key7|1851|757|0|202|0|1856|13,751|0|193|0|
23|key7|1851|11,757|0|202|0|1856|13|0|193|0|
24|key7|1851|11,757|0|202|0|1856|751|0|193|0|


$ cat test.awk
#!/bin/awk

BEGIN{
FS="|";
counter=0
}

NR == FNR {
for(i=1; i<= NF; i++)
{
  n=split($i,arr,",")
  if ( n > 1)
  for(j=1; j<=n; j++)
       printf "%s|%s|%s|%s\n",counter++,$1,i,arr[j]
}

}



$ cat test2.awk
#!/bin/awk
BEGIN { FS="|"
k=0;
}

NR == FNR {

for(i=1; i<= NF; i++)
{

  n=split($i,arr,",")
  if (n > 1)
     for(j=1; j<=n; j++)
            printf("%s|%s\n",k++,$0)

    }
}


$ cat updt_db1.awk
#!/bin/awk
BEGIN {
    FS = "|"
}
( NR == FNR ) {
    lookup[$1] = $0
}

( NR > FNR ) {
    key = toupper($1)
if (key in lookup){
    split(lookup[key], replacements, "|")
    for (i = 1; i <= NF; i++)
        col[i] = $i;
    for (i=3; replacements[i+1] != "" ; i=i+1){
    j=replacements[i]
    col[j+1] = replacements[i+1]
    }
    for (i = 1; i <= NF; i++)
        printf "%s|", col[i]
    print ""
  }
  else
    print $0;
}
  • I have given the steps which I followed. – janani Oct 24 '14 at 18:18
  • @AvinashRaj - I moved that A up into the Q for the OP. – slm Oct 24 '14 at 18:22
2

You could use sed like this:

    sed 'h;s/,[^|]*//g;x
    /,/{s/|[^,|]*,*/|-/g;H;}
    x;s/-\([^|]\)/\1/g;P;D'

It wound up being relatively simple after all. Applying that little script to your data gets:

key1|0|11881|0|0|0|0|11769|0|0|0
key2|2027|345|0|1|0|2040|364|0|1|0
key2|-|712|-|-|-|-|729|-|-|-
key3|0|670944|0|0|0|0|495554|0|0|0
key4|1847|1|0|0|0|1814|1|0|0|0
key4|-|21|-|-|-|-|22|-|-|-
key5|1880|11|0|154|0|1886|11|0|151|0
key5|-|402|-|-|-|-|397|-|-|-
key6|1|65|0|8|0|16684|51|0|8|0
key6|1|4570|-|-|-|0|4176|-|-|-
key6|19137|-|-|-|-|-|-|-|-|-
key7|1851|11|0|202|0|1856|13|0|193|0
key7|-|757|-|-|-|-|751|-|-|-

Basically sed just tackles each field from both ends. It first saves a copy of its current iteration to hold space. Then sed removes everything from every field following the first comma on. After that sed switches back to its saved copy so it can remove the field it just saved in the other buffer.

If commas remain it appends the second copy to the first following an inserted \newline character so it can recurse at least once more when P;D Print then Delete only up to the first occurring \newline character in pattern space before starting over with what remains.

  • @Glenn, I used ur script as follows, my task is accomplished, u are awesome, thanks a bunch, <ur script> | awk -F"|" '{for(i=1; i<=NF; i++) printf "%s|",($i == "") ? "-" : $i; print ""}' – janani Oct 25 '14 at 4:33
  • 1
    @janani - is this comment intended for me? – mikeserv Oct 25 '14 at 4:36
  • I thank to everyone who helped me to resolved the issue, of course u are also part of it, Mike :) – janani Oct 25 '14 at 17:23
2

I would not use awk for this, because I think it needs more flexible data structures. I would use perl:

perl -MList::Util=max -F'\|' -lane '
    $key = shift @F;
    @data = map {[split /,/]} @F;
    do {
        @row = map {shift(@$_) // "-"} @data;
        print join("|", $key, @row);
        $max = max map {scalar @$_} @data;
    } while ($max > 0);
' file
key1|0|11881|0|0|0|0|11769|0|0|0
key2|2027|345|0|1|0|2040|364|0|1|0
key2|-|712|-|-|-|-|729|-|-|-
key3|0|670944|0|0|0|0|495554|0|0|0
key4|1847|1|0|0|0|1814|1|0|0|0
key4|-|21|-|-|-|-|22|-|-|-
key5|1880|11|0|154|0|1886|11|0|151|0
key5|-|402|-|-|-|-|397|-|-|-
key6|1|65|0|8|0|16684|51|0|8|0
key6|1|4570|-|-|-|0|4176|-|-|-
key6|19137|-|-|-|-|-|-|-|-|-
key7|1851|11|0|202|0|1856|13|0|193|0
key7|-|757|-|-|-|-|751|-|-|-
  • how to get this executed through my ksh script? How can i invoke your code from ksh – janani Oct 24 '14 at 19:27
  • a ksh script can invoke perl just like it can invoke grep or cp or ... anything. Just copy that command into your ksh script (change the filename of course) – glenn jackman Oct 25 '14 at 1:02
  • I did, but getting this error.. Search pattern not terminated at -e line 5. is there any issue with the command? can you also please add comments to ur script which will be helpful to the beginners like me? – janani Oct 25 '14 at 1:51

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.