Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do you easily convert an excel file into a XML file?

When trying to save as an XML File, it complains that the file does not have an XML mapping. Clicking help brings up pretty complicated stuff about XML Mapping file, XLD and some other acronyms. Why is it so complicated?

Lately I've realized that tab delimited, CSV and others are prone to formating issues (comas in a field, new lines, quotes, ...). So I think that XML is a better way to process excel data.

Please advise. Maybe a freeway tool?

share|improve this question
    
Tab delimited avoids the issue of commas in fields as they are not part of the delimiter. If you save CSV files using Excel and it detects commas in some fields it will wrap them in quotes to avoid ambiguity, but the system you are inputting into needs to understand this and strip them. – AdamV Mar 25 '10 at 11:08

Here is an oldish reference to the 'XML::Excel' Perl module.
There is another question on Stackoverflow that shows samples for converting to CSV that may give you ideas on using such scripts.

There is also a short IBM developerWorks article on tips to Convert Excel data to XML which points to some resources.

share|improve this answer
    
The link to IBM developerWorks is broken, sadly... do you know of an updated link? – Nathan Fellman Jul 7 '13 at 19:56
    
I took the liberty of fixing the link. – Nathan Fellman Jul 7 '13 at 20:07

here is the perl code to convert CSV file into XML file

use XML::CSV;
$csv_obj = XML::CSV->new();

 $csv_obj->parse_doc("customer.csv", {headings => 1});

 $csv_obj->print_xml("customer.xml");

more details you can find in Convert CSV file into XML file in Perl

share|improve this answer

In Excel 2007, by default spreadsheets are saved into a complicated XML format(xlsx) which then gets zipped up into a single file. If you want to save to a custom XML format you have to define a schema which defines how the data will be formed. There is no just "save to XML" option.

share|improve this answer
up vote 0 down vote accepted

How about that: http://office.microsoft.com/en-us/excel/HA102635091033.aspx

share|improve this answer
1  
Link is broken :-( – rjdkolb Feb 4 '15 at 12:53

If you want to open the developer tab, here is some VBA code to generate XML.

I previously wrote this answer on a similar question on SO, so this is copypasted from there.

It might be a bit advanced if you are not used to the developer part of Excel, but if you do this once, you can then automatically generate xml documents from your Excel data with the press of one button.

'*****************************************************************
'This is how the main structure of the XMl file is created in VBA
'Use this as guidance to create new XML files.
'*****************************************************************

Sub CreateXML()

Dim objDom As DOMDocument
Dim objRootElem As IXMLDOMElement
Dim objSubRootElem As IXMLDOMElement
Dim objMemberElem As IXMLDOMElement
Dim objSubMemberElem As IXMLDOMElement
Dim objMemberRel As IXMLDOMAttribute
Dim objMemberName As IXMLDOMElement

Set objDom = New DOMDocument    

' Creates root element
'Set objRootElem = objDom.createElement("r1")
'objDom.appendChild objRootElem

' Creates sub root element
'Set objSubRootElem = objDom.createElement("r2")
'objRootElem.appendChild objSubRootElem

' Creates Error Date & Time element
'Set objMemberElem = objDom.createElement("r3")
'objSubRootElem.appendChild objMemberElem

' Create element under Member element, and
' gives value "some guy"
'Set objMemberName = objDom.createElement("r3_Tag")
'objMemberElem.appendChild objMemberName
'objMemberName.Text = "value"

' Creates User Name element
'Set objMemberElem = objDom.createElement("r2_tag")
'objSubRootElem.appendChild objMemberElem
'objMemberElem.Text = "value"

' Creates Error Date & Time element
'Set objMemberElem = objDom.createElement("r3")
'objSubRootElem.appendChild objMemberElem

' Create element under Member element, and
' gives value "some guy"
'Set objMemberName = objDom.createElement("r3_Tag")
'objMemberElem.appendChild objMemberName
'objMemberName.Text = "value"

' Creates User Name element
'Set objMemberElem = objDom.createElement("r2_tag")
'objSubRootElem.appendChild objMemberElem
'objMemberElem.Text = "value"

' To make this some what more readable for humans, we need to add formatting, indent it and add a carriage return before its children. Then recursively format the children with increased indentation.

Sub FormatXmlNode(ByVal node As IXMLDOMNode, ByVal indent As Integer)
    Dim child As IXMLDOMNode
    Dim text_only As Boolean

    ' Do nothing if this is a text node.
    If TypeOf node Is IXMLDOMText Then Exit Sub

    ' See if this node contains only text.
    text_only = True
    If node.HasChildNodes Then
        For Each child In node.ChildNodes
            If Not (TypeOf child Is IXMLDOMText) Then
                text_only = False
                Exit For
            End If
        Next child
    End If

    ' Process child nodes.
    If node.HasChildNodes Then

        ' Add a carriage return before the children.
        If Not text_only Then
            node.InsertBefore node.OwnerDocument.createTextNode(Chr(10)), node.FirstChild
        End If

        ' Format the children.
        For Each child In node.ChildNodes
            FormatXmlNode child, indent + 2
        Next child
    End If

    ' Format this element.
    If indent > 0 Then

        ' Indent before this element.
        node.ParentNode.InsertBefore node.OwnerDocument.createTextNode(Space$(indent)), node

        ' Indent after the last child node.
        If Not text_only Then node.appendChild node.OwnerDocument.createTextNode(Space$(indent))

        ' Add a carriage return after this node.
        If node.NextSibling Is Nothing Then
            node.ParentNode.appendChild node.OwnerDocument.createTextNode(Chr(10))
        Else
            node.ParentNode.InsertBefore node.OwnerDocument.createTextNode(Chr(10)), node.NextSibling
        End If
    End If
End Sub

' Saves XML data to disk.

On Error Resume Next
MkDir ("C:\Users\" & Environ$("Username") & _
"\Desktop\FXML_FILES") 'Creates folder on desktop, ignores error if it already exists
On Error GoTo 0

objDom.Save ("C:\Users\" & Environ$("Username") & _
"\Desktop\XML_FILES\" & "filename.xml")
share|improve this answer

Excel has an XML tool add in for converting from spreadsheet to XML: http://office.microsoft.com/en-us/excel-help/create-an-xml-data-file-and-xml-schema-file-from-worksheet-data-HA010342365.aspx

share|improve this answer
1  
Same link as the accepted answer (only for another Office version) – nixda Jun 5 '14 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.