Hi Monks, This is a re-submitted request (Extracting fields) with more detail provided.

I need to re-format the following data (Data View 1), provided as multi-line records, to single line
records with a primary key (Data View 2).

There are five record types (10,20,30,40,50) that are associated to one primary key.

The data definitions are (with example data from primary key 00001):

Row 1 
record10 = integer (length 2) (10).
primary key = integer (length 5).(00001)
sub item number = integer (length 3).(225)
start date = dd.mm.yyyy.(01.11.1996)
end date = dd.mm.yyyy.(00.00.0000)
category = alphanumeric (length 3).(BVF)
group = alphanumeric (length 3).(AAA)
subgroup = alphanumeric (length 3).(A65)
item type = text (options: S or N).(S)
fee type = text (options: N or D).(N)
provider type = alphanumeric (length 3).(009)
new item = text (options: Y or N).(Y)
item change = text (options: Y or N).(N)
procedure change = text (options: Y or N).(Y)
description change = text (options: Y or N).(N)
fee change = text (options: Y or N).(Y)

[download]
Row 2 
record20 = integer (length 2).(20)
start date = dd.mm.yyyy.(01.11.1996)
fee = decimal (nnnnn.nn).(00098.05)
benefit1 = decimal (nnnnn.nn).(00073.55) 
benefit2 = decimal (nnnnn.nn).(00083.35)

[download]
Row 3 
record30 = integer (length 2).(30).
start date = dd.mm.yyyy.(14.11.1996)
description = alphanumeric (length 80).(This derived fee is for profes
+sional attendances for GP and Specialist.)

[download]
Row 4 
record40 = integer (length 2).(40)
start date = dd.mm.yyyy.(23.12.1996)
indicator = (Anaes.)

[download]
Row 5 
record50 = integer (length 2).(50)
start date = dd.mm.yyyy.(01.11.1997)
description = alphanumeric (length 80).(Professional attendance being 
+an attendance at other than consulting rooms, by a general practition
+er on not more than 1 patient.

[download]
DATA VIEW 1 
100000122501.11.199600.00.0000BVFAAAA65SN009YNYNY
2001.11.199600098.0500073.5500083.35
3014.11.1996This derived fee is for professional attendances for GP an
+d Specialist.
4023.12.1996(Anaes.)
5001.11.1997Professional attendance being an attendance at
5001.11.1997other than consulting rooms, by a general
5001.11.1997practitioner on not more than 1 patient.

100000222601.11.199600.00.0000BDGAABA66SN010YNYNY
2001.11.199600098.0500073.5500083.35
3014.11.1996This derived fee is for professional attendances by GP onl
+y.
4023.12.1996(Anaes.)
5001.11.1997Professional attendance being an attendance at
5001.11.1997other than consulting rooms, by a general
5001.11.1997practitioner only on not more than 1 patient.

[download]
DATA VIEW 2 
00001,10,225,01.11.1996,00.00.0000,BVF,AAA,A65,S,N,009,Y,N,Y,N,Y
00001,20,01.11.1996,00098.05,00073.55,00083.35
00001,30,14.11.1996,This derived fee is for professional attendances f
+or GP and Specialist.
00001,40,23.12.1996,(Anaes.)
00001,50,01.11.1997,Professional attendance being an attendance at oth
+er than consulting rooms, by a general practitioner on not more than 
+1 patient.
00002,10,226,01.11.1996,00.00.0000,BDG,AAB,A66,S,N,010,Y,N,Y,N,Y
00002,20,01.11.1996,00098.05,00073.55,00083.35
00002,30,14.11.1996,This derived fee is for professional attendances b
+y GP only.
00002,40,23.12.1996,(Anaes.)
00002,50,01.11.1997,Professional attendance being an attendance at oth
+er than consulting rooms, by a general practitioner only on not more 
+than 1 patient.

[download]

I would be very grateful for your suggestions.

Thanks in advance.

Janitored by Arunbear - added code tags, as per Monastery guidelines

In reply to Re-formatting multi-line records by kerrya

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.