none
Convert Data from Excel to XML when schema includes 'list of lists' RRS feed

  • Question

  • Hi,

     

    I am trying to convert a complext spreadsheet from excel to a XML. Also beacuse of the requirements of the destination application requirement i need to follow the specific schema.

     

    I was able to map all the elements in excel spreadsheet and export the spreadsheet in the required xml format. But the problem starts when there is a 'List of List' in the XML data. and the data is not normalized. when i am truing to map the child of child , i am not able to retain the relationships.

     

    I know with .net  it is possible to build a application from scracch to do it. but with the time and application contraint, is there a way to handle this mapping of excel data?

     

    Thanks

    Kedar Adavadkar

    Friday, March 21, 2008 8:37 PM

All replies

  • Hi

     

    Have you been able to solve your problem? If not can you provide us with more details? I am not sure if I understand the scenario and the problem from your description.

     

    Thanks

    Pawel

     

    Monday, March 24, 2008 6:22 PM
  • Hi Pawel,

     

    I haven't been able to solve it yet.

     

    Please find the example scnario as below:

     

    I have a schema which contains customers and items he is buying.

    In the xml schema, items have a list which has been mapped to a specific cells in Excel spreadsheet.

    Also the cutomer part of the schema have been mapped to specific cells.

    For customers,  information in the xml looks like this

     

    <customer>

    <customerlist>

    <name>....</name>

    <city>....</city>

    <itemlist>

    <item>item1<item>

              <item>item2<item>

                        <item>item3<item>

    </itemlist>

    </customerlist>

    </customer>

     

     

    These items in the customer list comes from the items' table. and excel can't map one cell to more than one element in one xml schema.

     

    if i map items table to the schema it works.

    but when i try to map the customer table, excel can't export the data and gives error 'can't export, data contains list of lists'.

     

    Also when the data to customer table is mapped to exl schema, excel fails to retain the relationship of items from cutomer table and item table.

     

    As a result the data can't be exported from excel not being normalized.

     

    long story short, Excel can't handle 'lists of lists' and how to export data in that way in XML format

     

    Thanks in advance for your help

    Kedar

     

    Monday, March 24, 2008 7:47 PM
  • I have got a few additional questions:

     

    How do you export the data from Excel? Do you do that manually or programmatically? if manually - can you post steps? if programmatically - can you post a code snippet? I would like to make sure that we are on the same page.

     

    In Excel you have got two separate lists - one contains customers while the other contains list of items for each customer. Now your schema requires the lists to be appropariately nested rather than being separate. Is this what causing the problem? Also if this is the case how do you know which customer is buying what items (in other words how the relationship is maintained in excel)?

     

    Thanks

    Pawel

     

     

     

    Monday, March 24, 2008 11:24 PM
  • I have mapped the my scehma (.XSD) file with specific cells in excel and these cell frms the xml lists.

    To export i use excel's built-in export feature which eports the data the data according to the schema and saves in XML format. Steps to do that:

     

    In excel right click on the cells mapped to the XML elements and you should have an 'export' option avalable.

     

    You are correct , the multiple level of nesting is the issue. Excel tables are linked internally and the items which customers are buying are populated based on the same.

     

    just to elaborate further the desired xml looks like this:

     

    <saledata>

    <itemdetail>

    <item>

    <sku>item1</sku>

    <price>15</price>

    </item>

    <item>

    <sku>item2</sku>

    <price>25</price>

    </item>

    </itemdetail>

     

    <customer>

    <customerlist>

    <name>....</name>

    <city>....</city>

    <itemlist>

    <item>item1<item>

              <item>item2<item>

                        <item>item3<item>

    </itemlist>

    </customerlist>

    </customer>

    </saledata>

     

     

     

    Thanks

    kedar

    Tuesday, March 25, 2008 5:10 AM
  • Take a look at this article:

    http://msdn2.microsoft.com/en-us/library/aa203737(office.11).aspx

     

    There is a section titled: 'Creating Relational Tables'. It seems to be similar to what you try to accomplish (nested sequences, relations).

     

    Hopefully it will help you to solve your problem.

     

    Pawel

    Tuesday, March 25, 2008 6:33 PM
  • Thanks Powel

     

    In fact this is very similar to what i am tring to accomplish.

     

    If you copy the code for the xml for the relational table and import it into excel, you can see what i am strugling to do.

    Once you import the xml, using the Source pane you map the data to various cells. now my requirement is to export the data from these cells. If you click on veryfy map for export or just try to export it , you will see the error which i am getting. i.e. Can't export map as it contains lists of lists and relationship in the data could not be preserved.

     

    you are exactly at apoint what i am trying to achive. Can you please try using the relational data table XML if its ok with you.

     

    Thanks again

    Kedar Adavadkar

     

     

     

    Tuesday, March 25, 2008 7:02 PM
  •  

    Hello Kedar,

     

    I followed the steps from the article and indeed I could not export the Xml data due to 'List of Lists' problem. Since the question seems to be related more to MS Excel rather than to Xml itself I would advise to ask it on Visual Studio Tools for Office forum (http://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=16&SiteID=1). Sorry I could not help.

     

    Thanks

    Pawel

    Wednesday, March 26, 2008 10:01 PM
  • Indeed, this has to do with the way the Excell file was created... Kedar, a good thing to do at this point would be to take this to the forum Pawel recommended.

     

    Hope this helps

    Irinel

    Thursday, March 27, 2008 6:00 PM
  • thanks all,

     

    appreciate your help Powel and Irinel

     

    Saturday, April 12, 2008 10:48 PM
  •  Pawel Kadluczka - MSFT wrote:

     Since the question seems to be related more to MS Excel rather than to Xml itself I would advise to ask it on Visual Studio Tools for Office forum (http://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=16&SiteID=1).

     

    Actually, the VSTO forum targets the VSTO technology, with a little bit about Word and Excel where the discussion intersects with document-level customizations. But general application / object model / interop is not supported. If the application version is 2007, the "Innovate on Office" forum is a good place to ask. Note that the moderators there are a bit picky, and will only look at a question if it's posed in the "How do I..." form.

     

    For other Office versions, please consult the list of links in the "Please Read First" message at the top of the VSTO forum.

     

    FWIW, Excel's XML capabilities do not support parent-child relationships. Excel itself is a "flat table" type of database, so that's logical :-) Therefore, if you need your data in a more complex form you will need to do additional, "manual" processing using the object model or ADO.NET (through an OLE DB connection to the closed file). Or, if this is Office 2007, you might take a close look at the OpenXML file format. You should be able to access the closed file through that and use standard XML tools to extract and recompose the data. You can get more information on OpenXML at OpenXMLDeveloper.org and the OpenXML SDK forum.

     

    Sunday, April 13, 2008 8:52 AM
    Moderator
  • Thanks Cindy

     

    I had already posted this question in the OenXML SDK forum, but nobody repllyed to it.

    I will try with OpenXMLDeveloper.org.

     

    Regards

    Kedar

     

    Friday, April 18, 2008 5:38 PM
  • Did you ever find a solution to this problem?  I am currently tackling this same task...
    Wednesday, April 29, 2009 6:30 PM
  • I'm having the same problem too and I've been beating my head against the wall to fix it, but nothing is working for me.
    Friday, June 12, 2009 4:13 PM
  • Same here :( ive been on it for 2 days to no avail! any updates on a solution?
    Thursday, June 10, 2010 8:47 PM
  • It seems Problem is not solved by anybody. LIST OF LIST.... UPDATES anybody .
    Friday, July 30, 2010 7:24 PM
  • have exactly the same problem and cant find a solution anywhere... anyone solved it?
    Tuesday, March 29, 2011 11:05 PM
  • Has anyone solved this. I wanted to use Excel to prepare an index of galleries for a slideshow project I'm working on but alas it seems I will have to find another tool to prepare the XML data.
    Saturday, January 21, 2012 7:41 AM
  • Hi,

    Unfortunately i have encountered the same message when i am trying to export from excel to xml.

    Does anybody find a solution?Or do i need to use other tool?

    Thanks

    Monday, February 27, 2012 11:44 AM
  • I am giving substantial weight to one of the earlier responses where the respondent said "Excel's XML capabilities do not support parent-child relationships. Excel itself is a "flat table" type of database".

    I wanted to take parent/child XML to Excel then back from Excel to parent/child XML.  We seem to all be facing the list-of-lists conundrum; and Excel's seeming lack of support for parent/child XML.

    I'm not happy with this.  I wanted to generate XML that depicts hierarchical parent/child relationships among the data, import the data into Excel, display/print/manipulate the data, and then export the data in similarly good form; all under direction of the XML map.

    If it's correct that parent/child (list-of-lists) is not inherently supported by Excel, I think a way around this is...

    1. Fully repeat excel rows for each repeating item (whereby most cell contents will not change except for the column(s) containing the repeating/child item(s)).

    2. Provide a map that regards each row as a simple row of non-repeating data.

    Then we can later export the data out of the worksheet.  Depending on your target app for the exported data, you might have issues with the flatter data.  I can adapt my source/target apps to observe this convention but it's not pretty.

    I think this approach is not ideal because I have to make my map more simplistic and make source data replicate whole rows for each repeating item.  Once done, however, I can import into Excel, manipulate to suit then get the data back out in similar; albeit less-desirable format.

    I really thought an XML Map, that describes a parent-child relationship, would be able to inspire Excel to import AND export appropriately (doesn't seem that difficult IMHO).

    I realize this is not a 'solution'; just a proposal for a convention that can work around the situation.

    I'll continue to monitor this thread for other ideas and opinions.

    Monday, February 27, 2012 4:51 PM
  • +1 for a solution - Excel needs to be able to infer a hierarchy somehow.
    Thursday, April 11, 2013 4:18 AM
  • Hi Kedar,

    Were you able to solve your problem?

    I am trying to do the exact same thing as you right now. Unfortunately, I was not able to find a solution yet. Please get back to me if you don' mind.

    Thanks,

    Andrew

    Wednesday, July 10, 2013 7:29 PM
  • Facing the same situation here.

    Almost 6 years later the original post and we still don't have a solution?

    Murilo

    Friday, February 28, 2014 11:21 AM
  • True, Excel does not support this functionality due to the low amount of demand from their customer base. 

    Thus, a robust xml editor needs to be employed to overcome this simple issue which Excel experiences.

    Best Regards,

    Hiero

    Friday, May 9, 2014 2:40 PM
  • Since Excel apparently only support simple XML files and not files with lists-in-lists.

    1) How do you take data from excel and export it into a XML lists-in-lists format ?

    2) any suggested processes or third party tools that can be used ?

    Monday, August 31, 2015 12:48 PM
  • Hi Thomas

    I suggest you ask this as a new question (you can link to this discussion if you feel it provides useful background information) in a forum that better targets the topic.

    This is the VSTO forum, a specific .NET tool that runs in-process with Office applications, such as Excel. As such, the question was already off-topic in 2008 (!) but left in the forum because, at the time, there were no other forums on MSDN that discussed Office.

    Your best bet is probably the Excel for Developers forum:
    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, August 31, 2015 1:56 PM
    Moderator
  • I was having the same problem (xml maps with lists of lists are not exportable).

    My workaround suggestion would be:

    1. make xml map, that has no list of lists definition.

       a) Either edit your xsd schema

       b) or import xml file with just one child.

    2. If you want to fill parent/child data (with several childs) repeat parent data for every row (or at least you can repeat one of the atributes that is unique.

    3. Export xml data -> You'll get multiply rows with parent element

    4. Create xslt file that groups your denormalized table (multiply parent xml elements) into one single parent element

    5. Transform your exported denormalized xml file using xslt file

        a) you can transform your file using vba.

        b) you can download JAVA xslt parser (like SAXON) libraries and run transformation in command line.

    As I'm not expert in VBA, I could not managed to work it with VBA (I've got some errors regarding active X, blablabla)

    I used b) option and it worked fine for me.

    So now I have button in my Excel file, which

    1) Exports file into temporary denormalized XML file

    2) run bat file (which runs SAXON java library to transform temporary XML file) which creates normalized result XML file


    Thursday, July 7, 2016 1:10 PM
  • To Solve "list of lists" problem check out my schema

    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
             <xs:element name="EXP_TRP">
                   <xs:complexType>
                         <xs:sequence>
                               <xs:element name="DATA" maxOccurs="unbounded" minOccurs="0">
                                     <xs:complexType>
                                           <xs:sequence>
                                                 <xs:element name="SLNO" type="xs:int"></xs:element>
                                                 <xs:element name="ADSCODE" type="xs:int"></xs:element>
                                                 <xs:element name="EXP_SERIAL" type="xs:int"></xs:element>
                                                 <xs:element name="EXP_YEAR" type="xs:int"></xs:element>
                                                 <xs:element name="AMOUNT_FC" type="xs:int"></xs:element>
                                                 <xs:element name="CURRENCY_CODE" type="xs:int"></xs:element>
                                                 <xs:element name="REALIZE_DATE" type="xs:date"></xs:element>
                                                 <xs:element name="BANK_BILL_NO" type="xs:string"></xs:element>
                                                 <xs:element name="BANK_BILL_DATE" type="xs:date"></xs:element>
                                                 <xs:element name="BANK_CHARGES" minOccurs="0">
                                                       <xs:complexType>
                                                             <xs:sequence>
                                                                   <xs:element name="CHARGES">
                                                                         <xs:complexType>
                                                                               <xs:sequence>
                                                                                     <xs:element name="SLNO" type="xs:int"></xs:element>
                                                                                     <xs:element name="CHARGE_CODE" type="xs:int"></xs:element>
                                                                                     <xs:element name="CHARGE_AMOUNT" type="xs:int"></xs:element>
                                                                               </xs:sequence>
                                                                         </xs:complexType>
                                                                   </xs:element>
                                                             </xs:sequence>
                                                       </xs:complexType>
                                                 </xs:element>
                                           </xs:sequence>
                                     </xs:complexType>
                               </xs:element>
                         </xs:sequence>
                   </xs:complexType>
             </xs:element>
       </xs:schema>

    This will solve list of lists error.

    Now Please me on another issue.

    above schema generate xml. but list of list appears separately. While i want to show list inside list.

    Wednesday, November 30, 2016 10:22 AM
  • Hi MSabbir,

      Can you please post the XL sheet as well which you are using with the above schema?

    Thanks,



    • Edited by Aathreya Thursday, December 1, 2016 4:50 AM
    Thursday, December 1, 2016 4:50 AM