Splitting a File Pattern

You can split a file pattern to load files or groups of files into separate tables.

  1. In the File Pattern column of the transfer table, click the Split Pattern icon. You can also right-click any entry with a file pattern and select Split Pattern. The Split Pattern dialog appears.
    File patterns are split by entering a GLOB expression that matches all or a subset of the file pattern files. Each asterisk (*) and sequence of question marks (?) defines a part of the file name that can be used to group files together and define a destination table name. If there is more than one extracted part of the file name, you can select which parts to use. Parts are selected in the Table Name Parts table header if more than one part is available for selection.
  2. Click Add to split the file pattern. The file pattern is split into multiple Split Pattern entries. The source in each entry may be a new file pattern or a file path, depending on the pattern split that you have defined. During execution, every file that matches the split pattern source (instead of the file pattern) is loaded into the selected destination table. Split pattern entries are determined during project design and not during project execution.
    Square Bracket Notation (SBN) expressions are not supported in the file name pattern of a split pattern entry. If a file pattern containing an SBN expression is split, the SBN expression is only evaluated when designing the split. The split pattern entries do not contain any SBN expressions. For example, if you split the file pattern, "[uGetEnv('MYDIR')]/[uGetEnv('MYPATTERN')]" with pattern "*.txt", the resulting split pattern entries is: [uGetEnv('MYDIR')]/<value of MYPATTERN>.txt.
    Here are some examples for splitting file patterns.

    Example 1: Loading into distinct tables

     
File Pattern: C:\data\*
Split Pattern: *.txt
Example file names: SALES.txt, ORDERS.txt, ...
Table Name Part: (only one available, automatically selected)
Destination Tables: SALES, ORDERS, ...
This loads each *.txt file in the C:\data\ directory into a distinct destination table.

    Example 2: Load into tables based on the table name part of a file name. This example creates split pattern entries sources such as, SALES_*.txt, ORDERS_*.txt, and so on.

    File Pattern: C:\data\*.txt
Split Pattern: *_*.txt
Example file name: SALES_2010-01-01.txt, SALES_2010-02-01.txt, ORDERS_2010-01-01.txt, 
ORDERS_2010-02-01.txt
Table Name Part: First part selected
Destination Tables: SALES, ORDERS, ...
This loads all files with the same values in the selected parts into the same destination table.  
The selected parts also determine the default destination table name.
  3. To split a file pattern more than once, right-click a split pattern entry and select Split Pattern. When you split a pattern more than once, you actually split the file pattern, not the split pattern entries.
  4. To remove a pattern split, right-click an entry and select Remove Pattern Split.
  5. To modify a pattern split, click Edit in the File Pattern cell of an entry.
Parent topic: Reviewing the Source and Destination Table Details