While datastores are used for databases and applications, you must create flat file formats to access data stored in text files.
File formats define the structure of a text file, including details such as column names, data types, and delimiters.
Like datastores, file formats are stored in the local object library. Although setting them up requires more initial effort, using a sample file can significantly speed up the process.
Note
This course focuses on delimited file formats. For information regarding other types, such as fixed width, SAP Transport, or unstructured files, refer to Unstructured file formats | SAP Help Portal.
The File Format Editor
The File Format Editor is divided into three main areas:
- Properties: Located on the left, this area displays the overall file definition and general properties.
- Column Attributes: Located in the upper right, this area lists field names, data types, sizes, and formats.
- Data Preview: Located in the lower right, this area displays the first 20 rows of data from the sample file, if one is provided.
Mandatory Properties
When creating a new file format, the following mandatory properties determine how SAP Data Services processes the file:
- Name: A unique identifier for the file format.
- Type: The format category, such as delimited or fixed length.
- Delimiters: For delimited files, you must specify the characters used to separate data.
- Skip row header and Skipped rows: Used to ignore header rows or specific lines at the beginning of the file.
Delimiters indicate the text, row, and column separators. This allows SAP Data Services to identify where records start and how column values are divided. If a specific delimiter is not in the drop-down list, you can enter it manually (for example, the pipe symbol "|"). For more details, see File format delimiters options | SAP Help Portal.
File Location
If you use a sample file, you must specify its location. The location can be:
- Local: Relative to the SAP Data Services Designer client installation.
- On the job server.
You must also provide the root directory and the file name.
Columns
For source files, define the structure of each column in the Column Attributes area. Note that you do not need to specify columns for files used exclusively as targets. If target columns are specified but do not match the output schema of the preceding transform, SAP Data Services will use the transform's schema to write the file.
In the column attributes, you can define data types and specific formats. If you use a sample file, these attributes are automatically inferred but can be modified manually.
| Column | Description |
|---|---|
| Field Name | The name of the column. |
| Data Type | The data type selected from the drop-down list. |
| Field Size | The length of the field (required for varchar types). |
| Precision | The total number of digits for decimal or numeric types. |
| Scale | The number of digits to the right of the decimal point for decimal or numeric types. |
| Format | The specific display or storage format for non-varchar types. This overrides the default format set in the Property Values area. |
For date columns, you can specify a global default format or unique formats for individual columns.
Use the following codes to define date and time formats:
| Code | Description |
|---|---|
DD | 2-digit day of the month |
MM | 2-digit month |
MONTH | Full name of the month |
MON | 3-character abbreviated month name |
YY | 2-digit year |
YYYY | 4-digit year |
HH24 | 2-digit hour (0-23) |
MI | 2-digit minute (0-59) |
SS | 2-digit second (0-59) |
FF | Subseconds (up to 9 digits) |
Validating Your Configuration
To verify that properties, data types, and formats are correctly defined, check the Data Preview section in the File Format Editor.
File Format Instance Properties
When you use a file format in a data flow, you create an instance of it. Certain properties can be overridden at the instance level:
- File location
- Error handling options
This flexibility allows a single file format to represent different physical files within the same or different data flows.
You can also process multiple files with the same schema and location simultaneously. In the File name(s) field, list names separated by commas (e.g., orders01.txt, orders02.txt) or use wildcards.
- * represents any number of characters.
- ? represents exactly one character.
Summary
In this lesson, you learned about SAP Data Services flat file formats.
- File formats define text file structure including column names, data types, and delimiters for accessing flat file data.
- File mandatory properties include Name, Type, Delimiters, and row-skipping options to control how Data Services processes files.
- Column attributes define data structure with field names, data types, sizes, and formats, auto-inferred from sample files but manually adjustable.
- File format instances allow flexibility by overriding location and error handling properties, supporting multiple files via wildcards or comma-separated lists.