User Tools

Site Tools


transformations:importfixedwidthtext

IMPORT FIXED WIDTH TEXT

Category: Import / File


Description

This action imports text files with columns of fixed width. The width and name of every column have to be defined explicitly in the transformation properties.

Use the preview pane (click the Show preview button) to visually split the text into columns. Click a character in the preview to create a splitter. Drag a column splitter left or right to adjust the column width.

Show preview: Preview the raw and imported versions of the dataset using the Show preview button at the top of the action settings window.


Use cases

Use the Import fixed width text file action when you have text-based data in which the fields are defined by positioning (spaces, tabs, etc.) rather than delimiting characters and you need to import them into columns.

If your data has delimiters, use the Import delimited text file action, instead. If your data is consolidated in a single field, and spacing between values is inconsistent, use the Import plain text action to read it in and use other actions to parse the individual values into columns.


Action settings

SettingDescription
Load file*Fully-qualified file name of the dataset (includes relative or absolute path).
EncodingASCII, ANSI (with code page), and other types of encoding. If you're not sure what to choose, try UTF-8 as it's the most common Unicode encoding.
First loaded line contains headersCheck this "on" if the first line of data contains the field names (headers). If unchecked, columns are given
generic "column" names unless user-assigned names are provided in the New columns section. see below.
New columnsRefer to the table below for new column settings.
Put remaining symbols into columnCheck "on" to capture any characters falling outside the final column's defined width. NOTE: If the
First loaded line contains headers is checked "on", this column will be named "Remainder" by default.
Otherwise, the user can provide the name for this column.

* Setting can be specified using a parameter.

New columns settings

SettingDescription
Add more columnsThis button will open a list where the columns in the imported file can be explicitly defined by Name and Width.
NOTE: Changes made here can be viewed in the preview pane by clicking the Show preview button at the top.
NameThe names to be assigned to the incoming columns.
WidthThe size, in characters, of the column. Characters that extend past the width of the final column are dropped unless the
Put remaining symbols into column option is used. See above.


Advanced options

SettingDescription
DecimalSelect whether decimal points are represented by a point (.) or comma (,).
Skip first linesDefines the number of rows in the incoming dataset to skip before importing data. Use this in cases where
leading rows may contain blank rows or metadata not to be included in the dataset.
Maximum number of lines to loadThe maximum number of rows to be imported. Use "0" to read in all ("unlimited") rows.

fixed_width_preview_pane.jpg

Importing multiple files

This action can import multiple files. See Importing Multiple Files for more information.


Examples

Example 1: You are importing data from a legacy system containing name/address data with the following parameters:

  • Fixed-width output
  • The first row contains header information
  • Column widths are known to be 20, 30, 15, 2, 5.

Input dataset:

Name                Address                       City           STZip
John Hardy          111 Any Street                Boston         MA02110
Amanda Smythe       16 East Valley Lane           Hanover        MD21076
Rick Johanssen      14 Sunset Ave, Apt 16B        Delaware City  DE19706

Use the following settings to import this dataset:

Column settings: Set these in the preview pane or enter them under New columns.
Column 1 Name is "Name" and the Width is 20
Column 2 Name is "Address" and the Width is 30
Column 3 Name is "City", and the Width is 15
Column 4 Name is "ST", and the Width is 2
Column 5 Name is "Zip", and the Width is 5
First loaded line contains headers (checked)

For safety, we will add a column to capture overflow characters:

Put remaining symbols into column (checked) This column will be named "Remainder" by default.

Output dataset:

NameAddressCitySTZip
John Hardy 111 Any Street Boston MA 02110
Amanda Smythe 16 East Valley Lane Hanover MD 21076
Rick Johanssen 14 Sunset Ave, Apt 16B Delaware City DE 19706



Example 2: You are presented with survey data that has been captured incorrectly - all values have been compressed into a single field:

  • There are 5, single-digit responses, all combined
  • The first row is a header row of question numbers (1-5)

Input dataset:

12345
14324
11232
45455
34234
55234

To expand these response values into their appropriate columns, use the following settings:

Column settings: Set these in the preview pane or enter them under New columns.
Column 1 Name is "1", and the Width is 1
Column 2 Name is "2", and the Width is 1
Column 3 Name is "3", and the Width is 1
Column 4 Name is "4", and the Width is 1
Column 5 Name is "5", and the Width is 1

Output dataset:

12345
1 4 3 2 4
1 1 2 3 2
4 5 4 5 5
3 4 2 3 4
5 5 2 3 4

In this case, you could also have used the Import plain text action to read the entire field into a single cell and parse it into separate columns later in the workflow.


Shortcuts

Fast ways to create the action:

  • Press the "Add data" menu button. Pick "Import file" or "Import multiple files".


See also

transformations/importfixedwidthtext.txt · Last modified: 2021/04/21 18:05 by craigt