TIBCO Tutorial: How to Read CSV File In TIBCO BW Process

By | March 5, 2014

In my previous tutorial, I taught you step by step how to create csv file in TIBCO designer and now in this tutorial, you will learn how to read a CSV file using TIBCO Designer.

After completing this tutorial you will be able to:

  • Create a new data format.
  • Parse CSV file using Parse Data activity.
  • Store contents of a CSV file in a database table using JDBC UPDATE activity.

Step 1: Create a new Data Format:

Our source file that we will be reading is Employee.csv with comma separated records as shown below:

csv file content

For reading this CSV file in our TIBCO BW process, we first create a Data Format by going through Add ResourceàParseàData Format

In configuration tab of Data Format, choose Delimiter Separated as format type, comma in col separator and carriage return/line feed in line separator. Complete configuration will look like below:

data format configuration

In the Data Format tab, we specify what will be the data format that will be parsed using this activity. As you can see in the snapshot of our Employee.csv file, we have Employee Code, Employee Name, Employee Designation and Employee age in comma separated form in our input file. So we create elements for all of them in our Data Format tab as shown below:

data format tab

We don’t need to make any changes to Field Offsets tab.

Step 2: Create BW Process to read CSV File:

To read the CSV file, I have created a BW process in TIBCO with following activities:

  • Parse Data activity to parse the CSV file contents.
  • JDBC Update activity to insert the CSV file data into a database table.

I have kept the process very simple and haven’t even added Exception handling. You can read in my other tutorialhow to handle exceptions in TIBCO BW processes.

In configuration tab of Parse Data activity, browse for the Data Format that we created in Step 1 and use that. As we are going to read from a CSV file, we specify File as input type. Configuration tab of Parse Data activity will look like below screenshot:

parse data configuration

In the Input tab of Parse Data activity, give the path of CSV file which should be read. I am reading 4 records from the csv file by giving the value 4 in noOfRecords. If your first row in CSV file is header information, you can skip it by using SkipHeaderCharacters but in my case, file is not having any header data so I have kept this as blank.

Input tab of Parse Data activity will be like below:

parse data input

In the below screenshot, you can see the output tab of Parse Data activity which shows the output that this activity will generate once tested.

parse data output

Now we need to configure our JDBC Update activity. As we are going to iterate through multiple records read from the CSV file, we will put JDBC UPDATE activity in a group.

In the configuration tab of JDBC UPDATE activity, we specify the database connection and also the SQL statement to be executed. In this case, we are inserting the data that we read from CSV file into a database table TBL_EMPLOYEE_TEST.

Query for creating this table is given below:

create table TBL_EMPLOYEE_TEST

(

EMP_CODE        VARCHAR2(20),

EMP_NAME        VARCHAR2(30) not null,

EMP_DESIGNATION VARCHAR2(30) not null,

EMP_AGE         VARCHAR2(10)

)

As you can see, we have created four parameters in the configuration tab of our JDBC activity which means that we will be mapping input values for all these parameters in the input tab of this activity.

Configuration tab of JDBC UPDATE activity will look like below:

jdbc update configuration

When adding this JDBC activity in a group, we need to give following configurations to our group.

Group Action: Iterate

Index Name: i

Variable List: $Parse-Data/Output/Rows/root

Iteration Element: Data

Configuration of group will be as shown in below screenshot:

iterate group configuration read csv

Now we move towards Input mappings of our JDBC Update activity. Input mappings will be as shown below:

jdbc update input mapping

Now we are done with all the activities configurations for our CSV Reader Process. Validate the project to make sure that there are no errors there.

Step 3: Test TIBCO Read CSV Process:

In order to test the process and verify that it works fine, load the process in designer tester. As you can see in below screenshot, process has run successfully.

read csv file test tibco

Now let us go to our database table TBL_EMPLOYEE_TEST and see if CSV data has been inserted successfully in the table or not.

Run the following query:

Select * from tbl_employee_test

As you can see below, four records read from CSV file by our TIBCO process have been successfully inserted in the database table.

csv data stored in db table using tibco

 

 

 

 

 

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

2 thoughts on “TIBCO Tutorial: How to Read CSV File In TIBCO BW Process

  1. vasu

    Hi Ajmal,
    I am requesting to you for how to create Header fields text file using withdelimeter file….

    Reply
  2. kiran

    Ajmal,

    your tutorial is good.Do we have any tutorial for processing one lakh of records.

    Thanks
    Kiran

    Reply

Leave a Reply

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