help with parsing a flatfile

Just to preface I am a BizTalk Advanced Beginner, and a .Net Ultra Beginner, so please keep that in mind when responding.

I am trying to develop an outbound process where I create XML invoice files from a legacy system's flatfile. I have been all over the web looking for anything that can help me work with the file format the legacy system outputs, but have not gotten very far. I keep seeing concepts of convoys, debatching, etc, but nothing that quite fits what I am trying to do. Therefore I am looking for some direction if anyone can help.

The flatfile is used to house invoices for multiple customers. The main issue I am having is that it is a flatfile (sequential or delimited) where the header record repeats for every line, but the detail lines change. Therefore when I try to create a hierarchical xml file I do not know how to make BizTalk look ahead to see if the next line belongs to the same invoice. Then if it does not, how to make it summarize the doc, and start over with a new doc.

CustA, Invoice1, InvoiceDate, Line#1, Part, Qty, Price
CustA, Invoice1, InvoiceDate, Line#2, Part, Qty, Price
CustA, Invoice2, InvoiceDate, Line#1, Part, Qty, Price
CustB, Invoice3, InvoiceDate, Line#1, Part, Qty, Price

My theory so far is to just have a map or orchestration to get the file from flat to hierarchical, then worry about where it goes after that using subscriptions, and customer specific mappings.

I hope I made myself clear, and I appologize in advance for my lack of programming knowledge.

Hello.

I would look at the solution in two parts.
1. Convert the file into XML
2. Map the document as you need it

The first part will be taken care of for you by the flat file disassemble.
The second part can be done in a map on the Receive Port. I think you could use custom XSLT to basically batch your message together inside a new node that you can then debatch in the Orchestration.

It would look like this:
CustA, Invoice1, InvoiceDate, Line#1, Part, Qty, Price
CustA, Invoice1, InvoiceDate, Line#2, Part, Qty, Price
CustA, Invoice2, InvoiceDate, Line#1, Part, Qty, Price
CustB, Invoice3, InvoiceDate, Line#1, Part, Qty, Price

To:
<node>
CustA, Invoice1, InvoiceDate,
Line#1, Part, Qty, Price
Line#2, Part, Qty, Price
</node>
<node>
CustA, Invoice2, InvoiceDate,
Line#1, Part, Qty, Price
Line#2, Part, Qty, Price
</node>

All the fields would be in XML of course. This article talks about it a little: http://codebetter.com/blogs/jeff.lynch/articles/category/1169.aspx. I don’t think the XSLT would be very hard. I have some samples of mapping on this site that might help as well.

Best of luck.

Steven, thanks for taking the time to respond. I have a few follow up Qs to your response. First of all, the link does not work for me, so I have not had a chance to look at the code examples.

Secondly, I am a little unclear as to where you are pointing me. Let me see if I can put your words into my words and to see if I have it correct.

1. convert each line into XML using the disassembler.
2. use custom XSLT within an orchestration to find like header records and group the lines together under the single header record within that single node as you have shown in the \"To:\" output.
3. debatch within the orch using whatever criteria I decide now that I have the doc in a hierarchical format.
4. map to customer specific format using maps in the ports.

I think I have a good idea how to do 1, 3, and 4, and hopefully after looking the examples I can figure out the custom XSLT. If not I will ask around here.

Again, Thanks for the assistance.
mike

Sorry about the link. It has an “.” at the end. Just remove that and the page opens. Try this one: http://codebetter.com/blogs/jeff.lynch/articles/category/1169.aspx

Close, #2 would be done inside a map on the Receive Port.

XSLT is straight forward but not very user friendly. I like w3schools.com. They cover XSLT well. I think what I was talking about can be done. I’d try to use a xslt:foreach with conditional logic to only create a new node if the invoice number was difference. Might need nested foreach, but don’t remember if that’s allowed.

Take a look at some of my maps samples on the site. I think I have some that us custom XSLT.

Might be some “easier” ways to do it without custom XSLT. Could probably use multiple maps inside an Orchestation. I’d just try to break it down into smaller parts like:
Step 1: Get flat file into xml
Step 2: Get Xml into a nodes based on Invoice Number.
Step 3: Get Xml sorted as needed
Step 4: Remove extra Headers
Step 5… so on.

Hope this helps.