• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1051
  • Last Modified:

Convert XLS to XML

I need to convert a list of address to XML format, but I need the data formatted in a specific way. I need each row in the attached spreadsheet converted to XML format below.

<Region>
    <Name></Name>
    <Location>
      <Name></Name>
      <City>
        <Name></Name>
				<Office>
        	<ContactID></ContactID>
        	<BusinessUnit></BusinessUnit>
        	<CompanyTitle></CompanyTitle>
        	<Telephone></Telephone>
					<Fax></Fax>
					<BusinessAddress1><![CDATA[<br />]]><![CDATA[<br />]]></BusinessAddress1>
					<BusinessAddress2></BusinessAddress2>
					<BusinessCity></BusinessCity>
					<BusinessStateProvince></BusinessStateProvince>
					<BusinessPostalCode></BusinessPostalCode>
					<Abbreviation></Abbreviation>
					<AbbreviationName></AbbreviationName>
					<SalesOffice></SalesOffice>
					<Plant></Plant>
					<OperationsOffice></OperationsOffice>
					<Warehouse></Warehouse>
				</Office>
      </City>

Open in new window

M-I-SWACO-Deepwater-Facility-Add.xls
0
ksa1016
Asked:
ksa1016
  • 3
  • 2
1 Solution
 
Christian de BellefeuilleProgrammerCommented:
I don't know which version of Excel you are using, but take a look at this site:
http://www.mrexcel.com/articles/using-xml-in-excel.php

It explain how to define your schema file (XSD)

But if you don't have one of theses versions, i think you will have to write some VBA...
- Microsoft Office Professional Edition 2003 (R, P, V, A, S)
- Microsoft Office Professional Enterprise Edition 2003 (includes InfoPath)(V)
- Microsoft Excel 2003 (R)(The stand-alone Excel box is considered to be professional)
0
 
ksa1016Author Commented:
I might be missing something...but that doesn't show how to bring an XLS into a format with new data. I need to create a new XML from the spreadsheet.
0
 
Christian de BellefeuilleProgrammerCommented:
In my version of Excel, i can simply do a SAVE AS (under FILE menu), and choose the file type XML.
But i don't know if in the most advanced version there's some additionnal popup  to let you choose an XSD file to properly format your XML file.  The help i can give you about this problem is quite limited.

All i could do is to write some VBA to do that.

I'll let other people answer, and if you are still stuck, just post a comment to ask for my help and i'll write you few lines to save it in XML.
0
 
Christian de BellefeuilleProgrammerCommented:
Have you found your solution?  Or still need some VBA to translate it to XML?
0
 
ksa1016Author Commented:
Your link helped me figure it out. I transformed the XLS data into XML. Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now