# Need Help Working with XML and Excel

**URL:** <https://boards.straightdope.com/t/need-help-working-with-xml-and-excel/572371>\
**Category:** Factual Questions\
**Created:** [February 23, 2011, 7:39pm UTC](https://boards.straightdope.com/t/need-help-working-with-xml-and-excel/572371 "2011-02-23T19:39:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![HookerChemical](https://avatars.discourse-cdn.com/v4/letter/h/278dde/32.png) [@HookerChemical](https://boards.straightdope.com/u/HookerChemical)\
**Post date:** [February 23, 2011, 7:39pm UTC](https://boards.straightdope.com/t/need-help-working-with-xml-and-excel/572371/1 "2011-02-23T19:39:03Z")

</div>

I need to create an XML file containing data from Excel from an existing schema. The schema includes nested repeating elements, and Excel doesn’t seem to like nested repeating elements. When I try to verify that the XML will export, I get the “lists of lists” error indicating that I won’t be able to export the data.

As an example of what I’m trying to do, imagine annual sales of games, where the types of games are not explicitly defined in the schema but are assigned to the **GameName** element instead.

Here is a simplified example of the structure of the schema. Please excuse any syntax issues; I’m only trying to get data into the format and don’t require a complete grasp of the schema formatting.

```auto

 <xsd:extension gYear=”YearGameSales”>
        <xsd:extension string=”GameNameDetail”>
            <xsd:extension name=”GameSales”>

```

Where **YearGameSales** repeats for each year and is therefore complex, **GameName** repeats for each game and is therefore comples, and **GameSales** is the value of the sales and is simple.  
And here is the type of table I have in Excel:

```auto

Year Jacks Chess Checkers
2000 100 40 120
2001 400 30 100
2002 50 100 50

```

Can I even use Excel to get these data into the required schema? If so, how? If not, how do I get the data from Excel into XML that works with the schema? I’m fairly new to XML and schema, but I’m reasonable proficient in Excel.

Finally, the Excel and Office help files and online help have been incredibly opaque or unhelpful for working with XML in Excel.

---

<div class="post-metadata">

**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\
**Post date:** [February 23, 2011, 8:50pm UTC](https://boards.straightdope.com/t/need-help-working-with-xml-and-excel/572371/2 "2011-02-23T20:50:16Z")

</div>

I’m not much help here. I’ve run into problems similar to yours in that I couldn’t get Excel to do the proper mapping from it’s tables.

My advice to you doesn’t carry expert status but may give you some ideas of your own.

Since excel didn’t like repeated elements I wrote VBA code that explicitly created my desired XML files.  
That meant that I had to keep a list of the desired tags somewhere and do some extra calculations that recognized the start and stop areas on the tables etc.

By knowing the tables structure and my desired structure for the XML file my VBA assembled the XML text line by line in my workbook on a separate sheet. Then the VBA code opened an output file, copied the XML sheet to the the new workbook and saved it as a text file with and XML suffix.

---

<div class="post-metadata">

**Author:** ![The\_Tao\_s\_Revenge](https://avatars.discourse-cdn.com/v4/letter/t/73ab20/32.png) [@The\_Tao\_s\_Revenge](https://boards.straightdope.com/u/The_Tao_s_Revenge)\
**Post date:** [February 23, 2011, 9:52pm UTC](https://boards.straightdope.com/t/need-help-working-with-xml-and-excel/572371/3 "2011-02-23T21:52:36Z")

</div>

Not an expert either, but on a similar project I used [ADO.NET](http://ADO.NET) in Visual basic .net to query an Access database. The same method supports Excel.

[http://support.microsoft.com/kb/311731](http://support.microsoft.com/kb/311731) \<- the example is ASP, but the relevant code will work in winforms.

Assuming someone doesn’t come along with a better idea you could use ADO to import the data as a table in a dataset, and then loop through the datatable writing it’s values to an XML file with your desired scheme.