Regex file schemas are used for files made of regular expressions, such as log files. If you often need to connect to a regex file, you may want to centralize its connection and schema information in the Repository for easy reuse.
Like the [New Delimited File] wizard, the [New RegEx File] wizard gathers both file connection and schema definitions in a four-step procedure.
This procedure requires some advanced knowledge on regular expression syntax.
To create a File Regex connection from scratch, expand the Metadata node in the Repository tree view, right-click File Regex and select Create file regex 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 regex 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 Encoding type and 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 the OS format.
The file viewer gives an instant picture of the loaded file.
Click Next to define the schema structure.
Defining the file parsing parameters
On this view, you define the file parsing parameters so that the file schema can be properly retrieved.
Set the Field and Row separators in the File Settings area.
If needed, change the figures in the Field Separator field to specify the column lengths precisely.
If the row separator of your file is not the standard EOL, select Custom String from the Row Separator list and specify the character string in the Corresponding Character field.
In the Regular Expression settings panel, enter the regular expression to be used to delimit the file.
Make sure to include the Regex code in single or double quotes accordingly.
If your file has any header rows to be excluded from the data content, select the Header check box in the Rows To Skip area and define the number of rows to be ignored in the corresponding field. Also, if you know that the file contains footer information, select the Footer check box and set the number of rows 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.
If the file contains column labels, select the Set heading row as column names check box to transform the first parsed row to labels for schema columns. Note that the number of header rows to be skipped is then incremented by 1.
Then click Refresh preview to take the changes into account. The button changes to Stop until the preview is refreshed.
Click Next to proceed to the next view where you can check and customize the generated Regex File schema.
Checking and customizing the file schema
Rename the schema (by default, metadata) and edit the schema columns as needed.
To retrieve or update the Regex File schema, click Guess. Note however that any edits to the schema might be lost after guessing the file based schema.
When done, click Finish to close the wizard.
The new schema is displayed under the relevant File regex node in the Repository tree view. You can drop the defined metadata from the Repository 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 Job and How to set a repository schema.
To modify an existing file connection, right-click it from the Repository tree view, and select Edit file regex 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.