CODEWITHSUNDEEP
excelxml
user2097141
user2097141

Reputation: 327

Creating XML from Excel

I am using Excel for Office 365 ProPlus. And I'm trying to create an XML file from Excel data (not more than 100 rows).

Started with the following sample schema file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <record>
       <LastName>Smith</LastName>
       <Sales>16753</Sales>
       <Country>UK</Country>
       <Quarter>Qtr 3</Quarter>
    </record>
    <record>
       <LastName>Johnson</LastName>
       <Sales>14808</Sales>
       <Country>USA</Country>
       <Quarter>Qtr 4</Quarter>
    </record>
</data-set>

And it worked on a sample data file: Sample Excel table

Now I'm trying the same schema on an extract from my real data (though in fact I will need a more sophisticated schema with 2 layers - see below): Some real Excel data

Now I'm getting an error: "...is not exportable because it contains the following: A mapped element relationship with other elements can't be preserved".

I may be blind but I can't see any principal difference between two sources. I've tried copying formatting, remapping from scratch, renaming the columns to no avail.

Does anyone have an idea of how to make it work?

BTW, my desired schema will look similar to this one:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Graduate>
<HUSID>1600000000003</HUSID>
<OWNSTU>8936</OWNSTU>
<COUNTRY>XF</COUNTRY>
<EMAIL>[email protected]</EMAIL>
<FNAMES>Diana</FNAMES>

<SURNAME>XYZ</SURNAME>
<UKTEL>02080000000</UKTEL>
<UKMOB>07700000000</UKMOB>
<PostalAddress>
<ADDRESSLN1>36 Street </ADDRESSLN1>
<ADDRESSLN2>London</ADDRESSLN2>
<ADDRESSLN4>United Kingdom</ADDRESSLN4>
<POSTCODE>HA7 HA7</POSTCODE>
</PostalAddress>
</Graduate>
<Graduate>
<HUSID>160000000013</HUSID>
<OWNSTU>8936</OWNSTU>
<COUNTRY>XF</COUNTRY>
<EMAIL>[email protected]</EMAIL>
<FNAMES>Ana</FNAMES>

<SURNAME>LLL</SURNAME>
<UKTEL>02089999999</UKTEL>
<UKMOB>07777777777</UKMOB>
<PostalAddress>
<ADDRESSLN1>36  Park ,</ADDRESSLN1>
<ADDRESSLN2>London</ADDRESSLN2>
<ADDRESSLN4>United Kingdom</ADDRESSLN4>
<POSTCODE>HA7 HA8</POSTCODE>
</PostalAddress>
</Graduate>
</data-set>

Upvotes: 1

Views: 146

Answers (1)

user2097141
user2097141

Reputation: 327

SOLVED (though I still have no explanation as to why this work around was needed).

  1. Leave all columns you need to map next to each other and in the order of tags in your schema;
  2. Not sure this is mandatory - but blank values may prevent mapping of the cells after them;
  3. Drag the top-level tag (GRADUATE in my case) to the first data cell in the first column (may work with header but not tested yet);

You will now have all columns mapped in their order and in the order of tags in your schema. The message changes to Map is exportable.

Comments are welcome from those who can explain why mapping works this way and not by mapping individual tags as all manuals suggest.

Upvotes: 1

Related Questions