4

I have loosely structured records in a file consisting of either 3 or 4 lines of text separated (mostly) by a blank line. Not all records have a blank line separator, but the last line of each starts with the word "Added". I would like to produce a csv file with each record on one line preceded by its line number. So far I have only been able to produce a concatenation of all records separated by an arbitrary number of spaces and a redundant comma.

Logically I am trying to achieve the following:

Read line, if line starts 'Added' keep newline at end
else replace 'newline' with ','
or if line is blank delete it
endif

Sample data:

Peter Green  
Space Monkey at Area 51  
Joined  
Added by SF 3 weeks ago  
Will Rossiter  
Joined  
Added by SF 3 weeks ago

Dean Matthews  
Guitarist at Blues  
Joined  
Added by SF 3 weeks ago  
Hobbit Mak  
Farnborough, United Kingdom  
Joined  
Added by SF 3 weeks ago  

Keneth W Moorfield  
THE STOREMAN  
Joined  
Added by SF 3 weeks ago  
Mick Georgious  
Software Engineer  
Joined  
Added by SF 3 weeks ago
  • You haven't said how big your data is. I'd be tempted to slurp it into emacs, and manually regularize the format to make later parsing easier. – waltinator Aug 5 '16 at 21:50
  • The dataset is thousands of records - which is why I gave up on manual editing @waltinator. – SeniorMoments Aug 6 '16 at 7:26
5

Try:

awk '/./{ printf "%s%s", $0, (/Added/?"\n":",") }' data

Using your sample input data:

$ awk '/./{printf "%s%s",$0,(/Added/?"\n":",")}' data
Peter Green,Space Monkey at Area 51,Joined,Added by SF 3 weeks ago
Will Rossiter,Joined,Added by SF 3 weeks ago
Dean Matthews,Guitarist at Blues,Joined,Added by SF 3 weeks ago
Hobbit Mak,Farnborough, United Kingdom,Joined,Added by SF 3 weeks ago
Keneth W Moorfield,THE STOREMAN,Joined,Added by SF 3 weeks ago
Mick Georgious,Software Engineer,Joined,Added by SF 3 weeks ago

How it works:

  • /./{...}

    This performs the commands in curly braces only if the line contains a character. In other words, this ignores blank lines.

  • printf "%s%s",$0,(/Added/?"\n":",")

    This prints the line, denoted $0, followed by either a comma or a newline depending on whether the line matches the regex Added.

  • 1
    I know "It's easy when you know how" but that was startlingly quick and looks effective. I am humbled and grateful. Thank you @John1024. – SeniorMoments Aug 5 '16 at 18:47
3

Here's a possible sed solution (with awk do do the line numbering):

$ sed -n -e :a -e '$!{/^$/!N}; /,Added/ {P;D}; s/\n/,/; ta' data | awk '{print NR","$0}'
1,Peter Green,Space Monkey at Area 51,Joined,Added by SF 3 weeks ago
2,Will Rossiter,Joined,Added by SF 3 weeks ago
3,Dean Matthews,Guitarist at Blues,Joined,Added by SF 3 weeks ago
4,Hobbit Mak,Farnborough, United Kingdom,Joined,Added by SF 3 weeks ago
5,Keneth W Moorfield,THE STOREMAN,Joined,Added by SF 3 weeks ago
6,Mick Georgious,Software Engineer,Joined,Added by SF 3 weeks ago 

Basically we just keep appending non-empty lines of input and replacing their newlines with commas, except we check at each iteration to see if we have a whole record and if we do, spit it out i.e.

  • set a program label :a
  • if not at the end of file $! then append non-empty lines to the pattern space {/^$/!N}
  • if we are at the end of a record /,Added/ then print it P and delete it D from the pattern space
  • substitute comma for newline s/,/\n/, branching back to a on success
  • RTFM RTFM Head down and researching how you have used sed - impressive, thank you @steeldriver – SeniorMoments Aug 5 '16 at 19:37
  • @SeniorMoments thanks - I like to explore its dusty corners ;) – steeldriver Aug 5 '16 at 20:05
2

FWIW, here's a perl option:

$ perl -lne '
    push @rec, $_ unless /^$/; if (/^Added/) {print join ",", ++$n, @rec; undef @rec;}
' data
1,Peter Green,Space Monkey at Area 51,Joined,Added by SF 3 weeks ago
2,Will Rossiter,Joined,Added by SF 3 weeks ago
3,Dean Matthews,Guitarist at Blues,Joined,Added by SF 3 weeks ago
4,Hobbit Mak,Farnborough, United Kingdom,Joined,Added by SF 3 weeks ago
5,Keneth W Moorfield,THE STOREMAN,Joined,Added by SF 3 weeks ago
6,Mick Georgious,Software Engineer,Joined,Added by SF 3 weeks ago 

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.