February 16, 2018 by Don Pulsipher

Transform Data in Talend Open Studio Without Writing ANY Code

With only a few steps and without writing any code, flat file data can be easily transformed and uploaded into BI reporting systems like Roambi using Talend Open Studio (Free).

At Calance, we utilize Talend Open Studio to the fullest for our clients. For this case, our automotive client was receiving data from a vendor called Polk. Polk provides sales data in flat file format that our automotive client wanted to use to generate interactive mobile reports using Roambi.

 

Below outlines the step-by-step process we took to migrate this data from flat file sources into Roambi using an extract transform and Talend Open Studio without writing ANY code.

 

ETL of Segment and Lease information

 

Talend Open Studio converts flat file data from Polk into a format that Roambi Analytics is expecting for a demo report that has already created for our automotive client as a proof of concept.

 

The report being generated in Roambi is for Segment and Lease information by month.

 

The workflow for this case use was as follows using Talend:

 

  1. Flat file data from Polk will be selected as data input in Talend job
  2. Talend job runs and filters or transforms data real time
  3. Talend writes a flat file in the format that Roambi expects
  4. Upload new file to Roambi

 

Flat file data from Polk:

 

    March Polk Segment and Lease by District and Dealer.csv

 

Sample Polk file content:

talend open studio 1.png   

 

Roambi file data to be transmitted to Roambi after Talend ETL:

 

    LV - SLP_SAN_FRANCO_TOP_5_DATA_DUMP.XLS

 

Sample Roambi file content:

talend open studio 2.png

 

Talend Open Studio

Talend Open Studio can create “Jobs” using a designer and the Java programming language. In most cases, the end user will rarely need to write any Java code and can rely almost completely on the designer tools and component palette to do the required work.

 

Here is what the finished job will look like in Talend Open Studio:

 talend open studio 3.png

 

Step 1 - Adding input files

On the Talend component palette tFileInputDelimited will be chosen for adding the Polk data to the job:

 talend open studio 4.png

 

Drag and drop this onto the job designer and it looks like this:

 talend open studio 5.png

 

The component view in the IDE will have all the settings you can change for this component:

 

Step 2 - Filtering and processing input

On the Talend component palette tFilterRow will be chosen for filtering data from our input file:

 talend open studio 6.png

 

You drag and drop this onto the job designer then right click and drag from tFileInputDelimited to connect the input file to this new component:

 talend open studio 7.png

 

The component view in the IDE will have all the settings you can change for this component, for this example we will filter by the month of April on the input file:

 talend open studio 8.png

 

Step 4 - Adding output files

On the Talend component palette tFileOutputExcel will be chosen the output file format:

 talend open studio 9.png

 

After connecting our Filtering component (right click/drag, select row and target) our job now looks like this:

 talend open studio 10.png

 

Here are the settings for the Excel output file component:

 talend open studio 11.png

 

Step 5 - Uploading new file to Roambi

 

Add the Roambi update component to the designer:

 talend open studio 12.png

 

After connecting the Excel file output to the Roambi update component, our job design is complete:

 talend open studio 13.png

 

Here are the Roambi update component settings (note: contents of Roambi account properties file not shown):

 talend open studio 14.png

 

Schema

 

When adding a flat file to the flat file input component, some additional work will be necessary in order to map column names and types to the destination Excel file. On the Input file component properties, there is a “schema” button which will allow you the ability to add this information (if it is not already in the first line of the CSV file).

 talend open studio 15.png

 

Clicking this button will display a popup window where you can easily edit this mapping information:

 talend open studio 16.png

 

Later when you add the Excel output file component you can map this schema any way you wish to the destination Excel file sheet columns.

  

With only a few steps and without writing any code, flat file data can be easily transformed and uploaded into BI reporting systems like Roambi using Talend Open Studio (Free).

  

Need Help with Talend Open Studio?

Contact Us

ETLETL ToolsBI & Big DataOpen Sourcetalendtalend open studioRoambipolk

About the author

Don Pulsipher

Don has over 20 years of experience in the IT industry with a strong background in leveraging OpenSource technology and tools to solve business problems.

Check Profile:

Shareables

Knowledge only grows when shared. Sharing our ideas and thoughts with you.

Recent Posts