How to convert an MS excel 2010 file into XML file

Question

0.00/5 (No votes)

See more:

Hi All,

I need to convert a excel file into xml format file.

This can be done by using the feature available in MS excel itself. But, I need to convert such that the repetitive values grouped and tagged in XML.

Below is my requirement,

This is my excel file,

Last Name	Sales	Country	Quarter
Smith	$16,753.00	UK	Qtr 3
Johnson	$14,808.00	USA	Qtr 4
Williams	$10,644.00	UK	Qtr 2
Jones	$1,390.00	USA	Qtr 3
Brown	$4,865.00	USA	Qtr 4
Williams	$12,438.00	UK	Qtr 1
Johnson	$9,339.00	UK	Qtr 2
Smith	$18,919.00	USA	Qtr 3
Jones	$9,213.00	USA	Qtr 4
Jones	$7,433.00	UK	Qtr 1
Brown	$3,255.00	USA	Qtr 2
Williams	$14,867.00	USA	Qtr 3
Williams	$19,302.00	UK	Qtr 4
Smith	$9,698.00	USA	Qtr 1

When this is exported as xml file, it will by default come as,

XML

<?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>
	<record>
		<LastName>Williams</LastName>
		<Sales>10644</Sales>
		<Country>UK</Country>
		<Quarter>Qtr 2</Quarter>
	</record>
	<record>
		<LastName>Jones</LastName>
		<Sales>1390</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 3</Quarter>
	</record>
	<record>
		<LastName>Brown</LastName>
		<Sales>4865</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 4</Quarter>
	</record>
	<record>
		<LastName>Williams</LastName>
		<Sales>12438</Sales>
		<Country>UK</Country>
		<Quarter>Qtr 1</Quarter>
	</record>
	<record>
		<LastName>Johnson</LastName>
		<Sales>9339</Sales>
		<Country>UK</Country>
		<Quarter>Qtr 2</Quarter>
	</record>
	<record>
		<LastName>Smith</LastName>
		<Sales>18919</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 3</Quarter>
	</record>
	<record>
		<LastName>Jones</LastName>
		<Sales>9213</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 4</Quarter>
	</record>
	<record>
		<LastName>Jones</LastName>
		<Sales>7433</Sales>
		<Country>UK</Country>
		<Quarter>Qtr 1</Quarter>
	</record>
	<record>
		<LastName>Brown</LastName>
		<Sales>3255</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 2</Quarter>
	</record>
	<record>
		<LastName>Williams</LastName>
		<Sales>14867</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 3</Quarter>
	</record>
	<record>
		<LastName>Williams</LastName>
		<Sales>19302</Sales>
		<Country>UK</Country>
		<Quarter>Qtr 4</Quarter>
	</record>
	<record>
		<LastName>Smith</LastName>
		<Sales>9698</Sales>
		<Country>USA</Country>
		<Quarter>Qtr 1</Quarter>
	</record>
</data-set>

But I want such that,
take for example <lastname>Smith comes thrice in this xml script, but I want that tag to appear just once, and the remaining tags Sales, Country, Quarter should get repeated thrice within LastName tag.

I am new to this XML, so I am bit confused on this.

Guide me out on this.

Regards,
Shivakumar A

Posted 24-Jul-14 6:29am

Shivakumar Aiyadurai

Updated 26-Jul-14 15:26pm

Andrius Leonavicius

v3

Add a Solution

Comments

Maciej Los 24-Jul-14 16:42pm

What have you done till now? Where are you stuck?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-07-26T15:19:00

Hi,

Based on your example, it seems that you have read this tutorial: XML[^].

I'm guessing that your desired output could be something like this:

XML

<data-set>
	<records-by-last-name>
		<lastname>Smith</lastname>
		<records>
			<record>
				<sales>16753</sales>
				<country>UK</country>
				<quarter>Qtr 3</quarter>
			</record>
			<record>
				<sales>18919</sales>
				<country>USA</country>
				<quarter>Qtr 3</quarter>
			</record>
			<record>
				<sales>9698</sales>
				<country>USA</country>
				<quarter>Qtr 1</quarter>
			</record>
		</records>
	</records-by-last-name>
	<records-by-last-name>
		<lastname>Williams</lastname>
		<records>
			<record>
				<sales>14867</sales>
				<country>USA</country>
				<quarter>Qtr 3</quarter>
			</record>
			<record>
				<sales>19302</sales>
				<country>UK</country>
				<quarter>Qtr 4</quarter>			
			</record>
		</records>
	</records-by-last-name>
</data-set>

This XML document could be imported into MS Excel. However, you won't be able to export the data to XML file in such structure using Export XML Data...

In order to import or export XML document, you need to add an XML map. If specified XML source doesn't refer to a schema (for example, as in my example above), MS Excel will create a schema based on the XML. Note that the XML schema describes the structure of the XML document.

MS Excel has some XML limitations. The attempt to export the data using this XML schema should end up with the following error message: "Cannot save or export XML data. The XML maps in this workbook are not exportable.". "Verify Map for Export..." would give more information about the error. In this case, it's because XML map contains list of lists and a mapped element's relationship with other elements cannot be preserved (MS Excel can't handle with it).

In conclusion, I doubt there is an easy way to do what you want (unless you actually need something else). However, if you want to export data using some complex XML structure, you could write VBA code to build the XML document from your data...