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