Have you ever wanted to name a metadata field using more than one word? Or give it a name with national characters, perhaps? Or any other non-alphanumeric characters? Now you can, with labels for metadata fields introduced in CloverETL 3.2.
As was already mentioned, the names of fields in CloverETL are only allowed to contain basic characters A-Z (both upper and lower case), digits 0-9, and underscores. In addition, the names cannot start with a digit and must be unique. When trying to import metadata with other characters (e.g. from a delimited flat file or a spreadsheet), the names are normalized to satisfy the requirements above.
In CloverETL 3.2, a new attribute was added to metadata fields: the Label. The attribute is used to preserve the original names of fields as they were before the normalization. Some writers can then use the labels when writing out the data (e.g. UniversalDataWriter or XLSWriter).
In the picture below, the names of fields were normalized to the “Clover-friendly” format after metadata extraction, while the labels contain the original names of columns, as they were in the DBF file. The same principle works for any metadata source (database, XSD, XLS…). Some labels were manually deleted – those are now displayed in light gray to indicate that the default values of labels are identical to the values of field names.
Usage of Metadata Field Labels
Having extracted metadata with labels from some source, what can we do with it? Let us illustrate it in a few examples.
To copy data from a database to a delimited flat file, extract metadata from the database, use UniversalDataWriter to write out the data, and enable the “Write field names” property.
To copy data from a flat file to a XLS spreadsheet, extract metadata from the flat file, use a XLSDataWriter, and set the “Metadata row” attribute to “1”. The same applies to the upcoming SpreadsheetDataWriter components (as of CloverETL 3.3).
To load data from a spreadsheet into a database (preserving the names of columns), first extract metadata from the spreadsheet. Then create a new table in the database by right-clicking the metadata and selecting “Create database table”.
Then just use a DBOutputTable to insert the data into the table.
In a Reformat and other components which use CTL, the label of a field can be obtained with one of the CTL functions for dynamic field access:
As you can see, labels can be used to preserve the original names coming from data sources that support arbitrary text for a field name (Excel spreadsheets, databases, etc.). In future versions, the plan is to integrate labels more into components and the Designer for an even better user experience.