Skip to Content

Overview


In this example we are going to explore importing a text file as a Sales Order.  The challenge with importing text files is simply they are not in XML and B1if processes XML.  The key to this document is the Format Control Document that uses regular expressions to transform the text in the file into XML tags so that we can process the information through additional steps.

In an effort to make the this example similar to a normal business transaction, the import file is defined as a simplified EDI 850 Purchase Order document.  This file definition is in no way defined to any specific standard and most EDI documents are more complex.

Attachments:
      ImportFile.txt – a test import file
      PO File Definition.txt – the definition of our import file.

 

Prerequisites
    Understanding Regular Expression
Understanding XSLT, particularly XPath

Scenario Package


The Scenario Package is simply set up to package our scenario steps.

Txt2SOPackage.JPG

 

Scenario Step


 

ScenarioStepSetup.JPG

Inbound

InboundStepSetup.JPG

 

InboundFormatting.JPG

InFileFormatControl.xml                                                                                         

<tagDefinition
             xmlns=”urn:com.sap.b1i.bizprocessor:pltdefinition”
             regex=”^ST\x2A850\x2A[0-9]{4}.*”
             schemaName=””
             tagName=”ORDER”
             matchSplit=”M”
             stackSize=”10000″
             DOTALL=”true”
             MULTILINE=”true”>

     <tagDefinition regex=”^BEG.*?\x5E” tagName=”BEG” matchSplit=”M” >
            <tagDefinition regex=”\x2A|\x5E” tagName=”SEGMENT” matchSplit=”S” />
     </tagDefinition>
     <tagDefinition regex=”^N1.*?\x5E” tagName=”NAME” matchSplit=”M”>
            <tagDefinition regex=”\x2A|\x5E” tagName=”SEGMENT” matchSplit=”S” />
      </tagDefinition>
      <tagDefinition regex=”^N3.*?\x5E” tagName=”ADDR” matchSplit=”M” >
             <tagDefinition regex=”\x2A|\x5E” tagName=”SEGMENT” matchSplit=”S” />
      </tagDefinition>
      <tagDefinition regex=”^N4.*?\x5E” tagName=”LOCATION” matchSplit=”M”>
             <tagDefinition regex=”\x2A|\x5E” tagName=”SEGMENT” matchSplit=”S” />
      </tagDefinition>
      <tagDefinition regex=”^PO1.*?\x5E” tagName=”LINE” matchSplit=”M” >
             <tagDefinition regex=”\x2A|\x5E” tagName=”SEGMENT” matchSplit=”S” />
      </tagDefinition>
</tagDefinition>

_____________________________________________________________________

 

The purpose of the Format Control Document is to define how to convert the text to XML.  This is accomplished by using regular expressions to define XML tag content.

The tagDefinition tags help us define what data belongs in each XML tag.  The 3 attributes that need to be defined are the regex, tagName and matchSplit attributes.  The tagName defines the the name of the XML tag to be used in the output.  The matchSplit is either M for match or S for split.  M tells the system to take the string-section matching the regex as output.  S tells the system to take the strings between the matching string-sections as output.  Each tagDefinition embedded in another tagDefintion will take the output of the outer definition as input to the inner definition.

In this example, the outer most tag defines the entire file.  ^ST\x2A850\x2A[0-9]{4}.* defines an ORDER tag to Match everything from a line beginning with ST*850*<4 digits> and 0 or more(*) of any character (.).  If the file definition could define multiple orders, then we would change the ORDER tag to include the end of the order.  ^ST\x2A850\2A[0-9]{4}.*SE\x2A[0-9]+\x2A[0-9]+\x5E to include the transaction set trailer.

The output of the outer tagDefiniton is passed into the the next layer of tag definitions.  Each of these tag definitions look at the beginning of each line for a specific pattern.  Each line begins with an identifier and ends with ^. Then we pass each lines output to the next tag definition to split the values on * or ^.  We have to use \x2A (*) and \x5E (^) because * and ^ are special characters in regular expressions. \x defines a hexadecimal value where 2A is the hexadecimal ASCII representation of *.

 

Resulting Payload                                                                                              

<Payload Role=”S” intype=”rgx_ruledoc”>
    <bfa:io pltype=”rgx” schemaName=””>
        <ORDER xmlns=””>
           <BEG>
                <SEGMENT>BEG</SEGMENT>
                <SEGMENT>00</SEGMENT>
                <SEGMENT>NE</SEGMENT>
                <SEGMENT>092123456</SEGMENT>
                <SEGMENT/>
                <SEGMENT>20130630</SEGMENT>
           </BEG>
           <NAME>
                 <SEGMENT>N1</SEGMENT>
                 <SEGMENT>BT</SEGMENT>
                 <SEGMENT>C20000</SEGMENT>
                 <SEGMENT>Norm Thomson^</SEGMENT>
            </NAME>
            <ADDR>
                 <SEGMENT>N3</SEGMENT>
                 <SEGMENT>100 S. Lasalle St.</SEGMENT>
                 <SEGMENT>Suite419</SEGMENT>
            </ADDR>
            <LOCATION>
                 <SEGMENT>N4</SEGMENT>
                 <SEGMENT>Chicago</SEGMENT>
                 <SEGMENT>IL</SEGMENT>
                 <SEGMENT>60601</SEGMENT>
            </LOCATION>
            <LINE>
                  <SEGMENT>PO1</SEGMENT>
                  <SEGMENT>0001</SEGMENT>
                  <SEGMENT>2</SEGMENT>
                  <SEGMENT>A00001</SEGMENT>
             </LINE>
             <LINE>
                   <SEGMENT>PO1</SEGMENT>
                   <SEGMENT>0002</SEGMENT>
                   <SEGMENT>5</SEGMENT>
                   <SEGMENT>A00002</SEGMENT>
              </LINE>
              <LINE>
                    <SEGMENT>PO1</SEGMENT>
                    <SEGMENT>0003</SEGMENT>
                    <SEGMENT>4</SEGMENT>
                    <SEGMENT>A00003</SEGMENT>
               </LINE>
               <LINE>
                    <SEGMENT>PO1</SEGMENT>
                    <SEGMENT>0004</SEGMENT>
                    <SEGMENT>3</SEGMENT>
                    <SEGMENT>A00004</SEGMENT>
               </LINE>
        </ORDER>
   </bfa:io>
</Payload>

___________________________________________________________________

 

 

Processing

Now that we have managed to convert the text file into XML we can process the input however we see fit.  In this example we will simply transform the data into a sales order schema for our outbound.

Txt2SOProcessing.JPG

Atom0 transform.

<xsl:template name=”transform”>

      <B1out xmlns=”” type=”object”>

           <Documents>

                  <row>

                         <CardCode>

                                   <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/NAME/SEGMENT[3]”/>

                         </CardCode>

                         <NumAtCard>

                                   <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/BEG/SEGMENT[4]”/>

                         </NumAtCard>

                         <DocDate>

                                   <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/BEG/SEGMENT[6]”/>

                         </DocDate>

                         <TaxDate>

                                   <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/BEG/SEGMENT[6]”/>

                         </TaxDate>

                         <DocDueDate>

                                   <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/BEG/SEGMENT[6]”/>

                         </DocDueDate>

                  </row>

          </Documents>

          <Document_Lines>

                  <xsl:for-each select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LINE”>

                         <row>

                               <ItemCode>

                                      <xsl:value-of select=”SEGMENT[4]”/>

                               </ItemCode>

                               <Quantity>

                                      <xsl:value-of select=”SEGMENT[3]”/>

                               </Quantity>

                         </row>

                 </xsl:for-each>

          </Document_Lines>

          <AddressExtension>

                 <row>

                       <ShipToStreet>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/ADDR/SEGMENT[2]”/>

                       </ShipToStreet>

                       <ShipToStreetNo nil=”true”/>

                       <ShipToBlock>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/ADDR/SEGMENT[3]”/>

                       </ShipToBlock>

                       <ShipToBuilding/>

                       <ShipToCity>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[2]”/>

                       </ShipToCity>

                       <ShipToZipCode>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[4]”/>

                       </ShipToZipCode>

                       <ShipToCounty/>

                       <ShipToState>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[3]”/>

                       </ShipToState>

                       <ShipToCountry>US</ShipToCountry>

                       <ShipToAddressType nil=”true”/>

                       <BillToStreet>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/ADDR/SEGMENT[2]”/>

                       </BillToStreet>

                       <BillToStreetNo nil=”true”/>

                       <BillToBlock>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/ADDR/SEGMENT[3]”/>

                       </BillToBlock>

                       <BillToBuilding/>

                       <BillToCity>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[2]”/>

                       </BillToCity>

                       <BillToZipCode>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[4]”/>

                       </BillToZipCode>

                       <BillToCounty/>

                       <BillToState>

                               <xsl:value-of select=”/vpf:Msg/vpf:Body/vpf:Payload[./@Role=’S’]//bfa:io/ORDER/LOCATION/SEGMENT[3]”/>

                       </BillToState>

                       <BillToCountry>US</BillToCountry>

                       <BillToAddressType nil=”true”/>

                 </row>

          </AddressExtension>

    </B1out>

</xsl:template>

 

Outbound

Txt2SOOutbound.JPG

 

SLD


Txt2SOSLD.JPG

 

Scenario Setup


Txt2SOPackageSetup.JPG

Txt2SOStepsActivation.JPG

Txt2SOSenderSetup.JPG

Txt2SOReceiverSetup.JPG

 

Conclusion


The key to any file import is to convert the data to XML data.  Once the data in converted to XML you can use B1if to create what ever processing step that need to be performed.  Using the regex file payload type allows more options for importing text files other than delimited files.  This is just a simple example of importing a file without any logging or error handling but these steps can be added.

 

As always, understanding XSLT and XML is key to translating data from one form to another, but the key to importing a non delimited file is to learn regular expressions to define the Format Control Document. 

 

To add error handling to the outbound see B1if by Example: Error Handling

 

References


Help -> Documents ->  2. Scenario Development -> 2.2 Inbound -> 2.2.3 File Inbound

B1if Version: 1.14.10

SBO Version: 8.82 PL10

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

    1. Jeff Campbell Post author

      There is a similar process for outbound using Regular Expressions.

      Check out the following link in which we handle errors and write the errors to a file and send a message to the user.  This doesn’t use regular expressions but uses the Store File atom to write to a file and an XForm atom to format the output.

      B1if by Example: Error Handling

      (0) 
  1. Amal Wanniarachchi

    Hi Jeff,

    Good day to you.

    I have a problem in this,

    In step definition—> Inbound channel —> Inbound type —- > I cannot select the flat type.
    in drop dwon menu, it shows the flat type. But when I select it, it doesnt appear in the box.
    Even I tried to write there, but it say inbound type doesnt support.

    P.S Its not a problem in the web browser, because in previous steps, it was working properly with the dropdown menus.

    please help me.

    Thank you.inbound - channel.jpg

    (0) 
  2. Amal Wanniarachchi

    Thank you Jeff. It worked.
    I uninsalled the IE 11. And it works perfectly with IE 9.

    I have another question.

    I need to import an xml file(or any file) from texdata database to the one MS SQL(SAP) database which is already existing(not a new one).

    I saw some webinars regarding this, but they create a new system for incoming file. I need to use in the existing system.

    please help.

    Thank you

    (0) 
  3. Amal Wanniarachchi

    Hi Jeff,

    Thank you for the solution.

    I have another question.

    I need to import an xml file(or any file) from texdata database to the one MS SQL(SAP) database which is already existing(not a new one).

    As per the your solution, the imported file is stored in a new location. I need to use an existing database file.

    In other words, I have the same file in the both databases. I need  MA SQL database file to be edited as the Texdata file is edited. (mirroring)

    please help.

    Thank you

    (0) 

Leave a Reply