Introduction

BitCurb ETL functionality provides enterprise level conversion engine, which can parse and transform your financial file format into easy to read format.
Our ETL solution is integrated with storage providers like SFTP, OneDrive and DropBox. Having that functionality, we don’t access your data until you request us to process it. Conversion happens in real-time and no data is preserved. We only keep KPI statistics, which we provide to you as reference through our reporting API.
With BitCurb ETL Engine, you can load, extract and transform your SWIFT MT 940, SWIFT MT 950 and CSV data into easy to read CSV format or to open-standard JSON file format.
If you want to get an Excel file result that you can download and read, you need to create and execute an ETL Template in our portal.
If you want to programmatically consume a file parsing and transformation, you can use the ETL API to execute a template or to pass your data file as input, without having anything defined in the portal.

ETL operations in the portal

To execute ETL operation you must have assigned licence to your user.
Before starting working on your ETL process, you must define the source of your data in BitCurb. That is the data containers, which you can define in the portal from your account.
At the moment we support:

  • DropBox
  • OneDrive
  • SFTP

The data containers abstract the storage and the access of your files and keep the control over it for you only. You need to provide the settings for your storage (i.e. DropBox, OneDrive or SFTP) and then your data becomes accessible within the ETL module.
BitCurb doesn’t store your data, we only process it!
To define a data container, you must be in Admin role.
Navigate to Admin/Data Containers from the top menu and click the “New” button.

You select which is the container type your data is stored in and provide the details for it.

Once you have your data container created, you can go to ETL module and define your processing rules for your files.
The creation of ETL template is a wizard like process where you provide different information at each step:

Step 1 General Info
You define the name of your template and the Data Container and Data Type you are going to process.
The “Data Container” pull-down will display the names of all defined data containers in your account.
The “Data type” is the types of files we support: MT SWIFT 940, MT SWIFT 950, CSV
If you choose a CSV file from the “Data type” pull-down you have additional options relevant to the selected format:

  • My file has a header
  • If you create ETL template for CSV and define your preview file has header, then your files that you are going to upload to the “source” folder in your data container also must have headers and follow the same structure of the file – i.e. having same number and names for the columns.
    The preview file is like a pattern definition for your ETL operations.

  • Field Delimiter
  • We provide most common delimiters for you to choose from a pull-down, but you can also define custom value in case your file has a field delimiter not listed in the pre-defined values.

Ste 2 Preview file
At this step you must select a fixed file, called “preview” file.
This file must have the same structure (and type) as the files you are going to automatically process with the ETL functionality. It is used to visually display the parsed file and to help you validate the applied functions.
We advise you to use small in size sample file, which represents a real file that is going to be continuously processed in future.
If you need to start over being at this step in the wizard, you can use the “RELOAD” button, which will refresh the content of your container.

Step 3 Source files
At this step you choose your input folder.
This is the folder which will store your data files to process. If there are multiple files at this folder at the time of execution, the last create will be picked up. To avoid confusion, when processing file try to make sure there is only one file in that folder of your data container.
You can’t select a file at this level. Only folder selection is possible.

Step 4 Destination files
At this step you choose your output folder. Every processed file will be moved to that folder regardless if there is an error during execution or not. We don’t delete your files.
You can’t select a file at this level. Only folder selection is possible.

Step 5 Verify Selection
At this step you can preview all the settings you provided during the wizard and to finalize the creation of the template by clicking the “FINISH” button.

Step 6 – work on your data

The ETL window is comprised of three areas:

Definition area
In the definition panels you can change the name of your template and the paths to your files.

Structure area
The “Structure” area is comprised of two panels – Definition List and Result List

  • Definition List – the list on the left
  • The Definition list is a set of fields that are extracted from your “preview” file. It represents the file that is parsed to the granularity of separate fields.
    For MT SWIFT formats, we support a number of pre-defined fields.
    The extensive list could be found in the ETL API documentation.
    If you are processing CSV files then depending on if your “preview” file has headers or not you will find your fields parsed here. If your “preview” doesn’t have time we will give the names of the columns – Column1, Column2, etc.

  • Result List – the list on the right
  • This list contains the fields from your file that you want to see in the result file (result data) when the ETL operation is executed.
    By default the list contains all fields from the “Definition List”, representing the parsing operation. If you only want to parse the file then you don’t have to do anything additional.
    To move a column from the “Definition List” to the “Result List” you shall use the “+” button in the list on the left.
    We provide the following operations for the columns from the “Result List”, that define your ETL result.

      Edit – you can change the display name of the field in the result for the ETL operation, and you can also provide a formula expression

    • Delete – remove the file from the result
    • Move Up – change the column order, move the column up in the ordered list
    • Move Down – change the column order, move the column down in the ordered list
    Edit fields and define formulas
    If you click on the “edit” icon you can change the name of the filed as it is displayed in the result of the ETL operation and optionally you can provide formula expressions to transform the value of the field.
    An example of edited field with different display name and expression to transform value:
    The changes you define in the Result List are automatically applied and visualized in the area below.

    Preview area
    The “Preview” area is comprised of two panels – Definition List and Result List.
    We display only the first 20 rows from your “preview” file, regardless how big your “preview’ file is.

    • Preview source data – the list on the left
    • The list visualizes the content of your “preview” file from the ETL template definition.
      We provide the visualization, so you can see and test real-time the processing of your data.
      You can see the fields as we parse them, so you know what data and transformations you may need to apply in your result definition.

    • Preview transformed data – the list on the right
    • The list visualized the data coming from your “preview’ file as it would look in the end result, after executing the ETL operation.
      It is defined from the list above – the “Result List” in the “Structure area”.
      The preview list takes all the column in the defined order and applies the formulas for the fields.
      Saving the expression from the above example will transform the field value and will take the new field name.

    Execute ETL operation

    From the list of your ETL templates, click the “Run this template” icon.

    An excel file is created as result and returned for saving.
    If you save and open the file you can see the edited field is presented in the file with its new name and applied formula transformation.
    In your data container bound to this ETL template, the file from the “source file path” will be moved to the “destination file path” folder, so you know it has been processed and the same file cannot be processed twice.

    ETL through API

    Please, refer our API documentation for details on how to consume ETL operations.