Converting single record per line invoice file into complex sequential file

[Anonymous]

The input file has one record per row containing invoice details. I need to convert this into a sequential file that has a Invoice Header, Invoice Line and Invoice Distribution record per invoice.

input line example:

I0000762857OOOODBISTHTRANSP00000000000001220000028800001/10/17                         01NPO52732100000Z0000000000000000076000              CR/LF

All of the required data for the Header, Line and Distribution record is contained in a single line. The actual spec for the output file is very complex, the invoice header record is 780 characters, the invoice line is 535 characters and the invoice distribution record is 198 characters, so not really practical to describe the format in full. Empty fields are ok in the record. 

But I am struggling to figure out how to process the input file to generate three different records from a single line and write them back to the same file. Also there may be more than one invoice line per invoice, so I need to group the invoice lines based on the Vendor-ID. 

Can anyone give me a steer?

Many thanks, Jerry

[TRF · Accepted solution]

Hi,

You may have a single tMap with 3 distinct output flows connected to the same output file (append option ticked).

Add 2 fields representing vendor and type, then using a tSortRow (based on these fields) to present the records in the desired order.

Then remove the extra-fields.

This is the idea, it should work.

[Anonymous]

Thanks TRF, that's a great help.
I didn't know you could do that with a single tMap, I'll give it a go.
Cheers, jerry

[TRF]

You're welcome.
Don't forget to mark your case as solved if it is

[Anonymous]

Hi TRF

Thanks for your help.

I've created a job that maps the data to three schemas in TMap, but the output has all of the headers first, then all of the line data then all of the footers. I need it to be in the correct sequence of header, line, footer per record. How to I control the flow so that the records appear in the output file in to correct order?