A transformation where a file is a destination

Create a transformation when the destination is a file

Etlworks can extract data from files, API endpoints, social websites, databases, etc., transform it and create files.

Read how to configure a transformation when the destination is a file.

Destination filename

The destination file name is calculated by concatenating the Directory specified when configuring the destination Connection, and the file name which was entered into the TO field for the transformation.

+

= Inprogress/test.csv

Use global variables as a part of the destination filename 

Any part of the destination file name can contain a global variable, enclosed in {}. The system will replace the {token} on the value of the global variable with the same name. Read about parameterization of transformations.

The destination file name is the same as the source file name

When processing files using a wildcard file name, it is possible to set the destination file name similar to the source file name.

Just enter * into the TO field for the transformation.

Calculate the destination filename

In a source-to-destination transformation, if any part of the TO field contains Macros of this type: replace(regex, replacement), the system will perform a regular expression replacement of the enclosed part of the string and concatenate it with the left and right parts of the string.

Example 1

FROM = *_processing.xml

TO = replace(_processing, _done)

If these are the source files in the source folder:

order1_processing.xml, order2_processing.xml, and order3_processing.xml,

then the following destination files will be created:

order1_done.xml, order2_done.xml, and order3_done.xml.

Example 2

FROM = *.xml

TO = replace('', _)

If these are the source files in the source folder: 

order1 processing.xml, order2 processing.xml, and order3 processing.xml,

then the following destination files will be created: 

order1_processing.xml, order2_processing.xml, and order3_processing.xml.

Create files with unique names in transformations

In Etlworks, you can specify what suffix should be used when a new file is created.

Once the suffix is enabled for the Connection, all files created using this Connection will have the suffix. This parameter works only when the file is created using source-to-destination-transformation. Read how to add a suffix to the files created when copying, moving, renaming, and zipping files.

Step 1. When creating a Connection, select one of the available types of suffixes, using the Add Suffix When Creating Files in Transformation field:

• timestamp: timestamp in the Format MMddyyyyhhmmss. For example, when the original filename is patient.csv, it becomes patient_03092017040523.csv.
• uuid: UUID - Globally Unique Identifier. For example, when the original filename is patient.csv, it becomes patient_f2f0fdb6-0132-11e7-93ae-92361f002671.csv.
• timestampdatefirst: timestamp in the Format ddMMyyyyHHmmss. For example, when the original filename is patient.csv, it becomes patient_09032017040523.csv.
• timestampyearfirst: timestamp in the Format yyyyMMddHHmmss. For example, when the original filename is patient.csv, it becomes patient_20170309040523.csv.
• timestampyeardatefirst: timestamp in the Format yyyyddDDHHmmss. For example, when the original filename is patient.csv, it becomes patient_20170903040523.csv.

When you're all done, all files created using this Connection will have the specified suffix.

Destination file name as a variable

In many cases, when you are building a pipeline of source-to-destination transformations, a file created in the previous step will be used as a source in the next step. When you are using wildcard file names or automatically-generated unique file names, it is impossible to hard-code the file's name for the next transformation.

Each time Etlworks creates a destination file, it stores its name as a global variable. The global variable is only available to that particular Flow, and all the Flows nested within it.

Let's assume that the destination name is *.CSV TO PIPE.JSON 1.

Tip: the destination name = the Transformation Name. You can always check the destination name by clicking View Flow XML for that particular Flow:

Or by checking the actual destination name, which is the value of the tag name below the tag destination:

Let's also assume that the actual destination file name (which is automatically generated by appending the UUID to the source file name) is /user/local/temp/pipe_4467ea6995904730a10a6c7ddbe25470.json.

So, for the destination name *.CSV TO PIPE.JSON 1 and for the actual destination filename /user/local/temp/pipe_4467ea6995904730a10a6c7ddbe25470.json, three global variables will be created:

• DESTINATION NAME.FULL.FILE.NAME.TO.WRITE: *.CSV TO PIPE.JSON 1.FULL.FILE.NAME.TO.WRITE = /user/local/temp/pipe_4467ea6995904730a10a6c7ddbe25470.json
• DESTINATION NAME.FILE.NAME.TO.WRITE: *.CSV TO PIPE.JSON 1.FILE.NAME.TO.WRITE = pipe_4467ea6995904730a10a6c7ddbe25470.json
• DESTINATION NAME.BASE.FILE.NAME.TO.WRITE: *.CSV TO PIPE.JSON 1.BASE.FILE.NAME.TO.WRITE = pipe_4467ea6995904730a10a6c7ddbe25470
• You can then reference any of these {variables} in the Connection parameters (such as the URL), or in the FROM / TO fields for the transformation.

You can also access any of the global variables above using the following JavaScript:

var destinationFileName = com.toolsverse.config.SystemConfig.instance().
getProperties().get('variable name above');