The create file format wizard saves the format of a flat
file. While creating a new file format, you identify the type of the
flat file (delimited, fixed-length, or PC/IXF), define the column
names, data types, the delimiter characters and so on. You save the
file format inside your data warehousing project and then load the
format in the file source operator to import data from the same kind
of file. The create file format wizard contains the following properties.
- File type
- Select an option from the list to specify the file format:
- Delimited row and delimited column format
- Stores data by using special character delimiters, such as the
comma and semicolon, to separate column values. The default column
delimiter is a comma. The default string delimiter is a double quotation
mark.
- Fixed row length and fixed column length format
- Uses fixed lengths for each column and row. The lengths of columns
and rows are defined in the metadata at the beginning of the file.
- Integrated exchange format (PC, IXF)
- Uses properties to describe the schema of the virtual table, including
column names and data types, after extraction from the flat file.
Throughout the remainder of the data flow, rows and columns of data
from the file source operator can be manipulated and transformed as
if the data came from the relational table.
- Field delimiter
- Select one of the following options used in the file to separate
the column values:
- <default>
- <TAB>
- ,
- ~
- %
- &
- '
- *
- (
- )
You can also type a value for the delimiter in this field. The
delimiter must be a single-byte character.
- String delimiter
- Select an option to separate the string values:
- Date format
- Select one of the following options or enter a format string that
conforms to the specifications in the DB2 documentation:
- NONE
- No date format.
- MM/DD/YYYY
- The date contains two digits that range from 01 to 12 for the
month, two digits that range from 01 to 31 for the day, and four digits
that range from 0000 to 9999 for the year.
- DD/MM/YYYY
- The date contains two digits that range from 01 to 31 for the
day, two digits that range from 01 to 12 for the month, and four digits
that range from 0000 to 9999 for the year.
- YYYY.MM.DD
- The date contains four digits that range from 0000 to 9999 for
the year, two digits that range from 01 to 12 for the month, and two
digits that range from 01 to 31 for the day.
- MM.DD.YYYY
- The date contains two digits that range from 01 to 12 for the
month, two digits that range from 01 to 31 for the day, and four digits
that range from 0000 to 9999 for the year.
- YYYYDDD
- The date contains four digits that range from 0000 to 9999 for
the year and three digits for the day of the year, ranging from 001
to 366.
- MM-DD
- The date contains two digits that range from 01 to 12 for the
month and two digits that range from 01 to 31 for the day.
- Time format
- Select one of the following options or enter a format string that
conforms to the specifications in the DB2 documentation:
- NONE
- No time format.
- HH:MM:SS
- The time contains two digits that range from 01 to 12 for a 12-hour
system or 00 to 23 for a 24-hour system; two digits that range from
00 to 59 for the minute, and two digits that range from 00 to 59 for
the second.
- HH:MM:SS TT
- The time contains two digits that range from 01 to 12 for a 12-hour
system or 00 to 23 for a 24-hour system; two digits that range from
00 to 59 for the minute; two digits that range from 00 to 59 for the
second; and two characters, AM or PM, for the meridian indicator (TT).
- H.MM TT
- The time contains one or two digits that range from 1 to 12 for
a 12-hour system or 0 to 23 for a 24-hour system; two digits that
range from 00 to 59 for the minute; and two characters, AM or PM,
for the meridian indicator (TT).
- SSSSS
- The time contains five digits that range from 00000 to 86399 for
the second of the day after midnight.
- Time stamp format
- Select one of the following options or enter a format string that
conforms to the specifications in the DB2 documentation:
- NONE
- No time stamp.
- YYYY/MM/DD HH:MM:SS.UUUUUU
- The time stamp contains four digits that range from 0000 to 9999
for the year, two digits that range from 01 to 12 for the month, two
digits that range from 01 to 31 for the day, two digits that range
from 01 to 12 for a 12-hour system, or 00 to 23 for a 24-hour system,
two digits that range from 00 to 59 for the minute, two digits that
range from 00 to 59 for the second, and six digits that range from
000000 to 999999 for the microsecond.
- YYYY/MM/DD HH
- The time stamp contains four digits ranging from 0000 to 9999
for the year, two digits ranging from 01 to 12 for the month, two
digits ranging from 01 to 31 for the day, and two digits ranging from
01 to 12 for a 12-hour system, or 00 to 23 for a 24-hour system.
- YYYY-MM-DD HH:MM:SS TT
- The time stamp contains four digits ranging from 0000 to 9999
for the year, two digits ranging from 01 to 12 for the month, two
digits ranging from 01 to 31 for the day, two digits ranging from
01 to 12 for a 12-hour system, or 00 to 23 for a 24-hour system, two
digits ranging from 00 to 59 for the minute, two digits ranging from
00 to 59 for the second, and two characters, AM or PM, for the meridian
indicator (TT).
- Code page
- Enter the IBM-defined code page ID, as mapped from the operating
system code set. For example, the ID for UTF-8 on any operating system
is 1208. For a complete list of code page IDs, refer to the DB2® documentation.
- Additional file type modifiers
- Specify additional file type modifiers that are not available
in the other fields on this page. These modifiers must adhere to the
DB2 LOAD syntax that is defined in the DB2 documentation. Do not enter
the MODIFIED BY keywords. Type a space between each separate modifier.
For example, two valid file type modifiers are NOROWWARNINGS and ANYORDER.
Note: These
modifiers are not checked for compatibility when the data flow is
validated. You must check that the modifiers you enter are valid before
trying to run the data flow.
- Field list
- This table describes the columns (fields) and data types that
the specified file contains. Click the Add New icon
to populate new rows of the table with default values, then edit them
as required. You can also insert, delete, and move rows up and down
in the table.