Regarding Excel sheet to xml conversion

Hi,

I have a requirement to convert the Excel sheet to xml.

Note: The data is available in the Sheet one

The A2(Vareeier) and A3(Saksbehandler) column values are field elements formed inside the Record "Header" and the corresponding values at B2(LHUKSU)
and B3(GROBOB) are values of the corresponding A2 and A3.

The 7th row in the excel sheet has the field elements of the record "Details" which should be formed inside the "DetailsGroup" repeating record.
Note: The 7th row may have many columns with respect to which the data elements inside each "Details" record should be reflected.

From the 8th row the values corresponding to the Data elememts created for "Details" record is present. We need to populate it accordingly.There may be values in the columns from 8th row which may be blank and needs to taken care of.

Note:I tried implementing the code provided by:https://mohammedatef.wordpress.com/2009/06/07/biztalk-custom-excel-pipeline-component/

But we are not supposed to use a temporary folder or use a Datareader to convert the excel to xml.



The Excel sheet would be like this:

The expected xml format:


-     - <Root> - <Header>   <Vareeier>LHUKSU</Vareeier>   <Saksbehandler>GROBOB</Saksbehandler>   </Header> - <DetailsGroup> - <Details>   <Type_x0020_Innspill>UT - UTMELDING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>5708869270668</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2911451</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5871538</Coop_x0020_Varenr_x0023__x0020_>   </Details> - <Details>   <Type_x0020_Innspill>UT - UTMELDING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>7058020022451</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2644755</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5543160</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>UT - UTMELDING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>8713626058103</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2463180</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5317474</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>UT - UTMELDING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>5708494003921</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2209039</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>4990586</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>UT - UTMELDING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>7058020019710</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2307700</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5100755</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>SO - SORTIMENTSENDRING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>4011260425225</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>1149020</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5521943</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>SO - SORTIMENTSENDRING</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>5708869130528</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2440360</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>5276134</Coop_x0020_Varenr_x0023__x0020_>   </Details> + <Details>   <Type_x0020_Innspill>NY - NYHET</Type_x0020_Innspill>   <EAN_x0020_KODE_x0020_f-pak>8713626114380</EAN_x0020_KODE_x0020_f-pak>   <EPD_x0020_nummer>2196145</EPD_x0020_nummer>   <Coop_x0020_Varenr_x0023__x0020_>4966966</Coop_x0020_Varenr_x0023__x0020_>   </Details>   </DetailsGroup>   </Root>




September 1st, 2015 12:52am

Hi Ranjana,

Your input Excel sheet and expected xml format are not visible to us. could you please share these information.

In mean while, please look into below useful articles and related thread for your reference,

BizTalk Server: Receive and Send Excel Files from Existing Templates

BizTalk: List of Custom Pipeline Components

Excel Reading Custom Pipeline for BizTalk Server

Reading and processing excel file (xlsx) in BizTalk 2010

Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 1:26am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics