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.

Improve this question
1
  • 1
    Any feedback on the proposed answers @ramp ? Commented Jun 20, 2017 at 19:03

3 Answers 3

Reset to default
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
Improve this answer
3
  • @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. Commented Jun 20, 2017 at 15:29
  • Doh sorry I missed the fact that you are filtering through sed first. Apologies. Commented Jun 20, 2017 at 15:31
  • @steeldriver No worries! Yes, just to simplify the awk script a bit... Commented Jun 20, 2017 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

Improve this answer
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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.