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

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:

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:

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:

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:

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:

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.

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:

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:

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

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.

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.