How to use the Stack Files Command in the Tabular Transformation BizApp?
The OneCloud Stack Files provides the ability to take files from sources such as on a file system or SFTP server and stack them together to create a single data payload to load to a target system. The walkthrough example will demonstrate how to stack files on a file system that has a GroundRunner installed. Also, it should be noted that File Stacking only works is there is a location to persist the intermediate file. If using a CloudRunner, then some type of file cloud storage will need to be used, such as Google Drive or Box.
Sample Files to Stack:
OneCloud Chain Overview:
It is highly recommended to create Chain variables to hold the value of the file directory and the target stacked file. Here is an example that also uses OneCloud's Variable Composition feature.
Command 1: List Directory
The first Command in the Chain is List Directory to get a list of files. These files need to all have the same number of columns and column headers. Please note that stacking files with different column values is currently not supported. In the example, all files with the name of "sample" and ".txt" will be included in the list.
Command 2: Extract Value
To allow this Chain to be as dynamic as possible, it is required to extract the header record and use the output as the first file to stack. Use the Tabular Transformation Command Extract Value. Set the Input file parameter to the output, Found files list, from the List Directory command:
By clicking on the Found files list, the Variable Transformation dialogue will pop up and should be set as follows:
Command 3: Create Header File
Use the Command Create File to write out the base stacked file with a header. The Text parameter should be the Value output from the Extract Value Command, and the File Path parameter should be the target stacked file (hint: use the Chain Variable created in the Chain configuration step).
Command 4: Create a Command Group
Create a Command Group with the following Commands:
Enable Iteration on the Command Group and assigned the Found File List output from the List Directory Command.
Command 5: Stack File (in the Command Group)
Use the Command Stack Files to stack each file found in the List Files Command. It is important to note that this Command is a part of Command Group that loops (iterates) over each file found. Use the Add button to stack two files. One of the files will be the current file in the loop, and the other file will be the target file. The order that these files are added does not matter.
Command 5: Move File (in the Command Group)
Once the current file and target file have been stacked, it is time to overwrite the prior stacked file with the new stacked file. This is operation is performed with the Move File Command, where the Source and Destination files are set, and the Overwrite option is selected. It should be noted that the stacked file will continue to build on each subsequent iteration.
Publish and Run
Given the sample source files, the following output will be created:
Please note that the data will be stacked in the reverse order of the list files, as stacking occurs from the first record in the existing file.
As always, we welcome questions on this post or feel free to contact us at firstname.lastname@example.org.