Saturday, November 12, 2011

How to Read a CSV file in Tibco BW having a Header, Body and trailer

Tibco BW provides a ‘Data format’ in order to parse/render the text data. However the palette has a limitation that it can read the file only if file has a fixed sequence.
However in general the CSV file contains a Header section which has the basic information like file creation date/time, number of records etc. The actual data will be present in the section called “Body” and then the last section known as “trailer”.
In order to read such a file in Tibco a small trick is needed.
Please note that you need to create three different data formats one for each header, body and trailer.
I have included the sample file and the BW project in this post. Download the zip file and import the same into BW. Let me know if you have any doubts in the sample included.
Comments/Questions are welcome.

Click here to download sample

3 comments:

Anonymous said...

Hi, how do you handle the csv if you data elements itself contains a comma?
example
"ALHAMBRA CONVALESCENT HOME, INC","","","","","","","","","","","","","","","","415 S GARFIELD AVE","","ALHAMBRA","CA"
Thanks for you help in advance

Anonymous said...

May be we have to change the delimeter..

Anonymous said...

All these is very easy. I have actually managed to do this but my problem now is that if my header and all its respective data is not on the first column. Header is on row 5 column 2 and data aligning data starts from row 9 column 2. How do I tell the parser to start reading from column 2 row 9. I know I can tell it which row to start reading from but not which column. And what happens if my data flow gets broken. How do I skip rows where my mandatory fields are NULL or have a certain character(i.e ",F:")