Return to Newsletter Contents...

Introduction to Data Integrator File Format Editor

by: Thomas Hinkle, Consultant, CRCP

Data Integrator’s file format editor is a user friendly way to establish format templates to use as source or target data.

A file format is a set of properties describing the structure of a flat file creating the metadata structure for a source or target data source.  The file format can be the definition for a single file or a template for many files of the same structure.

This is an introduction to the file format process.  Detailed information for using the editor can be found in the technical manuals - Data Integrator Designer Guide.

When working with file formats you will need to:

• Create a file format template that defines the file structure.
• Create a specific source or target file format in a data flow. The source or target file format is based on a template and specifies connection information such as the file name.
• Determine the file type; Delimited or Fixed width format.

Create a new file format

• To begin the process, click the Formats tab in the Local Object Area and right click Flat Files and select New.
• The file Format Editor will open.
• Begin working in the left pane selecting the file format, fixed length or delimited.
• The File Format Editor has three working panes, shown below:

The Properties-Values area is the left pane and contains details of the file, connection information, file specific details and error handling.

The right pane is divided into an upper and lower pane.  The upper pane contains the Column Attributes, the lower pane will display a Data Preview once a file has been specified.

All three areas can be resized using the “splitter” bares and the window can be maximized.

I find it convenient to work from top to bottom in the left pane when first starting the file format.

Some details to remember when setting the Properties-Values:

• To view options available, click the area in the value that you want to change (the blue lettering) and select the option you desire.  Some of the options will display several options but you are able to type other entries in the space; for example the Delimiters and Default format sections.
• Determine the file type first – Delimited is the default.
• Name the file format with a descriptive name that will make sense later.  Once you have named a file format and saved it you will not be able to change the name.  You will be able to replicate it and give the copy a new name.
• Next select a file to use as a sample.  Click the file folder on the file name line.  Point to a single file using the format you want to define.  You can create a format form a spec sheet without a sample file, but pointing to a file will allow the first twenty rows to display in the Data Preview area.  Once the format has been created you may use wildcards to select multiple files.
• If you are viewing the data from a delimited file and the first line has the column headings then you want to set the Skip row headers under Input/Output to “yes”.  This will allow the column headings to display under the Column Attributes pane in the upper right window.
• Refer to the Data Integrator Designer guide for details on the remaining entries under the Properties-Values area.

Working in the Column Attributes pane:

• If you are creating a delimited format and you are displaying the row headers each column will be named and a file type will be chosen based on the data from the sample file.
• If you are creating a fixed file format you will need to name each column specify the data type, field size and format.  You may also edit this information for either file type by clicking in the box and selecting one of the options for file type or entering the value for name, size or format.
• Changes made to the column attributes will be reflect in the Data Preview area below
• The Data Integrator Designer Guide and the Reference Manual provide detailed information on all options

Additional hints for using the File Format Editor:

• Click the Save and Close button to write the format to the repository.  Do not click the cancel button or none of your changes will be saved.  I click save and close often then reopen the editor to make more changes.  This makes sure my changes have been saved to the repositiory.
• Check the spelling of your column names several times.  If you use spaces in the column names make sure your database will accept them.
• Once a file format has been used in a dataflow you will want to make design changes from the Format tab in the local object library.
• To select a different file location for a source you will want to double click the object in the dataflow to open it and you can then make changes to that instance of the object.
• If you have several formats that will be similar then start with one and replicate it to then change the details in the copy.

Remember that Data Integrator can also create formats for XML files and Excel files.  Those are topics for another time.

Go to Top  |  Return to Newsletter Contents