If you often need to read data from and/or write data to delimited files, you may want to centralize their metadata in the Repository for easy reuse. File Delimited metadata can be used to define the properties of tFileInputDelimited, tFileOutputDelimited, and t*OutputBulk components.
The file schema creation is very similar for all types of file connections: Delimited, Positional, Regex, XML, or Ldif.
Unlike the database connection wizard, the [New Delimited File] wizard gathers both file connection and schema definitions in a four-step procedure.
To create a File Delimited connection from scratch, expand Metadata in the Repository tree view, right-click File Delimited and select Create file delimited from the contextual menu to open the file metadata setup wizard.
To centralize a file connection and its schema you have defined in a Job, click the icon in the Basic settings view of the relevant component with its Property Type set to Built-in to open the file metadata setup wizard.
Then define the general properties and file schema in the wizard.
Defining the general properties
In the file metadata setup wizard, fill in the Name field, which is mandatory, and the Purpose and Description fields if you choose to do so. The information you provide in the Description field will appear as a tooltip when you move your mouse pointer over the file connection.
If needed, set the version and status in the Version and Status fields respectively. You can also manage the version and status of a repository item in the [Project Settings] dialog box. For more information, see Version management and Status management respectively.
If needed, click the Select button next to the Path field to select a folder under the File delimited node to hold your newly created file connection. Note that you cannot select a folder if you are editing an existing connection, but you can drag and drop it to a new folder whenever you want.
Click Next when completed with the general properties.
Defining the file path and format
Click the Browse... button to search for the file on the local host or a LAN host.
Select the OS Format the file was created in. This information is used to prefill subsequent step fields. If the list doesn't include the appropriate format, ignore it.
The File viewer gives an instant picture of the file loaded. Check the file consistency, the presence of header and more generally the file structure.
Click Next to proceed to the next step.
Defining the file parsing parameters
On this view, you can refine the various settings of your file so that the file schema can be properly retrieved.
Set the Encoding type,and the Field and Row separators in the File Settings area.
If the file preview shows a header message, exclude the header from the parsing. Set the number of header rows to be skipped. Also, if you know that the file contains footer information, set the number of footer lines to be ignored.
The Limit of Rows allows you to restrict the extend of the file being parsed. If needed, select the Limit check box and set or select the desired number of rows.
In the File Preview panel, view the new settings impact.
Click Next to proceed to the final step to check and customize the generated file schema.
Checking and customizing the file schema
The last step shows the Delimited File schema generated. You can customize the schema using the toolbar underneath the table.
If the Delimited file which the schema is based on has been changed, use the Guess button to generate again the schema. Note that if you customized the schema, the Guess feature does not retain these changes.
Click Finish. The new schema is displayed under the relevant File Delimited connection node in the Repository tree view.
Now you can drag and drop the file connection or any schema of it from the Repository tree view onto the design workspace as a new component or onto an existing component to reuse the metadata. For further information about how to use the centralized metadata in a Job, see How to use centralized metadata in a Joband How to set a repository schema.
To modify an existing file connection, right-click it from the Repository tree view, and select Edit file delimited to open the file metadata setup wizard.
To add a new schema to an existing file connection, right-click the connection from the Repository tree view and select Retrieve Schema from the contextual menu.
To edit an existing file schema, right-click the schema from the Repository tree view and select Edit Schema from the contextual menu.