TIBCO Tutorial: How to Read Data From a File and Store to Database

By | September 21, 2014

While working on TIBCO projects, you often come across the situations where you need to read data from some text files, parse that data and then store it to a database table. In this step by step tutorial, I will explain you how you can read text file and then save its data to Oracle database table.

Scenario:

In this step by step TIBCO Tutorial, I will read data from a text file (accountfile.txt). The data in the text file will be pipe (|) separated and It will have account id, account beneficiary name, account balance and account type.

Once data is read from the text file, It will be stored to a database table (tbl_account_test). The table structure is shown below:

account table strucutre

Now once scenario is clear, Let’s proceed step by step with the tutorial.

 

Step 1: Create database connection

As we are going to save the contents read from the text file to Oracle database table, we need to create a Connection shared resource. From the JDBC Palette, add JDBC Connection activity and configure it as shown in below screenshot:

jdbc connection tibco

Using Test Connection button, make sure that connection is configured correctly and you are able to connect to the database.

 

Step 2: Add Data Format

Data Format is a shared resource activity available in the Parse Palette. We need this activity in order to specify details of the data that will be in our text file.
Drag the Data Format resource and specify Pipe (|) as delimiter as shown below:

tibco read text file data format configuration

In the Data Format tab, we specify the elements that will be in the text file that can be parsed using this Data Format. As you can see below, I have added a Complex element with account related sub-elements in the data format:

tibco text file parse data format

 

Step 3: Create Process to Read Text File, Parse Contents and Save to Database

In this step, we will create the process and add the whole logic in it. The structure of the process will be as shown below.

tibco parse data process screenshot

Let’s now configure each activity to achieve our goal. Read File activity is used to read the source text file. Our source file for this example scenario is having content as below:

220|Ajmal|430|Special
221|Umer|300|Regular

In the Input tab of Read File activity, we are specifying the text file to be read as you can see below:

tibco parse read file input

Next we proceed towards configuration of Parse Data activity. In the configuration tab of this activity, we choose the data format that was created in Step 2 of this tutorial.

tibco text file read parse data configuration

In the Input tab, for the Text field, we map the text of Read File activity. For NoOfRecords field, we specify -1 as we want to read all the records from the source text file.

parse data input mapping tibco

As we want to store all the records parsed to a database table, we need to use Iterate group and enclose JDBC Update activity in the group.

For Iterate Group, we specify Group Action as Iterate and Output of Parse Data as Variable List as you can see in below diagram:

tibco parse data iterate group to store records to database

In the Configuration tab of JDBC Update activity, we choose the Connection that was created in Step 1. As you can see below, we are using parameters for the data to be added to database table:

tibco parse data jdbc update configuration

In the Input tab of JDBC Update activity, we map the content parsed to the parameters so that all the records are stored to database table one by one in the group iteration:

parse data tibco store to database input mapping

 

Step 4: Run the Process in Designer Tester and See the Results

In this step, we are going to load the process that we developed in designer tester and we will see how data is read from a text file, parsed using Parse Data activity and then all records are stored to database table.

Process is loaded in designer tester and as you can see below, process has run successfully:

tibco text file to database designer tester

 

Now in order to see the results, Let’s query the database table and see if text data read from the text file has been saved in database or not.

As you can see in below screenshot, all records read from text file have been successfully saved to database table:

tibco save data from text file to database table

 

Ajmal Abbasi

Ajmal Hussain Abbasi is a TIBCO Consultant By Profession with more than 6 years experience in TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Spotfire, TIBCO BE, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in Telecom sector by using his skills in Tibco Designer, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi is also experienced in developing solutions using Oracle PL/Sql, Linux and Java. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn

7 thoughts on “TIBCO Tutorial: How to Read Data From a File and Store to Database

  1. umamahesh

    HI ajmal i have tried this example,
    but it is only updating the first row but not all
    i have followed your steps, nothing else,
    can you tell where i have made mistake.
    thank you

    Reply
  2. umamahesh

    Hi ajmal,
    this is umamahesh, i asked about a problem in previous post
    but now i cleared it,i made mistake in group
    now its fine
    thank you

    Reply
  3. Phan Tuan

    I had a Problem when i’m trying to excercise

    My String: 1|2|3|||6|7
    str1 = 1,
    str2=2
    str3=3
    str4=null
    str5=null
    str6=6
    str7=7
    How to fix it? thanks!

    Reply
  4. sasha

    thanks, is there a way to specify the field separator as blank ?

    best regards

    sasha

    Reply
  5. sandeep yenigandla

    Please also post what are the chances of errors and mistakes we may do

    Reply
  6. Anu

    Hi Ajmal,

    If while inserting rows in DB i want to skip the records that are already present in the table , how to do that ?

    Thanks and Regards

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *