Transformations > Transformations > File lists
  

File lists

You can use a file list as a source for flat file connections. A file list is a file that contains the names and directories of each source file that you want to use in a mapping. Use a file list to enable a task to read multiple source files for one source object in a mapping.
For example, you might want to use a file list if your organization collects data for multiple locations that you want to process through the same mapping.
You configure a source object such as a Source transformation or Lookup transformation to read the file list. You can also write the source file name to each target row. When you run a mapping that uses a file list, the task reads rows of data from the different source files in the file list.
Use the following rules and guidelines when you create a file list:
You can create a file list manually or you can use a command to create the file list.

Manually created file lists

To create a file list manually, create the list in a text editor and save it as a text file. Place the text file in a directory that is local to the Secure Agent.
When you create the file list, enter one file name or one file path and file name on each line. Data Integration extracts the field names from the first file in the file list.
The following example shows a valid Windows file list:
customers_canada.dat
C:\Customer_Accounts\customers_us.dat
C:\Customer_Accounts\customers_uk.dat
C:\Customer_Accounts\customers_india.dat
In the previous example, Data Integration extracts the field names from the customers_canada.dat file which resides in the same folder as the file list.

File list commands

You can use a command to generate a list of source files for a mapping. You can use a valid DOS or UNIX command, batch file, or shell script. Data Integration reads each file in the list when the task runs.
Use a command to generate a file list when the list of source files changes often or when you want to generate a file list based on specific conditions. For example, you can use a command to generate a file list from all files in a directory or based on the file names.
Use the following guidelines when you generate a file list through a command:

UNIX Example with Shell Script

You need to extract data from parts lists that are stored on a Linux machine. The parts lists are text files that are stored in the /home/dsmith/flatfile/parts directory.
The following table shows the command that you enter in the Source transformation and the contents of the corresponding shell script:
Command
Shell Script (parts.sh)
/home/dsmith/flatfile/parts/parts.sh
cd /home/dsmith/flatfile/parts
ls *.txt

Windows Example with Batch File

You need to extract data from sales records that are stored on a Windows machine. The sales record files are stored in the C:\SalesRecords directory and use the naming convention SalesRec_??-??-2017.txt.
The following table shows the command that you enter in the Source transformation and the corresponding batch file contents:
Command
Batch File (SalesSrc.bat)
C:\SalesRecords\SalesSrc.bat
@echo off
cd C:\SalesRecords
dir /b SalesRec_??-??-2017.txt

Example without Shell Script or Batch File

You can also generate a file list through a command instead of through a batch file or shell script. For example, the following command generates a file list that contains one file named source.csv:
echo C:\sources\source.csv

Command sample file

When you generate a file list through a command, you select a sample file that Data Integration uses to extract the field names. Data Integration does not extract data from the sample file unless the sample file is included in the generated file list.
If a file in the generated file list does not contain all fields in the sample file, Data Integration sets the record values for the missing fields to null. If a file in the file list contains fields that are not in the sample file, Data Integration ignores the extra fields.
For example, the sample file that you select contains the fields CustID, NameLast, and NameFirst. One file in the generated file list does not contain the NameFirst field. When Data Integration reads data from the file, it sets the first names for each record in the file to null.
Another file in the generated file list contains the fields CustID, NameLast, NameFirst, and PhoneNo. Data Integration does not import records for the PhoneNo field because the field is not in the sample file. If you want to import the phone numbers, either select a sample file that contains the PhoneNo field, or add a field for the phone numbers in the transformation.

Using a file list in a Source transformation

To use a file list in a Source transformation, create the text file, batch file, or shell script that creates the file list. Then configure the Source transformation to use the file list.
    1. Create the text file, batch file, or shell script that creates the file list and install it locally to the Secure Agent.
    2. In the Mapping Designer, select the Source transformation.
    3. On the Source tab, select a flat file connection.
    4. To use a manually created file list, perform the following steps:
    1. a. In the Source Type field, select File List.
    2. b. In the Object field, select the text file that contains the file list.
    3. c. On the Fields tab, verify the incoming fields for the Source transformation.
    4. Data Integration extracts source fields from the first file in the file list. If the source fields are not correct, you can add or remove fields.
    5. To use a file list that is generated from a command, perform the following steps:
    1. a. In the Source Type field, select Command.
    2. b. In the Sample Object field, select the sample file from which Data Integration extracts source fields.
    3. You can use one of the files you use to generate the file list as the sample file or select a different file.
    4. c. In the Command field, enter the command that you use to generate the file list, for example, /home/dsmith/flatfile/parts/parts.sh.
    5. d. On the Fields tab, verify the incoming fields for the Source transformation.
    6. If the source fields are not correct, you can add or remove fields, or click the Source tab and select a different sample file.
    6. Optionally, to write the source file name to each target row, click the Fields tab, and enable the Add Currently Processed Filename field option.
    The CurrentlyProcessedFileName field is added to the fields table.

Using a file list in a Lookup transformation

To use a file list in a Lookup transformation, create the text file, batch file, or shell script that creates the file list. Then configure the Lookup transformation to use the file list.
    1. Create the text file, batch file, or shell script that creates the file list and install it locally to the Secure Agent.
    2. In the Mapping Designer, select the Lookup transformation.
    3. On the Lookup Object tab, select a flat file connection.
    4. To use a manually created file list, perform the following steps:
    1. a. In the Source Type field, select File List.
    2. b. In the Lookup Object field, select the text file that contains the file list.
    3. c. On the Return Fields tab, verify the return fields for the Lookup transformation.
    4. Data Integration extracts the return fields from the first file in the file list. If the return fields are not correct, you can add or remove fields.
    5. To use a file list that is generated from a command, perform the following steps:
    1. a. In the Source Type field, select Command.
    2. b. In the Lookup Object field, select the sample file from which Data Integration extracts return fields.
    3. You can use one of the files you use to generate the file list as the sample file or select a different file.
    4. c. In the Command field, enter the command that you use to generate the file list, for example, /home/dsmith/flatfile/parts/parts.sh.
    5. d. On the Return Fields tab, verify the return fields for the Lookup transformation.
    6. If the return fields are not correct, you can add or remove fields, or click the Lookup Object tab and select a different sample file.