1

Is there any way to do this?

The structure my file has is something like the one below, but the file is large (around 5000 records in an 8MB xml file) and it has at least has one parent with many children that can vary from entry to entry (meaning that for some records, the number of children is not fixed and they use the same tag, like children of 

MainCourseCategories
on the example below)

When I use Insert -> PivotTable -> Use an external data source -> Choose Connection... I get an error that says "The type of connection selected cannot be used to create a PivotTable"

<?xml version="1.0" ?>
<institutions>
    <institution>
        <TypeOfProvider>Provider Z</TypeOfProvider>
        <MainCourseCategories>
            <MainCourseCategory>Category 1</MainCourseCategory>
            <MainCourseCategory>Category 2</MainCourseCategory>
            <MainCourseCategory>Category 3</MainCourseCategory>
            <MainCourseCategory>Category 4</MainCourseCategory>
        </MainCourseCategories>
    </institution>
    <institution>
        <TypeOfProvider>Provider A</TypeOfProvider>
        <MainCourseCategories>
            <MainCourseCategory>Category 1</MainCourseCategory>
            <MainCourseCategory>Category 2</MainCourseCategory>
            <MainCourseCategory>Category 7</MainCourseCategory>
        </MainCourseCategories>
    </institution>
    </institutions>
edgarator
  • 296
  • 1
  • 4
  • 17
  • Nothing in this field. I manage to import the xml file as a table, and then the summary from there, but the table adds many records where a 1 to many relationship is found... and that's not really helpful. Once I create the table, a connection is added to the data source which I cannot use for this purpose. I also rewrote my Python script to make a one level only data source but it didn't work either. The only thing that I have as a reference is a file that works beautifully with a completely source-blocked pivot table. – edgarator Dec 21 '11 at 06:59
  • Out of curiosity - have you manage to open your file in Excel as a normal file and see the values in the tabular form? I believe that exercise would fail, which means you have to pre-process your file. (XSLT?) – Juliusz Dec 21 '11 at 15:27
  • Hi Julius, the file opens fine. But I've seen some pivot tables that have like an embedded database within, with relationships and everything. I was wondering if I could do the same with my plain XML file. – edgarator Dec 24 '11 at 08:03

0 Answers0