In my next two blogs, I will be discussing a common challenge facing EXTOL users — the handling of proprietary flat file data received from trading partners. The flat file trend is becoming more popular and, more importantly, being forced on users by their trading partners. We’ve seen a trend where you are either forced to handle the data in the format it is presented or lose the business. Another side of the increase in processing flat file data is to accommodate smaller “Mom & Pop” shops without the means to present the data in a better format.
Let’s explore the different flavors of flat file data. Flat file data can fall into one of two format types: single format or multiple format. Single format files have a common record layout throughout the entire payload data. This means, for example, that every record in the payload has the same exact layout, all of the fields are identified in the same manner in every record and every record of data is treated the same way in the pending data transformation. Multiple format files contain more than a single record layout throughout the payload. This means you will see multiple record layouts needing to be identified and treated as different records in the pending transformation.
A good example of multiple format data is any business document in flat file form having distinct header vs. detail records. Multiple format flat file data must also contain a record identifier as part of the data so records can be identified and categorized into the correct layout or format. To stay uniform with our previous example, maybe the file would contain an “H” or “D” in the first field position to identify the record as being header or detail.
Also, within each of the two categories of flat file data, there are two field types: fixed length and delimited. Fixed length flat file records contain data split into fields based on their position in the record itself. For example, field X will always be located in positions 15 to 20 of the record. Delimited flat file records contain fields that are separated by a uniform delimiter throughout the payload.
Before continuing, let’s look at an example of each type of flat file data:
SINGLE FORMAT FIXED
Widget_1 12345 $5.50 Description of Widget_1
Widget_2 67890 $9.50 Description of Widget_2
* All records fall into the same format, and the fields are identified by their positions in the record.
SINGLE FORMAT DELIMITED
Widget_1,12345,$5.50,Description of Widget_1
Widget_2,67890,$9.50,Description of Widget_2
* All records fall into the same format, fields separated by a uniform delimiter, a comma in this case.
MULTIPLE FORMAT FIXED
DTL$5.50 Description of Widget_1
DTL$9.50 Description of Widget_2
* More than one record format, in this case distinct header and detail formats, each identified by the first three positions of the record and each with a distinct field layout.
MULTIPLE FORMAT DELIMITED
DTL,$5.50,Description of Widget_1
DTL,$9.50,Description of Widget_2
* More than one record format, header and detail, each identified by the value in the first field position of the record. Each record has a different field layout.
EXTOL Business Integrator (EBI) users are armed with the tools needed to accommodate any flat file request. Once you have visually identified the format type (single or multiple) and field type (fixed or delimited), you can pass that information along with a sample of the data in to EBI’s flat file wizard in order to generate a corresponding schema identifying the file. The flat file wizard can build a complete schema that can be used for mapping in rulesets.
The flat file wizard is a tool that will truly save hours for those creating rulesets in EBI. In my next blog, I will discuss another EBI tool for dealing with flat files in EBI — the record fragment.