Using BizTalk to Parse a FlatFile with Multi line header and trailers

Today I was asked to parse a Bloomberg file with had multi line header and trailer with pipe separated body.  Here is a file that was given to me

START-OF-FILE
RUNDATE=20120604
PROGRAMFLAG=oneshot
FIRMNAME=dl669
COMPRESS=yes
REPLYFILENAME=spg_historical
TIME=1233
DATERANGE=20090101|20120604
HIST_FORMAT=horizontal
PROGRAMNAME=gethistory

# Automatically generated by Bloomberg Data License Request Builder version 5.0.24
# Build Code: 010001101000000015

START-OF-FIELDS
PX_CLOSE_1D
END-OF-FIELDS

TIMESTARTED=Mon Jun  4 13:39:31 EDT 2012
START-OF-DATA
SPX Index|0|1|01/02/2009|903.25|
SPX Index|0|1|01/05/2009|931.8|
USISDA30 Index|0|1|06/04/2012|2.301|
END-OF-DATA
TIMEFINISHED=Mon Jun  4 13:39:33 EDT 2012
END-OF-FILE

Instead of going the standard route of splitting the header, body and trailer – what I chose to do was to split on Words instead of CR/LF.

Step1) Open FlatFileSchema Wiz and define your name space and root. Choose the file that needs to be parsed.

Step 2) Choose “By Delimiter Symbol” and click Next.

Step 3) Instead of choosing a simple {CR}{LF} use the word that splits the Header and Body – In this case I used “START-OF-DAY{CR}{LF}”

Step 4) On the next screen set the first element as “Header” and Element Type as FieldElement (Since we dont want to split the header).  On the second element though, choose the element type to be “Record” so we can split the data

Step 5) Now the Body and the trailer is automatically selected

Step 6) Do steps 2- 5 now choosing “END-OF-DATA{CR}{LF}” instead of Start of data

Step 7) Now you would be at a familiar point to split the records.

Hopefully this helps to split MultiLine headers and footers.

Advertisements
    • vaibu
    • October 25th, 2012

    Thank you- awesome post

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: