Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I export simple repeating data from excel as xml?

I'm attempting to use the Excel 2007 XML Developers tools, but I'm not able to export a simple set of simple repeating data.

I have a worksheet with headers, and columns of data.

I have an xsd which describes (I think) a map of the data, with the first element repeating.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="http://tempuri.org/FeedbackLookup.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema.xsd" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="FeedbackLookup">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="RevieweeInfo" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="RevieweeName" type="xs:string">
              </xs:element>
              <xs:element name="RevieweeTitle" type="xs:string">
              </xs:element>
              <xs:element name="ReviewLevel" type="xs:string">
              </xs:element>
              <xs:element name="RecipientName" type="xs:string" />
              <xs:element name="RecipientEmail" type="xs:string" />
              <xs:element name="RecipientTitle" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

And mapped that to the headings in my xml file.

Excel XML Pane

But when I click Verify Map for Export I get the following error popup:

A mapped element's relationship with other elements cannot be preserved

The elements are all mapped to the same page, and are all regular data columns.

Excel Header Row

What am I doing wrong? At this point would it be faster to write the naive VBA to create the XML?

like image 441
C. Ross Avatar asked Nov 23 '10 14:11

C. Ross


3 Answers

I am not sure if this qualifies as an answer ... but your xsd worked OK for me.

Here is the exported xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:FeedbackLookup xmlns:ns1="http://tempuri.org/FeedbackLookup.xsd">
    <ns1:RevieweeInfo>
        <ns1:RevieweeName>1</ns1:RevieweeName>
        <ns1:RevieweeTitle>1</ns1:RevieweeTitle>
        <ns1:ReviewLevel>1</ns1:ReviewLevel>
        <ns1:RecipientName>1</ns1:RecipientName>
        <ns1:RecipientEmail>1</ns1:RecipientEmail>
        <ns1:RecipientTitle>1</ns1:RecipientTitle>
    </ns1:RevieweeInfo>
    <ns1:RevieweeInfo>
        <ns1:RevieweeName>2</ns1:RevieweeName>
        <ns1:RevieweeTitle>2</ns1:RevieweeTitle>
        <ns1:ReviewLevel>2</ns1:ReviewLevel>
        <ns1:RecipientName>2</ns1:RecipientName>
        <ns1:RecipientEmail>2</ns1:RecipientEmail>
        <ns1:RecipientTitle>2</ns1:RecipientTitle>
    </ns1:RevieweeInfo>
</ns1:FeedbackLookup>

Edit Screen capture

alt text

like image 175
Dr. belisarius Avatar answered Oct 10 '22 07:10

Dr. belisarius


As per @Petoj's comment, try dragging the ns2:RevieweeInfo element onto the worksheet instead of each field individually.

This resolved this exact issue for me, but I had to create a separate worksheet first that only contained columns matching the target element's attributes.

like image 45
wolfyuk Avatar answered Oct 10 '22 06:10

wolfyuk


Check out this blog on the same error.

http://itbadass.blogspot.com/2010/11/converting-excel-to-xml-mapped-elements.html

It looks like it may be Excel "not seeing" all the data it needed to. His solution was to save the data file off to a CSV, then open that and try the import again. The major advantage I can see of doing that would be to strip any table formatting or funky data types out that may be tripping up Excel.

I also had to put my data next to each other in columns and ensure I was dragging the element icon onto the first row of data for the column (as opposed to the column name) to get my mapping right.

like image 23
Watki02 Avatar answered Oct 10 '22 05:10

Watki02