0

I have a file with data in the below format

Item1|keys,books,helmet,handle,
Item2|Bike,
Item3
Item4|Tyre,brakes,headlight,clamps,rollergrip,
Item5|Nails,hammers,

I wanted the above data to be converted to the below format

Item1|keys
Item1|books
Item1|helmet
Item1|handle
Item2|Bike
Item3
Item4|Tyre
Item4|brakes
Item4|headlight
Item4|clamps
Item4|rollergrip
Item5|Nails
Item5|hammers

I was trying to achieve this by using cut command though that was working fine I wanted to know can this be achieved using awk command. Since if the input file size gets bigger then it should be cumbersome.

  • 1
    Any feedback on the proposed answers @ramp ? – zee Jun 20 '17 at 19:03
1
sed -e 's/,$//' -e '/^[^|]*$/s/$/|/' file.in |
awk -F'[|,]' -vOFS='|' '{ for (i = 2; i <= NF; ++i) { print $1, $i } }'

The sed does a bit of preprocessing of the input data. It removes the trailing commas at the end of each line (-e 's/,$//'), and if a line doesn't contain a pipe symbol, it adds one to the end (-e '/^[^|]*$/s/$/|/').

The example data is transformed into the following by the sed filter:

Item1|keys,books,helmet,handle
Item2|Bike
Item3|
Item4|Tyre,brakes,headlight,clamps,rollergrip
Item5|Nails,hammers

The awk script takes this and interprets each line as a set of fields separated by either a pipe symbol or a comma (-F'[|,]'). For each input line, it will then repeatedly pair up the first field with each of the other fields and output them. Each pair of fields in the output are separated by a pipe symbol (-vOFS='|').

The result is

Item1|keys
Item1|books
Item1|helmet
Item1|handle
Item2|Bike
Item3|
Item4|Tyre
Item4|brakes
Item4|headlight
Item4|clamps
Item4|rollergrip
Item5|Nails
Item5|hammers
  • @steeldriver Hmmm... What awk are you using? I've tested this with GNU awk, mawk and with BSD awk. The reason I added the | to the line with only one field with sed was to get an empty field. NF will be 2 for that line. – Kusalananda Jun 20 '17 at 15:29
  • Doh sorry I missed the fact that you are filtering through sed first. Apologies. – steeldriver Jun 20 '17 at 15:31
  • @steeldriver No worries! Yes, just to simplify the awk script a bit... – Kusalananda Jun 20 '17 at 15:34
1

awk solution:

awk -F'|' 'NF>1 && $2~/[^,]+,[^,]*/{ 
           len=split($2,a,","); 
           for(i=1;i<=len;i++) { 
               if(a[i]!="") print $1,a[i] 
           } 
           next }1' OFS='|' file

The output:

Item1|keys
Item1|books
Item1|helmet
Item1|handle
Item2|Bike
Item3
Item4|Tyre
Item4|brakes
Item4|headlight
Item4|clamps
Item4|rollergrip
Item5|Nails
Item5|hammers

Details:

  • -F'|' - field separator

  • NF>1 && $2~/[^,]+,[^,]*/ - considers at least 2 fields with comma-separated values within the 2nd field

  • len=split($2,a,",") - split the 2nd field into array a by separator ,. The len variable is assigned with array size(number of chunks)

  • for(i=1;i<=len;i++) iterating through the "chunks"

  • if(a[i]!="") print $1,a[i] - print each related sequence except empty values

1

Another awk version

  • set the field separator to | or ,
  • if the record has fewer than 2 fields, print it as-is
  • else loop over all fields 2 to NF-1 printing $1 and $i (this skips the empty $NF caused by the trailing comma

i.e.

$ awk 'NF<2 ; {for (i=2;i<NF;i++) print $1,$i}' FS='[|,]' OFS='|' file
Item1|keys
Item1|books
Item1|helmet
Item1|handle
Item2|Bike
Item3
Item4|Tyre
Item4|brakes
Item4|headlight
Item4|clamps
Item4|rollergrip
Item5|Nails
Item5|hammers

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.