Mixed positional and delimited example
About this task
This example shows how to build a Flat File Schema to parse a mix of positional and delimited data. To create a Flat File Schema, you must first analyze your Flat File data to determine what fields and nodes to added to the Flat File Schema. In this example, you create a Flat File Schema for the following two lines of data:
BuyRight Pharmacy;USACAMVI334;!389!22;34980;
Taylor Drugs;USANJWLB128;!778!23!523;5667;
Use a top-down approach for defining the records and fields of the Flat File Schema as shown in the following steps:
Procedure
- Create a root node:
- In the Project tab, right-click the Flat File Schemas folder and select New Flat File Schema. The Basic Wizard dialog box opens.
- Enter Order in the Name field.
- Click OK. The Flat File Schema Editor opens and the Flat File Schema is displayed beneath the Flat File Schemas folder in the Project tab. In addition, a root node of the same name shows in the Schema Layout tab.
- Determine the highest level of structure in the data. In
this example the largest structure is the two lines that each represent
a record. The two records in this Flat File data are delimited by
an end-of-line that is located in between the two records, as shown
in Figure 1: Figure 1. Mixed example 1: the records of the file are delimited by the end-of-line that is located between the characters that make up the record.
- Configure the root node called Order to
parse the child repeating records using the following criteria:
- Separate the data into records using a delimiter.
- Expect the document to end with an EOS (End Of Stream) because the last record does not end with a delimiter.
- Use the end-of-line delimiter to delimit any child records.
- Expect the delimiter in between the data.
- Expect repeating child records.
- Use the end-of-line delimiter to delimit the child repeating records.
Using these criteria, specify the following values for the Basic Properties and Delimiters pane of the Order node as shown in Figure 2:
Figure 2. Mixed example pane 1 where Structure is Delimited and Document ends with EOS box ticked. The Line End option is selected and Child Delimiter position is set to In Between - Add a child node to the root node to represent the records. In the Schema Layout tab, right-click the Order node. From the menu, select New Child > Record, enter purchaseOrder, and click OK.
- Determine the next level of structure in data. In this
example, the next level of structure are the fields delimited by semicolons
as shown in Figure 3: Figure 3. Mixed example 2: The individual fields of the record are delimited by semicolons.
- Configure the purchaseOrder node
to parse these fields as described by following criteria:
- The purchaseOrder node can have one or more occurrences.
- Parse the data of the record into child fields using a delimiter.
- Use the semicolon as the delimiter between the data.
- Expect the delimiter after the data.
Using these criteria, specify the following values for the Basic Properties and Delimiters pane of the purchaseOrder node as shown in Figure 4:
Figure 4. Mixed example pane 2: Structure is set to Delimited, Maximum unbounded and Character option selected. Child Delimiter Position is set to After. - Add the fields of the purchaseOrder node by right-clicking the purchaseOrder node in the Schema Layout tab and selecting the New Children option from the list. The Bulk Add dialog box is displayed.
- In the Bulk Add dialog box, add all the child records and
fields of the purchaseOrder node:
- Click Add and change field1 to storeName. Leave the Node Type set to Field.
- Click Add and change field2 to originCode. Change the Node Type to Record.
- Click Add and change field3 to items. Change the Node Type to Record.
- Click Add and change field4 to orderID. Leave the Node Type set to Field.
- Click OK.
- Determine the next level of structure in data. In this
example, the next level of structure is a set of positional data found
in the originCode record as shown in Figure 5: Figure 5. Mixed example 3: originCode is a section of positional data in the delimited record.
- Configure the originCode record
to be positional:
- In the Schema Layout tab, select the originCode node. The Basic Properties are displayed.
- In the Structure list, select Positional.
- Add the positional fields of the originCode node by right-clicking the originCode node in the Schema Layout tab and selecting the New Children option from the list. The Bulk Add dialog box opens.
- In the Bulk Add dialog box, add all
the child records and fields of the originCode node:
- Click Add and change field1 to countryCode. Leave the Node Type set to Field.
- Click Add and change field2 to stateCode. Leave the Node Type set to Field.
- Click Add and change field3 to cityCode. Leave the Node Type set to Field.
- Click Add and change field4 to storeCode. Leave the Node Type set to Field.
- Click OK.
- Configure the Positional Properties of
each positional field. For each child node of the originCode node,
click the node in the Schema Layout tab and
at the end of the properties pane set the values of the Field
Offset and Field Length as shown
in the following list:
- countryCode -
- stateCode -
- cityCode -
- storeCode -
The Field Offset is calibrated from the current character location as the originCode string is being processed and not from the beginning of the originCode string for each field. In this example, the countryCode is processed and the first three characters of the originString are assigned to the countryCode. When the stateCode is processed, the current character location is already at the 3 character location, so the Field Offset of 1 of stateCode is added to the current character location to get the starting point for processing the stateCode string as shown in Figure 6:
Figure 6. Mixed Example 4 shows current cursor location and Field Offset. - countryCode -
- Determine the next level of structure in data. In this
example, the next level of structure are the repeating fields delimited
by exclamation points as shown in Figure 7: Figure 7. Mixed example 5 where delimited data is indicated by exclamation points.
- Configure the items node to parse
child repeating records using the following criteria:
- Separate the data into fields using a delimiter.
- Use exclamation points as delimiters to delimit any child fields.
- Expect the delimiter before the data.
- Expect one or more repeating child fields.
- Use exclamation points as delimiters to delimit the child repeating fields.
Using these criteria, specify the following values for the Basic Properties and Delimiters pane of the items node as shown in Figure 8:
Figure 8. Mixed example pane 3 where Child Delimiter Position is set to Before. - Add the repeating child field to the items node by right-clicking the items node in the Schema Layout tab and selecting the New Child > Field option from the list. The New Field dialog box opens.
- In Field Name enter item and click OK.
- Configure the item node to contain
one or more occurrences as shown in Figure 9: Figure 9. Mixed example pane 4 where Maximum Repeat is set to Unbounded.The Flat File Schema is complete and ready to test.
- Paste the following text into the left sub-pane of the
test pane:
BuyRight Pharmacy;USACAMVI334;!389!22;34980;
Taylor Drugs;USANJWLB128;!778!23!523;5667;
This data is also provided in the mixedData.txt file. If you accepted the defaults during the installation of Studio, this file is located in the following directory: C:\Program Files\IBM\WebSphere App Connect Studio 3.X\Samples\FlatFileSchemaAttention: Do not add a carriage return after the last line. - Click the Test icon. The Save Confirmation dialog box opens.
- Click Yes. The following XML representation
of the data is shown within the test pane:
<?xml version="1.0" encoding="UTF-8"?> <Order> <purchaseOrder> <storeName>BuyRight Pharmacy</storeName> <originCode> <countryCode>USA</countryCode> <stateCode>CA</stateCode <cityCode>MVI</cityCode> <storeCode>334</storeCode> </originCode> <items> <item>389</item> <item>22</item> </items> <orderId>34980</orderId> </purchaseOrder> <purchaseOrder> <storeName>Taylor Drugs</storeName> <originCode> <countryCode>USA</countryCode> <stateCode>NJ</stateCode> <cityCode>WLB</cityCode> <storeCode>128</storeCode> </originCode> <items> <item>778</item> <item>23</item> <item>523</item> </items> <orderId>5667</orderId> </purchaseOrder> </Order>
- In the Schema Layout tab, click
the item node. In the right sub-pane of the
test pane, the data parsed into the item node
is highlighted as shown in Figure 10: Figure 10. The item node is highlighted
- Click the state node to view the
data assigned into the state node. Note: A reference implementation of the Order Flat File Schema is provided in the FlatFileSchemasExample project. To view and open the FlatFileSchemasExample project, follow steps 1 and 2 in the Group Example and double-click the Order Flat File Schema. The nodes of the Order Flat File Schema are displayed in the Schema Layout tab and the properties of the Order root node show in the right pane.