Accessing files using Flat File Formats

Objectives

After completing this lesson, you will be able to:

  • Access files using flat file formats

File Formats

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.

ColumnDescription
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:

CodeDescription

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)

Let's try it

Let me guide you through the creation of your first file format.

Log in to track your progress & complete quizzes