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

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:

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:

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:

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:

 

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.

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:

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.

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.

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:

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:

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:

 

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:

 

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: