This topic demonstrates how to customize field lengths using an example Flat File Schema for a multiple-format, fixed-length flat file.
CIC Studio can generally estimate where fields should begin and end for single-format fixed-length flat files. However, the nature of multiple-format flat files, with each line containing fields of different lengths, often requires the need to customize the Schema fields.
The example will use this sample data, containing header, note, detail, and total information:
OPO_HDR 555552007060520070607111111111 BEST VALUE INC BOB SMITH 10
OPO_MSG Dot Foods * EDI and Fax
OPO_MSG Additional Message
OPO_NAM STTEST DISTRIBUTION CO 111111111 1234 MAIN STREET MORGANTOWN PA19534-9999
OPO_DTL 1UA002421500293 79535/802T 55555 1CA12 CT BEST VALUE POT HOLDER TERRY 8"X 8" 5 55.5500CA
OPO_DTL 2UA002421500282 379518 44444 1CA1 PAIR BEST VALUE 17" BESTGUARD OVEN MITT 3 3.3300CA
OPO_TTL 4 25 290.00
Alter Fields Page
You've created a new Flat File Schema. You've specified that it's fixed length. Then the Alter Fields panel appears.
If this was a single-format file, Studio would likely have all the fields neatly separated. But there are multiple formats and all the fields don't line up. Note how some fields are split in half, such as the word Fax being split, with F in Field2 and ax in Field3.
For now, focus only on the first format. Most importantly, make sure that whatever field denotes the record is the correct length. If the first field of the first format is what denotes the record, only adjust that field's length.
Also, always adjust fields from left-to-right. Adjusting a field affects the length of the field to the right of it, so if you don't start with the left-most field and go in order, you'll have re-adjusting to do.
Field1 denotes the format contents as header information. Click the Field1 header to highlight the fields. Then, use the arrows in the Field Length box to adjust Field1 to the correct length (7 characters).
In the Record ID panel, make sure the MultiFormatted Flat File checkbox is selected. By default, the Record ID is set to Field1. This should reflect the field that identifies what information is in the format, and it should be the same field for each format.
In our example, it is in fact Field1. When Studio reads from or writes to a file using this Schema, it will use this field to tell when to start a new format. OPO_HDR means start a new header format, OPO_MSG means start a new message format, and so on.
Here is where we will adjust all the fields for all the formats.
First, make sure all the records you want are where you want them in the Record Structure area on the left.
You can create additional record groups by clicking the Create Record Group button. You can remove records IDs or Record Groups by selecting them and clicking the Remove button.
You can add Record IDs from the Available Record IDs area on the right to the Record Structure. In the Available Record IDs area, click on the Record ID you want, then click the Add Record ID to Record Structure button. It will be added to whichever Record Group is highlighted in the Record Structure window.
Note: If you see additional, incorrect RecordIDs, you may have made the RecordID field too long, including characters from another field. Click Back until you reach the Alter Fieldspanel and re-adjust them.
In our example, we do not need to change the record structure.
We do, however, need to adjust the field lengths, field names, and make sure the data types are correct.
In the Record Structure area, select the first Record ID (OPO_HDR). Its fields appear in the Field Properties area. Here, you can click on a field heading to select it, then change the field name, data type, and field length.
In the Field Properties area, click the Field1 header to select it.
- In Field Name, type Header.
- Make sure Data Type is String.
- Change Field Length to 7.
You can change the field length by typing a new number in the Field Length field, using the arrows in the box, or by dragging the line separating the column headers to the correct position.
And remember: Always adjust fields from left-to-right.
Note: If a field has leading or trailing spaces, it cannot be an Integer or Decimal data type. It must be String.
In the Field Properties area, click the Field2 header to select it.
- In Field Name, type OrderNo.
- Make sure Data Type is String.
- Change Field Length to 7.
Adding and Removing Fields
If you run out of fields, click the last field to select it, then click the Insert a Field button to create an additional field. Then adjust the length of those fields.
If you have too many fields in a format, click the extra field to select it, then click the Remove a Field button.
Out of characters
If the Field column to the right of the Field you are editing only has one character, you won't be able to make the Field Length any larger.
To fix this, just click and drag the rightmost column to make it larger, and expand the other columns, going from right to left, until you have characters to spare in each. Then resume adjusting the field you wanted to lengthen as you normally would.
In the end, for the Header we've adjusted the following fields for this format (using _ to represent leading spaces):
- Field1 : Header - Length 7 - OPO_HDR
- Field2 : OrderNo - Length 7 - __55555
- Field3 : Date1 - Length 8 - 20070605
- Field4 : Date2 - Length 8 - 20070607
- Field5 : CompanyID - Length 9 - 111111111
- Field6 : Company - Length 22 - ________Best_Value_Inc
- Field7 : Contact - Length 23 - _____________Bob_Smith
- Field8 : InternalNo - Length 19 - _________________10
To do the next format, in the Record Structure area, click the next Record ID (OPO_MSG). Its sample fields will appear in the Field Properties area.
Repeat the process of selecting Field Headers, then changing the Field Name, Data Type, and Field Length for each field. Do this for each format.
Flat File Schema Editor
When you are finished and have named the Flat File Schema and saved it, all your changes will be reflected in the editor.
If anything is incorrect or if you didn't finish everything in the New Flat File Schema window, you can make all the same changes in the editor. You can also make changes to the Group constraints, and add Descriptions to groups or individual fields.