File formats describe the structure of the text file – for instance, column names, data types, delimiters, etc.
The file formats are stored in the local object library, very similar to the datastores. Setting these up takes a bit more work, but having a good sample file available can make it easier and faster.
Note
This course focuses on file formats of type Delimited. For information about the other types (Fixed width, SAP Transport, Unstructured), please refer to Unstructured file formats | SAP Help Portal
The File Format Editor
When creating (or editing) a file format in the local object library, three areas are displayed:
- Properties – The left side of the editor screen where overall file definition and properties are shown.
- Column Attributes – Upper right. Shows the field names, data types, sizes and formats
- Data Preview – Lower right. Displays first 20 rows of data from sample file, if available.
Main Mandatory Properties
When creating a new file format, those properties are mandatory and determine how the file will be processed by Data Services:
- Name: Name of the file format. This needs to be unique.
- Type : Is the file delimited or fixed length for example.
- Delimiters: In case of a delimited file, you need to specify which are the delimiters.
- Skip row header and Skipped rows: If the file has a header row or if some of the first rows should not be read.
Delimiters Indicate text, row and column delimiters in your file formats so Data Services can determine when a new record starts and what separates column values. If the delimiter is not available on the drop-down, you can enter it manually (for example the "|" or "pipe") . For more information, you can refer to File format delimiters options | SAP Help Portal
File Location
If you are using a sample file, you must specify its location.
It can either be:
- local (relative to where Data Services Designer client is installed)
- on the job server
You then need to specify root directory and file name.
Columns
For source files, specify the structure of each column in the Column Attributes work area. Be aware that columns do not need to be specified for files that will only be used as targets. If the columns are specified, and they do not match the output schema from the preceding transform, Data Services writes to the target file using the transform’s output schema.
In the column attributes, you can define each column data type and specific format. If you have used a sample file, those will be inferred from the file content but can be changed if needed.
| Column | Description |
|---|---|
| Field Name | Enter the name of the column. |
| Data Type | Select the appropriate data type from the drop-down list. |
| Field Size | For columns with a data type of varchar, specify the length of the field. |
| Precision | For columns with a data type of decimal or numeric, specify the precision of the field. |
| Scale | For columns with a data type of decimal or numeric, specify the scale of the field. |
| Format | For columns with any data type but varchar, select a format for the field, if desired. This information overrides the default format set in the Property Values work area for that data type. |
For Date columns you can specify a default format or specific formats for each column if those are different.
Here are the codes you can use to define the date format:
| Code | Description |
|---|---|
DD | 2-digit day of the month |
MM | 2-digit month |
MONTH | Full name of the month |
MON | 3-character name of the month |
YY | 2-digit year |
YYYY | 4-digit year |
HH24 | 2-digit hour of the day (0-23) |
MI | 2-digit minute (0-59) |
SS | 2-digit second (0-59) |
FF | Up to 9-digit subseconds |
Checking your entries
One way of knowing if you have correctly defined the properties, data types and formats is by looking at the sample data of the File Format Editor.
File Format Instance properties
Some of the properties you define in the File Format can be overridden each time you use the file format in a data flow (create a new instance of it).
- File location
- Error handling options
It means that one File Format could be used for different files in the same data flow or in different data flows.
Multiple files (sharing same schema and location, but different names) can be read at execution time. In the File name(s) box, either enter each file name delimited with commas (example orders01.txt, orders02.txt, orders03.txt) OR use wildcard characters (* for any number of characters, including none; ? For exactly one single character) in place of the variable part of the file name (example orders*.txt)