Fixed Width Data In Source And Target Text Files
Text files can be used as a source or target datastore and are accessed using the Text File As A Source or Text File As A Target Connector. On the Source Text Connection — Entities Tab or the Target Text Connection — Entities Tab of the Text Connection dialog you can indicate that the text file you are using is Fixed Width. If you are using text files that contain Fixed Width data there are the following additional requirements:
- Field names must be unique.
- If the first character in the heading is whitespace, all of the field names are right-justified. If the first character is not whitespace, the field names are left-justified. Whitespace refers to space, tabs, or certain Unicode characters identified as blanks.
- You cannot specify a field delimiter or text qualifier for fixed width data. Commas and quotation marks are not parsed as special characters.
- If Data is selected for the First Row setting on the Text Connection Entities tab, add spaces to each field name on the Text Connection Fields tab to ensure that it is as long as the first field in that column.
Fixed Width Data Examples
With Data selected for the First Row setting in the Text Connection Entities tab, TIBCO Scribe® Online assigns the initial field width for each field to be the width from the start of one field to the beginning of the next field. You can see this using the Query Block Preview tab in a Map that uses the Text as a Source Connection. See Block Properties Preview Tab.
For the last field, the field is the width of the header. For example, if you assume 5 spaces between each heading:
FirstName LastName Age Hadrian Wall Unspecified Joan Darcy 53
The field widths are:
- FirstName = 14 (9+5)
- LastName = 13 (8+5)
- Age = 3, and the data in the first row is truncated to "Uns"
To fix the truncated field, open the Fields tab, select the Age field, and change its length to at least 12 characters.
The last column now displays the expected data, Unspecified.
- If Field Names is not selected for the First Row setting, TIBCO Scribe® Online creates a single field, called Field1 on the Fields tab. You can also see this using the Query Block Preview tab in a Map that uses the Text as a Source Connection. See Block Properties Preview Tab.
Create Sample Data Fields
- Open the Fields tab on the Text Connector Connection dialog.
- From the Fields pane on the right, use the Gear menu
for Field1 to add two new fields, as follows:
- LastName, Text, Width= 13, Trim = Both sides
- Age, Text,Width = 3, Trim = None
- Now, go back and rename Field1 to FirstName, Text, Width = 14, Trim = Both sides
- Select OK to save your Connection.
- Use the Query Block Preview tab in a Map that uses the Text as a Source Connection to verify. See Block Properties Preview Tab.
- Your data should display in the three columns that you expect.
Note: Do not create fixed width files by exporting data from Microsoft Excel to a .prn file. Saving in .prn can cause errors in your data.
Related Topics
Source Text Connection — Fields Tab
