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:

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 Integration Consultant By Profession with 13+ years experience in Integration domain mainly with TIBCO products. He has extensive practical knowledge of TIBCO Business Works, TIBCO Cloud, TIBCO Flogo, TIBCO Mashery, TIBCO Spotfire, EMS and TIBCO ActiveSpaces. He has worked on a number of highly critical integration projects in various sectors by using his skills in TIBCO Flogo, TIBCO API Management (Mashery), TCI, Tibco Designer, TIBCO Business Studio, Adapters, TIBCO EMS, RV, Administrator, TIBCO BE, TIBCO ActiveSpaces etc. Ajmal Abbasi has experience with MuleSoft ESB as well. Ajmal Abbasi is also experienced in the area of API Management particularly with WSO2 API management platforms. Ajmal Abbasi is also experienced in developing solutions using Core Java and J2EE Technologies. You can contact Ajmal Abbasi for Consultancy, Technical Assistance and Technical Discussions.

More Posts - Website - Facebook - LinkedIn - YouTube

7 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
    1. satya

      you need to use the batch update in the JDBC update acitivty and change the condition in the group conidition with subsetsize =true() condition

      Reply
  3. Joel

    Hello.i followed this tutorial and it was really great. I just want to ask, if i want to pick any .csv file in a specified path, instead of a specifically named CSV file, how can i do this?

    Reply
  4. Travis

    Hi, thanks for the tutorial, however it didn’t answer the question that I was hoping to be answered. I’m parsing a csv using a very similar strategy to yours, however, some of the lines in the csv contain empty data. For instance, if I were using a data format exactly the same as your example, the empty data would look like this:
    900,Jane Doe,Doctor,35
    800,John Doe,,45

    Whenever I try to parse this data using the same settings as you use in the tutorial, my code gets stuck on the following error:

    There is a binding error associated with activity. Parse Data
    BWENGINE-100031 Job-25000 Error in [Processes/ACH_Loader/BusinessLogic/TestLoader.process/Parse Data]
    Output data invalid
    caused by: org.xml.sax.SAXException: validation error: unexpected content “emp_age”; expected “emp_designation” ({com.tibco.xml.validation}COMPLEX_E_UNEXPECTED_CONTENT) at /Output[1]/Rows[1]/root[2]/emp_age[1]
    com.tibco.xml.validation.exception.UnexpectedElementException: unexpected content “emp_age”; expected “emp_designation”

    It’s okay for the empty data to be marked as null, since that column can be null in my table. But for some reason, it looks like the empty data field is being completely skipped during parsing, which is causing a validation error. How do I keep the empty data from being skipped?

    Reply
  5. Tibco User

    Hi,
    First of all I would like to thank you for creating such an awesome website. It really is helping me a lot in grooming as a Tibco developer.
    I have an small query, If I don’t know number of records in CSV file (dynamic records meaning 1Day=10000 other day=500), How do we candle it in Parse Data?

    Any help will be appreciated.

    Thanks,
    Tibco User

    Reply

Leave a Reply

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