none
Reading an excel from a location using Custom Pipeline component and converting it into 2 EDI outputs RRS feed

  • Question

  • Hi,

    We have requirement of reading an excel from a location and converting it into 2 EDI outputs - EDI 214 and EDI 315 based on an excel column value.

    We had designed a custom pipeline component earlier for a requirement before which reads the excel and transforms it into single output EDI 214 format. And it works fine.

    In this requirement, as we need to transform the input file into 2 output EDI's based on a column value, please suggest on how to proceed.

    We have questions like, should we split the excel itself and then the pipeline should pick them? or should we convert the excel to 2 XML's in the pipeline component and then it should be processed?

    Any suggestions are appreciated.

    Regards,


    Anand

    Monday, September 15, 2014 11:02 AM

Answers

  • You can easily cover this in a map by making use of Equal functoid to check the value of Milestone Field and map the same to the destination schema by mapping the output of the Equal functoid to the Value Mapping functoid.

    It will work like below:

    1) The equal functoid will check the value of the Milestone field and if matches 'Milestone 1', it will return True. Output of Equal Functoid will be the input to Value mapping functoid and the second input parameter will be the Milestone field. Output of the Value mapping functoid will be mapped to the destination schema. i.e. If the value of the Equal functoid is “true,” then the value of the second input parameter is returned.

    2) Value mapping Functoid can be used to map all other fields under Detail Record using the output of above mentioned Equal functoid and the second input parameter of the respective field.

    3) Step 1 and 2 has to be performed for all Detail Record. So that all the Detail Records with Milestone field having value 'Milestone 1' will mapped to destination schema as well.

    I hope this helps.

    Regards,

    Rachit

    Thursday, September 18, 2014 6:55 AM
    Moderator
  • It worked for me,

    Using Equal and Value Mapping functoids, i was able to restrict the duplicate data and filter the records. But, few nodes in output schema were still coming without any elements value.

    To the output of the above combination, i added the "Logical Existence" functoid, and i got the output as required.

    Thanks all for valuable inputs.


    Anand

    • Marked as answer by Anand M J Thursday, September 18, 2014 11:27 AM
    Thursday, September 18, 2014 11:22 AM

All replies

  • The 'regular' BizTalk Pattern for this would be to have the custom Pipeline Component convert the Excel file to Xml (or extract the Xml content, whatever, doesn't really matter).

    Publish that to the MessageBox where it is routed to 2 Send Ports.  Then on each Send Port, Map to the 214 and 315.

    • Proposed as answer by Girish R. Patil Monday, September 15, 2014 12:29 PM
    Monday, September 15, 2014 12:06 PM
  • Thanks for the response,

    So once it is passed to MessageBox, should we handle and route it to 2 send ports inside the Orchestration?

    Sorry, i don't have much experience working on the Orchestrations. If you can provide me in much more detail,it would be helpful for me.

    Thanks in advance.


    Anand

    Monday, September 15, 2014 12:29 PM
  • You don't have to use an Orchestration, just configure 2 Send Ports using whatever technique you're using now.  The difference would be the Map on each Send Port.
    Monday, September 15, 2014 12:48 PM
  • Ok, I will have 2 Send ports to handle 2 different maps (for 214 and 315). But, how should we have the filter conditions as the excel will have complete data. Can we filter it based on a column value under SendPort filter section itself in BizTalk admin console?

    Anand

    Monday, September 15, 2014 1:00 PM
  • The better way would be having the Excel->Xml component produce a complete output that can be used to generate either the 214 or 315.

    Each Map would be responsible for pulling only the data that particular transaction requires.

    Monday, September 15, 2014 2:42 PM
  • Ok, I will have 2 Send ports to handle 2 different maps (for 214 and 315). But, how should we have the filter conditions as the excel will have complete data. Can we filter it based on a column value under SendPort filter section itself in BizTalk admin console?

    Anand

    Use filter for Receive port name, which is same value for each send port:

    BTS.ReceivePortName == YourReceivePortName

    This way, both the send ports will subscribe to your message and with different maps on each send port, different outputs can be generated (as already suggested by boatseller).

    Thanks

    Tuesday, September 16, 2014 2:00 AM
  • Thanks for the inputs,

    But as soon as 1 of the send ports picks the file, it will be removed from the input location right?

    How will the second send port access the same input file?


    Anand

    Tuesday, September 16, 2014 6:37 AM
  • Sorry, I think i was not clear earlier.

    Lets assume, I have an input excel file with 2 columns (Col1 and Col2) and 50 records.

    Col1 has few standard values("XYZ" and "ABC").

    Assume 25 records has "XYZ" as Col1 value and other 25 records have "ABC" as Col1 value.

    Now, my requirement is :

    1. The records with Col1 value as only "XYZ" (25records) should be mapped to EDI 214 output.

    2. The records with Col2 value as only "ABC" (25records) should be mapped to EDI 315 format.

    That's the reason i thought the input file must be split before the biztalk picks it up. Please suggest on this.

    Any help is deeply appreciated.

    Thanks in Advance.



    Anand

    Tuesday, September 16, 2014 7:07 AM
  • The file will be removed from the input location as soon as it successfully passed the receive pipeline and is stored in the MsgBox. As both SendPorts have an subscription to the file they both receive a copy.

    Regards,

    René

    Tuesday, September 16, 2014 7:11 AM
  • Thanks for the inputs,

    But as soon as 1 of the send ports picks the file, it will be removed from the input location right?

    How will the second send port access the same input file?


    Anand

    Send port doesn't pick up message, Recieve port does. You can have 20 different send ports all having a simple subscription like filter for ReceivePortName or MessageType.

    Best design I can think of, Read Excel convert to xml using disassembler or pipeline Component.

    Create orchestration that check value of xml node corresponding to each column. Use decide shape two go through either branch. Add 1 transform shape on each side and create two different maps.Put source schema as your xml (from excel) and target schema as 214, 315 in two maps separately. This way your message flows into one of the two branches and you get output accordingly.

    Thanks


    Tuesday, September 16, 2014 8:00 PM
  • But as soon as 1 of the send ports picks the file, it will be removed from the input location right?


    Technically no because the Receive Location and the Send Port is not a 1-to-1 relationship.

    Once the Receive Port publishes the message to the MessageBox, both Send Ports can subscribe and each get a copy.

    Tuesday, September 16, 2014 11:12 PM
  • 1. The records with Col1 value as only "XYZ" (25records) should be mapped to EDI 214 output.

    2. The records with Col2 value as only "ABC" (25records) should be mapped to EDI 315 format.

    That's no problem, the Map can filter on the value of Col1 or Col2 to make either 214 or 315.

    Yes, the data will be duplicated, but it will be ignored.  There is no performance consideration here.

    Tuesday, September 16, 2014 11:14 PM
  • @ Rene, Prabhdeep and Johns:

    Thanks for the inputs.. Yes I understood that the receive port picks the input file and publishes it to the messagebox, and based on the subscription "n" number of send ports can access it. Thank you all.


    Anand

    Wednesday, September 17, 2014 5:22 AM

  • That's no problem, the Map can filter on the value of Col1 or Col2 to make either 214 or 315.

    Yes, the data will be duplicated, but it will be ignored.  There is no performance consideration here.

    The thing is i will have thousands of records in input excel, so data should not be duplicated.

    Can you please help me in explaining on how can i use filter on the Col1 in the Filters under Send Port of Admin Console?

    Thanks in advance.


    Anand

    Wednesday, September 17, 2014 5:27 AM
  • Forget about filtering directly based on Col1 values for a second and think of some other design.

    First of, so far are you able to read your excel into xml yet? if so paste an xml instance of excel file with (say) 50 rows with two kinds of values.

    Once your xml is ready, we can think about conditional mapping and generate output accordingly. Then in send ports you don't need any filter for col1 values etc. Your maps should be good enough.

    Another approach - a bit lengthier, Load excel in SQL database (using SSIS etc). Then write a stored proc with two select statements ( Select * from col1_tbl where col1 = 'XYZ') and another select statement like  (Select * from col1_tbl where col1 = 'ABC'). This way , by the time your xml message hits BizTalk, you would have separated records with different values and can you use two separate maps.

    Thanks

    Thursday, September 18, 2014 2:34 AM
  • Yes Prabhdeep,

    I am able to read the excel and generate the xml. Please find below the instance of xml having 4 records.

    It has a header and details section. Under details, we can see the values of "Milestone 1" and "Milestone 2" for "Milestone" column in the excel.

    <ns0:Test xmlns:ns0="Test Namespace">
      <Header>
        <BLAWBNumber>XYZ</BLAWBNumber>
        <Container>123</Container>
        <TransportationType>Land</TransportationType>
        <Details>
          <Detail>
            <Milestone>Milestone 1</Milestone>
            <Country>US</Country>
            <City>Laredo, TX</City>
            <EventDate>2014-02-26T00:00:00</EventDate>
          </Detail>
          <Detail>
            <Milestone>Milestone 1</Milestone>
            <Country>US</Country>
            <City>Laredo, TX</City>
            <EventDate>2014-02-26T00:00:00</EventDate>
          </Detail>
          <Detail>
            <Milestone>Milestone 2</Milestone>
            <Country>US</Country>
            <City>Laredo, TX</City>
            <EventDate>2014-02-26T00:00:00</EventDate>
          </Detail>
          <Detail>
            <Milestone>Milestone 2</Milestone>
            <Country>US</Country>
            <City>Laredo, TX</City>
            <EventDate>2014-02-26T00:00:00</EventDate>
          </Detail>
        </Details>
      </Header>
    </ns0:Test>

    The requirement is,

    The records having "Milestone 1" should be mapped to EDI 214 and the records with "Milestone 2" as milestone value should be mapped to EDI 315 output.

    Please suggest on the same.

    Thanks.


    Anand

    Thursday, September 18, 2014 5:35 AM
  • You can easily cover this in a map by making use of Equal functoid to check the value of Milestone Field and map the same to the destination schema by mapping the output of the Equal functoid to the Value Mapping functoid.

    It will work like below:

    1) The equal functoid will check the value of the Milestone field and if matches 'Milestone 1', it will return True. Output of Equal Functoid will be the input to Value mapping functoid and the second input parameter will be the Milestone field. Output of the Value mapping functoid will be mapped to the destination schema. i.e. If the value of the Equal functoid is “true,” then the value of the second input parameter is returned.

    2) Value mapping Functoid can be used to map all other fields under Detail Record using the output of above mentioned Equal functoid and the second input parameter of the respective field.

    3) Step 1 and 2 has to be performed for all Detail Record. So that all the Detail Records with Milestone field having value 'Milestone 1' will mapped to destination schema as well.

    I hope this helps.

    Regards,

    Rachit

    Thursday, September 18, 2014 6:55 AM
    Moderator
  • Thanks Rachit, will try mapping based on your inputs.

    Anand

    Thursday, September 18, 2014 7:36 AM
  • It worked for me,

    Using Equal and Value Mapping functoids, i was able to restrict the duplicate data and filter the records. But, few nodes in output schema were still coming without any elements value.

    To the output of the above combination, i added the "Logical Existence" functoid, and i got the output as required.

    Thanks all for valuable inputs.


    Anand

    • Marked as answer by Anand M J Thursday, September 18, 2014 11:27 AM
    Thursday, September 18, 2014 11:22 AM
  • Please vote up if you find some answers helpful.

    Thanks

    Friday, September 19, 2014 12:18 AM