none
Excel to XML with list of drop down values RRS feed

  • Question

  • Hi,

    I am converting Excel to XML using custom pipeline component in BizTalk 2013 R2. If there are multiple values for a column like below it is returning only selected value from drop down how can I get all the values listed as an array in XML.

    

    <product_ID>4</product_ID>

    <Product_Name>PRODUCT1</Product_Name>

    <Product_Name>PRODUCT2</Product_Name>

    <Product_Name>PRODUCT3</Product_Name>

    <Product_Name>PRODUCT4</Product_Name>

    <Product_Price>4000</Product_Price>

    Monday, October 9, 2017 8:56 AM

Answers

  • As noted above, do not do this.  We all love Excel but it's a terrible message format.

    It will take a lot of work.  You will have problems.  Your users will be disappointed.

    So, the first thing you have to do is exhaust all other possibilities.  If the producer is unable or unwilling to change from Excel, the next thing you need to do is inform your management that you will have to spend at least twice the amount it time and money writing a validation component for the received Excel file.  And then, your only option is to reject it for even the slightest issue.

    Now, once you've done all that and budged time for all the errors that will still occur ;)...

    The list is a feature of the Excel UI and is only one value ever stored in the cell.  If you need more values, the user needs to enter all using some separator...which they will frequently get wrong... :(

    I say all this from many experiences.

    Monday, October 9, 2017 1:31 PM
    Moderator

All replies

  • DON'T do this..

    Dont accept excel as a source for ur integration

    Ask for a flatter file with records such as :

    4,PRODUCT1,4000

    4,PRODUCT2,4000

    4,PRODUCT3, 4000

    4,PRODUCT4, 4000


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Monday, October 9, 2017 11:06 AM
    Answerer
  • Hi

    Not sure if your question/requirement in clear. For the single cell, only one value is ever possible. The dropdown lists a set of possible values. But the cell can only have a single value out of those.


    Thanks Arindam

    Monday, October 9, 2017 11:37 AM
    Moderator
  • As noted above, do not do this.  We all love Excel but it's a terrible message format.

    It will take a lot of work.  You will have problems.  Your users will be disappointed.

    So, the first thing you have to do is exhaust all other possibilities.  If the producer is unable or unwilling to change from Excel, the next thing you need to do is inform your management that you will have to spend at least twice the amount it time and money writing a validation component for the received Excel file.  And then, your only option is to reject it for even the slightest issue.

    Now, once you've done all that and budged time for all the errors that will still occur ;)...

    The list is a feature of the Excel UI and is only one value ever stored in the cell.  If you need more values, the user needs to enter all using some separator...which they will frequently get wrong... :(

    I say all this from many experiences.

    Monday, October 9, 2017 1:31 PM
    Moderator