0

I have to parse a CSV flat-file containing only line item data, with no recognisable header record, kinda like this:

930001,14-02-2013,100.00,1,Line 1,2,10.00,20.00
930001,14-02-2013,100.00,2,Line 2,2,20.00,40.00
930001,14-02-2013,100.00,3,Line 3,1,40.00,40.00
930002,13-02-2013,200.00,1,Line 1,10,10.00,100.00
930002,13-02-2013,200.00,2,Line 2,5,20.00,100.00
930003,14-02-2013,100.00,1,Line 1,3,20.00,60.00
930003,14-02-2013,100.00,2,Line 2,2,20.00,40.00

Where the fields are, in order:

Order No,Order Date,Order Amt,Line No,Line Desc,Line Qty,Unit Price,Line Price

I want to use the BizTalk Flat File receive pipeline to transform this into a hierarchical schema, grouping on the first field, the Order No:

Order_Batch
  + Order
    + OrderLine

Is there a way to perform a grouping operation via the flat-file receive, so that, in the above instance, the first 3 lines (Order No=930001)

<OrderBatch>
  <Order>
    <OrderLine>
      <OrderNo>930001</OrderNo>
      <other_fields />
      <LineNo>1</LineNo>
      <other_fields_etc />
    </OrderLine>
    <OrderLine>
      <OrderNo>930001</OrderNo>
      <other_fields />
      <LineNo>2</LineNo>
      <other_fields_etc />
    </OrderLine>
    <OrderLine>
      <OrderNo>930001</OrderNo>
      <other_fields />
      <LineNo>2</LineNo>
      <other_fields_etc />
    </OrderLine>
  </Order>
  <Order> ... Details of Order 930002 ... </Order>
  <Order> ... Details of Order 930003 ... </Order>
</OrderBatch>

The only option I currently see available to me is to accept the entire file as a set of OrderLine records, un-batched, then perform the batching using the Gather pattern in another Orchestration. I would prefer to Keep It Seriously Simple.

Brett
  • 1,127
  • 6
  • 11
  • In order to do that you need to know the order numbers before hand and the sequence they come in. As these would not be limited and could be added or removed dynamically, the answer is not possible, just by using the flat file schema. Atleast we need a tag identifier which distinguishes where a batch of records end and a new batch begin. – user1826905 Feb 14 '13 at 09:29

1 Answers1

0

Use a map to translate from flat to hierarchical:

  1. Create a schema for your flat file using the flat file schema wizard
  2. Use a pipeline and the flat file disassembler to get the input message
  3. Create a schema for your desired output xml
  4. Create a map to transform the flat file message to the desired output message

I believe you can use xsl in the map you can do the grouping

Community
  • 1
  • 1
Jay
  • 13,803
  • 4
  • 42
  • 69