Share via

Complex Flat File Conversion Using BizTalk Schema and Map

  • Article

Introduction

Consider the following requirements:

User have Flat File format:

CDR 0A|0|4|0A|Country|India/South|;2;1690;1;5;20070101;5;20070106;5;20130106;|
CDR 1A|0|4|1A|Country|India/West|;2;1683;1;5;20070101;5;20070106;5;20120107;|
CDR 1N|0|4|1N|Country|NY|;2;231;1;5;20120101;5;20120102;5;20120103;5;20210107;|

Now user wants to parse this Flat File and convert into another format which is given below.

Year,EventDate,DayType,CalendarType
2007,20070101,HYD,India/South
2007,20070106,HYD,India/South
2013,20130106,HYD,India/South
2007,20070101,WKD,India/West
2007,20070106,WKD,India/West
2012,20120106,WKD,India/West
2007,20120101,WKD,NY
2007,20120102,WKD,NY
2012,20120103,WKD,NY
2021,20210107,WKD,NY

Building the Sample

Step 1: we need to manually create Flat File Schema, that is able to parse this flat file.

Structure of the Flat File Schema looks like this

Properties for HolidayDetails Node

  • Child Delimiter: ;
  • Child Delimiter Type: Character
  • Child Order: Postfix
  • Max Occur: 1
  • Min Occur: 1

Properties for HolidayRecord Node

  • Child Delimiter: ;
  • Child Delimiter Type: Character
  • Child Order: Infix
  • Max Occur: *
  • Min Occur: 0

This Schema can parse flat file and can convert into XML easily.

<Root xmlns="http://TechBiztalk.FlatFileSchema1">
  <Details xmlns="">
    <CDR_Code>CDR 0A</CDR_Code>
    <CDR_Code1>0</CDR_Code1>
    <CDR_Code2>4</CDR_Code2>
    <CDR_Code3>0A</CDR_Code3>
    <CalanderType>Country</CalanderType>
    <CaanderName>India/South</CaanderName>
    <HolidayDetails>
      <Field></Field>
      <CDR_Code4>2</CDR_Code4>
      <NoOfDaysInCalander>1690</NoOfDaysInCalander>
      <CDR_Code5>1</CDR_Code5>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20070101</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20070106</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20130106</HolidayDate>
      </HolidayRecord>
    </HolidayDetails>
  </Details>
  <Details xmlns="">
    <CDR_Code>CDR 1A</CDR_Code>
    <CDR_Code1>0</CDR_Code1>
    <CDR_Code2>4</CDR_Code2>
    <CDR_Code3>1A</CDR_Code3>
    <CalanderType>Country</CalanderType>
    <CaanderName>India/West</CaanderName>
    <HolidayDetails>
      <Field></Field>
      <CDR_Code4>2</CDR_Code4>
      <NoOfDaysInCalander>1683</NoOfDaysInCalander>
      <CDR_Code5>1</CDR_Code5>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20070101</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20070106</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20120107</HolidayDate>
      </HolidayRecord>
    </HolidayDetails>
  </Details>
  <Details xmlns="">
    <CDR_Code>CDR 1N</CDR_Code>
    <CDR_Code1>0</CDR_Code1>
    <CDR_Code2>4</CDR_Code2>
    <CDR_Code3>1N</CDR_Code3>
    <CalanderType>Country</CalanderType>
    <CaanderName>NY</CaanderName>
    <HolidayDetails>
      <Field></Field>
      <CDR_Code4>2</CDR_Code4>
      <NoOfDaysInCalander>231</NoOfDaysInCalander>
      <CDR_Code5>1</CDR_Code5>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20120101</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20120102</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20120103</HolidayDate>
      </HolidayRecord>
      <HolidayRecord>
        <HolidayIndicator>5</HolidayIndicator>
        <HolidayDate>20210107</HolidayDate>
      </HolidayRecord>
    </HolidayDetails>
  </Details>
</Root>

Now create Schema for target Flat File.

Structure of schema looks like.

Now Simple Map can convert this XML into target XML.

We can test the XML instance and check output XML result.

XML generated from this Map is:

<ns0:Root xmlns:ns0="http://TechBiztalk.FlatFileSchema2">
  <Details>
    <Year>2007</Year>
    <EventDate>20070101</EventDate>
    <DayType>5</DayType>
    <CalType>India/South</CalType>
  </Details>
  <Details>
    <Year>2007</Year>
    <EventDate>20070106</EventDate>
    <DayType>5</DayType>
    <CalType>India/South</CalType>
  </Details>
  <Details>
    <Year>2013</Year>
    <EventDate>20130106</EventDate>
    <DayType>5</DayType>
    <CalType>India/South</CalType>
  </Details>
  <Details>
    <Year>2007</Year>
    <EventDate>20070101</EventDate>
    <DayType>5</DayType>
    <CalType>India/West</CalType>
  </Details>
  <Details>
    <Year>2007</Year>
    <EventDate>20070106</EventDate>
    <DayType>5</DayType>
    <CalType>India/West</CalType>
  </Details>
  <Details>
    <Year>2012</Year>
    <EventDate>20120107</EventDate>
    <DayType>5</DayType>
    <CalType>India/West</CalType>
  </Details>
  <Details>
    <Year>2012</Year>
    <EventDate>20120101</EventDate>
    <DayType>5</DayType>
    <CalType>NY</CalType>
  </Details>
  <Details>
    <Year>2012</Year>
    <EventDate>20120102</EventDate>
    <DayType>5</DayType>
    <CalType>NY</CalType>
  </Details>
  <Details>
    <Year>2012</Year>
    <EventDate>20120103</EventDate>
    <DayType>5</DayType>
    <CalType>NY</CalType>
  </Details>
  <Details>
    <Year>2021</Year>
    <EventDate>20210107</EventDate>
    <DayType>5</DayType>
    <CalType>NY</CalType>
  </Details>
</ns0:Root>

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.