Using CLOB format to transform source messages

Basic ideas

Typically, in Etlworks you are using one of the following techniques when working with data:

• If you want to extract data from any source, transform and load into any destination you are going to be using source-to-destination transformations. 
• If you want to just move or copy files unmodified from the source to the destination you are going to be using file management flows, such as copy, move. delete, etc.
• If you want to transform XML files using XSLT you are most likely going to be using XLST flow.
• If you want to create complex nested XML or JSON documents you are most likely going to be using JavaScript or Python.

Obviously, the most powerful transformation is source-to-destination. In almost all cases it hides the complexity of working with specific data formats and allows ETL developers to use high-level instruments, such as mapping editor. 

There are cases, however, when you just want to make a few changes in the source text document and save it to the same or different location. For example, you might want to rename all nodes in the JSON document which start with <name to <the_name (I'm making this up).

It can be easily accomplished using the technique explained below. 

Step by step instruction

Step 1. Create source and destination connections. It can be any file storage, cloud storage or HTTP listener.

Step 2. Create a new CLOB format.

Step 3. When creating a format enter the transformation code in the Preprocessor field. Example:

if (message != null) {
    message = message.replace('<name', '<the_name');
}

value = message;

Step 4. Create a source-to-destination transformation where the connections are the same connections that you created in step 1 and format is a format created in step 2.

Step 5. Set the source and destination name. You can use the wildcard file name as a source and enable wildcard file processing.