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 4:49am

Hi Ranjana,

You can use OPEN XML SDK to read excel files directly into your pipeline component.

 - You no need to use any temporary path in this scenario, just read / feed excel file to pipeline component by the stream.

The link which you are referring it uses the file folder location to read the files, you cannot avoid folder in that pipeline component.

- It expects / standard is to read from the file folder using a connection string.

Thanks, SMSVikasK
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 5:04pm

Hi Ranjana,

When you are working with the Open XML SDK and If you need any suggestion you can ask in the forum below.

Open XML Format SDK

Hope this helps you.

Thanks, SMSVikasK

September 1st, 2015 6:17pm

Hi Ranjana,

You can use OPEN XML SDK to read excel files directly into your pipeline component.

 - You no need to use any temporary path in this scenario, just read / feed excel file to pipeline component by the stream.

The link which you are referring it uses the file folder location to read the files, you cannot avoid folder in that pipeline component.

- It expects / standard is to read from the file folder using a connection string.

Thanks, SMSVikasK
  • Edited by SMSVikasK Tuesday, September 01, 2015 9:02 PM
  • Marked as answer by Ranjana_Torreti Wednesday, September 02, 2015 4:33 AM
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 8:59pm

Hi Ranjana,

When you are working with the Open XML SDK and If you need any suggestion you can ask in the forum below.

Open XML Format SDK

Hope this helps you.

Thanks, SMSVikasK

  • Marked as answer by Ranjana_Torreti Wednesday, September 02, 2015 4:33 AM
September 1st, 2015 10:12pm

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

Other recent topics Other recent topics