Text File Input
The Text file input step reads data from a variety of text-file types, including formats generated by spreadsheets and fixed width flat files. The features of the step allow you to read from a list of files or directories, use wild cards in the form of regular expressions, and accept genericized filenames from previous steps.
General
Enter the following information in the transformation step name field:
- Step name: Specify the unique name of the Text file input step on the canvas. You can customize the name or leave it as the default.
You can use Preview rows to display the rows generated by this step. The Text file input step determines what rows to input based on the information you provide in the option tabs. This preview function helps you to decide if the information provided accurately models the rows you are trying to retrieve.
Options
The Text file input step features several tabs with fields. Each tab is described below.
File tab
Use the File tab to enter the following connection information for your source.
| Option | Description |
| File or directory | Specify the source location if the source is not defined in a field.
Click Browse to display the Open File window and navigate to the file or folder. For the supported file system types, see Connecting to Virtual File Systems. Click Add to include the source in the Selected files table. If the source location is defined in a field, use the Accept filenames from previous steps to specify your file name. |
| Regular expression | Specify a regular expression to match filenames within a specified directory. |
| Exclude regular expression | Specify a regular expression to exclude filenames within a specified directory. |
Regular expressions
Use the Wildcard (RegExp) field in the File tab to search for files by wildcard in the form of a regular expression. Regular expressions are more sophisticated than using * and ? wildcards. This table describes several examples of regular expressions.
| File Name | Regular Expression | Files Selected |
| /dirA/ | .userdata.\.txt | Find all files in /dirA/ with names containing userdata and ending with .txt |
| /dirB/ | AAA.\* | Find all files in /dirB/ with names that start with AAA |
| /dirC/ | \[ENG:A-Z\]\[ENG:0-9\].\* | Find all files in /dirC/ with names that start with a capital and followed by a digit (A0-Z9) |
Selected files table
The Selected files table shows files or directories to use as source locations for input. This table is populated by clicking Add after you specify a File or directory. The input step tries to connect to the specified file or directory when you click Add to include it in the table.
The table contains the following columns:
| Column | Description |
| File/Directory | The source location indicated by clicking Add after specifying it in File or directory. |
| Wildcard (RegExp) | Specify a regular expression to match filenames within a specified directory. |
| Exclude wildcard | Specify a regular expression to exclude filenames within a specified directory. |
| Required | Required source location for input. |
| Include subfolders | Whether subfolders are included within the source location. |
Click Delete to remove a source from the table. Click Edit to remove a source from the table and return it back to the File or directory option.
Accept file names
You can specify your file name and pass it to the input step, which allows the file name to come from any source, such as a text file or database table.
| Option | Description |
| Accept filenames from previous step | Select to get file names from previous steps. |
| Pass through fields from previous step | Select to get field information from previous steps. |
| Step to read file names from | Enter the name of the step from which to read the file names. |
| Field in the input to use as filename | Enter the name of the field in the input step to determine which file name to use. |
Show action buttons
When you have entered information in the File tab fields, select an action button if you want to look at the source file names or data content.
| Button | Description |
| Show filename(s) | Select to display the file names of the sources connected to the step. |
| Show file content | Select to display the raw content of the selected file. |
| Show content from first data line | Select to display the content from the first data line for the selected file. |
Content tab
In the Content tab, using the following options, you can specify the format of the source files.
| Option | Description |
| Filetype | Select either CSV or Fixed length. Depending on the file type you select, a corresponding interface appears when you click Get Fields in the Fields tab. |
| Separator | Specify the character used to separate the fields in a single line of text, typically a semicolon or tab. Click Insert Tab to place a tab in the Separator field. The default value is semicolon (;). |
| Enclosure | Specify an optional character used to enclose a field if that field contains a separator character. The default value is double quotation marks ("). |
| Allow breaks in enclosed fields | Not implemented. |
| Escape | Specify one or more characters to indicate if another character is a part of a regular text. For example, if a backslash (\) is the escape character and a single quote (') is an enclosure or separator character, then the text Not the nine o\’clock news is parsed as Not the nine o’clock news. |
| Header | Select if your text file has a header row (first lines in the file). You can use Number of header lines to specify the number of times the header line appears. |
| Footer | Select if your text file has a footer row (last lines in the file). You can use Number of footer lines to specify the number of times the footer row appears. |
| Wrapped lines | Select if you work with data lines that have wrapped beyond a specific page limit. You can use Number of times wrapped to specify the number of times the line is wrapped. Headers and footers are never considered wrapped. |
| Paged layout (printout) | Select when other text handling options (above) fail on a text file designed to be output to a line printer. You can use Document header lines to skip introductory texts and Number of lines per page to position the data lines. |
| Compression | Select if your text file is in a ZIP or GZip archive. Only the first file in the archive is read. |
| No empty rows | Select if you do not want to send empty rows to the next steps. |
| Include filename in output | Select if you want the file name to be part of the output, and use Filename fieldname to enter the name of the field that contains the file name. |
| Rownum in output | Select if you want the row number to be part of the output. You can use Rownum fieldname to enter the name of the field that contains the row number. Select Rownum by file if you want to allow the row number to be reset per file. |
| Format | Select the file format, which can be either DOS, UNIX, or mixed. UNIX files have lines terminated by line feeds. DOS files have lines separated by carriage returns and line feeds. If you specify mixed, no verification is done. |
| Encoding | Select the text file encoding to use. Leave blank to use the default encoding on your system. To use Unicode, specify UTF-8 or UTF-16. On first use, the PDI client searches your system for available encodings. |
| Length |
Select the length of the field according to its type:
|
| Limit | Specify a limit on the number of records generated from this step. Specify zero (0) for an unlimited number of records. |
| Be lenient when parsing dates? | Clear the check box if you want strict parsing of data fields. If selected, dates like Jan 32nd become Feb 1st. |
| The date format Locale | Specify the locale to use to parse dates written in full, such as February 2nd, 2006. For example, parsing February 2nd, 2006, on a system set to French (fr_FR) would not work because February is called Février in that locale. |
| Add filenames to result | Select to add file names to a resulting list of file names. |
Error Handling tab
In the Error Handling tab, you can specify how the step reacts when errors occur, such as malformed records, bad enclosure strings, wrong number of fields, and premature line ends. The following table contains options for error handling:
| Option | Description |
| Ignore errors? | Select if you want to ignore errors during parsing. |
| Skip error files? | Select if you want to skip those files that contain errors. You can generate a file that contains a listing of files where the errors occur. Otherwise, files with errors are not skipped, and the files that have parsing errors are empty (null). |
| Error file field name | Specify an error file name if you want to add field names where errors were occurred. |
| File error message field name | Specify an error message field name if you want to add field names where errors occurred in the error file. |
| Skip error lines? | Select if you want to skip those lines that contain errors. You can generate an extra file that contains the line numbers where the errors occur. Otherwise, lines with errors are not skipped, and the fields that have parsing errors are empty (null). |
| Error count fieldname | Specify the field name if you want to add a field containing the number of errors on the line to the output rows. |
| Error fields fieldname | Specify the field name if you want to add a field containing the names of fields where errors occurred to the output rows. |
| Error text fieldname | Specify the field name if you want to add a field containing descriptions of the parsing error occurrences to the output rows. |
| Warning files directory | Specify the location of the directory where warnings are placed if they are generated. The name of the resulting file is <warning dir>/filename.<date_time>.<warning extension>. |
| Error files directory | Specify the location of the directory where errors are placed if they occur. The name of the resulting file is <errorfile_dir>/filename.<date_time>.<errorfile_extension>. |
| Failing line numbers files directory | Specify the location of the directory where parsing errors on a line are placed if they occur. The name of the resulting file is <errorline dir>/filename.<date_time>.<errorline extension>. |
Filters tab
The Filters tab contains a table with the columns where you can specify the lines you want to skip in the text file.
| Column | Description |
| Filter string | The string that you want to search for. |
| Filter position | The position where the filter string must be placed in the line. Zero (0) is the first position in the line. If you specify a value below zero (0), the filter string is searched for in the entire string. |
| Stop on filter | Enter Y if you want to stop processing the current text file when the filter string is encountered. Enter N to continue processing after encountering the string. |
| Positive match | Enter Y if you want to process lines that match the filter string. Enter N to ignore matching lines. |
Fields tab
In the Fields tab, you can specify the information about the name and format of the fields being read from the text file.
| Option | Description |
| Name | Name of the field. |
| Type | Type of the field can be either String, Date, or Number. |
| Format | See Number formats for a complete description of format symbols. |
| Position | The position is needed when processing the Fixed filetype. It is zero-based, so the first character is starting with position 0. |
| Length |
The value of this field depends on format:
|
| Precision |
The value of this field depends on format:
|
| Currency | Used to interpret numbers such as $10,000.00 or E5.000,00. |
| Decimal | A decimal point can be a period (.) as in 10,000.00 or it can be a comma (,) as in 5.000,00. |
| Group | A grouping can be a comma (,) as in 10,000.00 or a period (.) as in 5.000,00. |
| Null if | Treat this value as null. |
| Default | Default value in case the field in the text file was not specified (empty). |
| Trim type |
Trim the type before processing. You can specify one of the following options:
|
| Repeat | If the corresponding value in this row is empty, repeat the one from the last time it was not empty (Y or N). |
See Understanding PDI data types and field metadata to maximize the efficiency of your transformation and job results.
Number formats
Use the following table to specify number formats. For further information on valid numeric formats used in this step, view the Number Formatting Table.
| Symbol | Location | Localized | Meaning |
| 0 | Number | Yes | Digit. |
| # | Number | Yes | Digit, zero shows as absent. |
| . | Number | Yes | Decimal separator or monetary decimal separator. |
| - | Number | Yes | Minus sign. |
| , | Number | Yes | Grouping separator. |
| E | Number | Yes | Separates mantissa and exponent in scientific notation. Need not be quoted in prefix or suffix. |
| ; | Subpattern boundary | Yes | Separates positive and negative patterns. |
| % | Prefix or suffix | Yes | Multiply by 100 and show as percentage. |
| ‰(/u2030) | Prefix or suffix | Yes | Multiply by 1000 and show as per mille. |
| ¤ (/u00A4) | Prefix or suffix | No | Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. |
| ‘ | Prefix or suffix | No | Used to quote special characters in a prefix or suffix, for example, '#'# formats 123 to #123. To create a single quote itself, use two in a row: # o''clock. |
Scientific notation
In a pattern, the exponent character immediately followed by one or more digit characters indicates scientific notation, for example, 0.###E0 formats the number 1234 as 1.234E3.
Date formats
Use the following table to specify date formats. For further information on valid date formats used in this step, view the Date Formatting Table.
| Letter | Date of Time Component | Presentation | Examples |
| G | Era designator | Text | AD |
| y | Year | Year | 1996 or 96 |
| M | Month in year | Month | July, Jul, or 07 |
| w | Week in year | Number | 27 |
| W | Week in Month | Number | 2 |
| D | Day in year | Number | 189 |
| d | Day in month | Number | 10 |
| F | Day of week in month | Number | 2 |
| E | Day in week | Text | Tuesday or Tue |
| a | am/pm marker | Text | PM |
| H | Hour in day (0-23) | Number 0 | n/a |
| k | Hour in day (1-24) | Number 24 | n/a |
| K | Hour in am/pm (0-11) | Number 0 | n/a |
| h | Hour in am/pm (1-12) | Number 12 | n/a |
| m | Minute in hour | Number 30 | n/a |
| s | Second in minute | Number 55 | n/a |
| S | Millisecond | Number 978 | n/a |
| z | Time zone | General time zone | Pacific Standard Time, PST, or GMT-08:00 |
| Z | Time zone | RFC 822 time zone | -0800 |
Additional output fields tab
The Additional output fields tab contains the following options to specify additional information about the file to process.
| Option | Description |
| Short filename field | Specify the field that contains the filename without path information but with an extension. |
| Extension field | Specify the field that contains the extension of the filename. |
| Path field | Specify the field that contains the path in operating system format. |
| Size field | Specify the field that contains the size of the data. |
| Is hidden field | Specify the field indicating if the file is hidden or not (Boolean). |
| Last modification field | Specify the field indicating the date of the last time the file was modified. |
| Uri field | Specify the field that contains the URI. |
| Root uri field | Specify the URI output field name. |
Metadata injection support
All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.