Need help to convert Excel data to XML

**uakash7** · 02-11-2021, 12:19 PM

Hi,

I'm trying to convert Excel data to XML but getting this error message - "Cannot save or export XML data. The XML maps in the workbook are not exportable."
Please help to fix this, enclosing the error screenshot, Excel file and XML mapping file (uploaded as .txt file as I was unable to upload .xml file, please save as .xml). Thanks.

**sandy666** · 02-11-2021, 03:29 PM

you can try with

Code:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<EntityID>EntityID</EntityID>
	<data>
		<date>
			<day>day</day>
			<month>month</month>
			<year>year</year>
		</date>
		<recID>recID</recID>
		<time>time</time>
		<itemSold>ItemSold</itemSold>
		<Price>Price</Price>
	</data>
</DataImport>

as XML schema (not XSD!)
but probably you'll see Denormalized Data error
so I suggest
W3C link
and
Normalizing XML link

**sandy666** · 02-11-2021, 06:23 PM

or try this Excel to XML

InveralData.xlsx.xml.txt
remove .txt and stay with .xml
and after mapping
test.xml.txt

**uakash7** · 02-12-2021, 06:54 AM

Thank you Sandy, I tried both but it didn't give me the desired output. I'm looking for output like this -

Code:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport>
    <forecast>
	<Entity>700</Entity>
	<data>
		<date>
			<day>19</day>
			<month>1</month>
			<year>2021</year>
		</date>
		<period recID="7001">
			<time>8:00</time>
			<ItemSold>19</ItemSold>
			<Price>219</Price>
		</period>
		<period recID="7001">
			<time>8:30</time>
			<ItemSold>21</ItemSold>
			<Price>219</Price>
		</period>
	</data>
    </forecast>
    <forecast>
	<Entity>701</Entity>
	<data>
		<date>
			<day>20</day>
			<month>1</month>
			<year>2021</year>
		</date>
		<period recID="7002">
			<time>8:00</time>
			<ItemSold>20</ItemSold>
			<Price>220</Price>
		</period>
		<period recID="7002">
			<time>8:30</time>
			<ItemSold>23</ItemSold>
			<Price>220</Price>
		</period>
	</data>
    </forecast>
</DataImport>

However I'm getting output like this -

Code:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<records>
		<record>
			<EntityID>700</EntityID>
			<ItemSold>23</ItemSold>
			<Price>119</Price>
			<day>19</day>
			<month>2</month>
			<recID>7001</recID>
			<time>8:00</time>
			<year>2021</year>
		</record>
		<record>
			<EntityID>700</EntityID>
			<ItemSold>25</ItemSold>
			<Price>120</Price>
			<day>19</day>
			<month>2</month>
			<recID>7001</recID>
			<time>8:30</time>
			<year>2021</year>
		</record>
		<record>
			<EntityID>700</EntityID>
			<ItemSold>24</ItemSold>
			<Price>121</Price>
			<day>19</day>
			<month>2</month>
			<recID>7001</recID>
			<time>9:00</time>
			<year>2021</year>
		</record>

and continues..

so the entity IDs are not repeated and date is not repeated within entity tag if that helps.

**uakash7** · 02-12-2021, 04:12 AM

**sandy666** · 02-12-2021, 09:26 AM

here is a proper xml for mapping
required.txt
if you will see warning choose first
existingformatting.png
and here is exported xml from the table
exported.txt

Table

EntityID	day	month	year	recID	time	ItemSold	Price
700	19	2	2021	7001	8:00	23	119
700	19	2	2021	7001	8:30	25	120
700	19	2	2021	7001	9:00	24	121
700	19	2	2021	7001	9:30	26	110
700	19	2	2021	7001	10:00	21	119
700	19	2	2021	7001	10:30	20	119
700	19	2	2021	7001	11:00	19	120
700	19	2	2021	7001	11:30	10	119
701	19	2	2021	7002	8:00	23	121
701	19	2	2021	7002	8:30	23	123
701	19	2	2021	7002	9:00	22	119
701	19	2	2021	7002	9:30	23	119
701	19	2	2021	7002	10:00	25	119
701	20	2	2021	7002	8:00	23	145
701	20	2	2021	7002	8:30	27	121
701	20	2	2021	7002	9:00	23	119
702	19	2	2021	7003	8:00	25	128
702	19	2	2021	7003	8:30	23	123
702	19	2	2021	7003	9:00	23	124
702	19	2	2021	7003	9:30	23	119
702	20	2	2021	7003	8:00	24	119
702	21	2	2021	7003	9:30	25	128
702	22	2	2021	7003	9:30	26	119
702	22	2	2021	7003	10:00	21	120

**uakash7** · 02-14-2021, 06:54 PM

Hi Sandy,

The exported xml file looks like -

Code:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>119</Price>
			</period>
		</data>
	</forecast>
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:30</time>
				<ItemSold>25</ItemSold>
				<Price>120</Price>
			</period>
		</data>
	</forecast>

However, what I need is -

Code:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<forecast>
		<Entity>700</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7001">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>119</Price>
			</period>
			<period recID="7001">
				<time>8:30</time>
				<ItemSold>25</ItemSold>
				<Price>120</Price>
			</period>
		</data>
	</forecast>
	<forecast>
		<Entity>701</Entity>
		<data>
			<date>
				<day>19</day>
				<month>2</month>
				<year>2021</year>
			</date>
			<period recID="7002">
				<time>8:00</time>
				<ItemSold>23</ItemSold>
				<Price>121</Price>
			</period>
		</data>
	</forecast>
</DataImport>

Only one entry for each unique entity ID and one date entry for each unique date within each entity tag.
Adding another sample file with few examples, hope it helps.
Is there a way we can do this via VBA if not possible by just using XML mapping?