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:
•Each file in the list must use the user-defined code page that is configured in the connection.
•Each file in the list must share the same file properties as configured in the connection.
•If you do not specify a path for a file, the task assumes that the file is in the same directory as the file list.
•Each path must be local to the Secure Agent.
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:
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:
•You must enter Windows commands that use parameters such as "/b" in a batch file.
•You must enter fully qualified file paths in each command, batch file, and shell script.
•You cannot use an in-out parameter for the file list 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.
1Create 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.
3On the Source tab, select a flat file connection.
4To use a manually created file list, perform the following steps:
aIn the Source Type field, select File List.
bIn the Object field, select the text file that contains the file list.
cOn the Fields tab, verify the incoming fields for the Source transformation.
Data Integrationextracts source fields from the first file in the file list. If the source fields are not correct, you can add or remove fields.
5To use a file list that is generated from a command, perform the following steps:
aIn the Source Type field, select Command.
bIn the Sample Object field, select the sample file from which Data Integration extracts source fields.
You can use one of the files you use to generate the file list as the sample file or select a different file.
cIn the Command field, enter the command that you use to generate the file list, for example, /home/dsmith/flatfile/parts/parts.sh.
dOn the Fields tab, verify the incoming fields for the Source transformation.
If the source fields are not correct, you can add or remove fields, or click the Source tab and select a different sample file.
6Optionally, 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.
1Create 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.
3On the Lookup Object tab, select a flat file connection.
4To use a manually created file list, perform the following steps:
aIn the Source Type field, select File List.
bIn the Lookup Object field, select the text file that contains the file list.
cOn the Return Fields tab, verify the return fields for the Lookup transformation.
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.
5To use a file list that is generated from a command, perform the following steps:
aIn the Source Type field, select Command.
bIn the Lookup Object field, select the sample file from which Data Integration extracts return fields.
You can use one of the files you use to generate the file list as the sample file or select a different file.
cIn the Command field, enter the command that you use to generate the file list, for example, /home/dsmith/flatfile/parts/parts.sh.
dOn the Return Fields tab, verify the return fields for the Lookup transformation.
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.