Excel – Convert rows of spreadsheet to separate XML files

excelxml

I am looking for a system to convert a spreadsheet (or CSV file) with a few hundred rows of data into individual XML files. One column in the spreadsheet would be the required filename, and the other columns would contain data to be inserted into specific nodes in the XML based on a template.

Kind Regards,
Dan

Edit 1
If it can be handled totally within Excel, creating a new XML for each row and inserting the relevant column data into the correct location of the XML template that would be the best outcome.

Best Answer

Using VBA and the MSXML2.DOMDocument this could be achieved as follows:

Assuming you have a Excel sheet like this: enter image description here

Then VBA like this should create a XML file for each row:

Sub testXLStoXML()
 sTemplateXML = _
        "<?xml version='1.0'?>" + vbNewLine + _
        "<data>" + vbNewLine + _
        "   <name>" + vbNewLine + _
        "   </name>" + vbNewLine + _
        "   <birthdate>" + vbNewLine + _
        "   </birthdate>" + vbNewLine + _
        "   <amount>" + vbNewLine + _
        "   </amount>" + vbNewLine + _
        "</data>" + vbNewLine

 Set doc = CreateObject("MSXML2.DOMDocument")
 doc.async = False
 doc.validateOnParse = False
 doc.resolveExternals = False

 With ActiveWorkbook.Worksheets(1)
  lLastRow = .UsedRange.Rows.Count

  For lRow = 2 To lLastRow
   sFile = .Cells(lRow, 1).Value
   sName = .Cells(lRow, 2).Value
   sBirthdate = Format(.Cells(lRow, 3).Value, "YYYY-MM-DD")
   sAmount = Format(.Cells(lRow, 4).Value, "Currency")
   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("name")(0).appendChild doc.createTextNode(sName)
   doc.getElementsByTagName("birthdate")(0).appendChild doc.createTextNode(sBirthdate)
   doc.getElementsByTagName("amount")(0).appendChild doc.createTextNode(sAmount)
   doc.Save sFile
  Next

 End With
End Sub

XML escape characters

There are only five:

"   &quot;
'   &apos;
<   &lt;
>   &gt;
&   &amp;

Escaping characters depends on where the special character is used.

The examples can be validated at the W3C Markup Validation Service.

Text

The safe way is to escape all five characters in text. However, the three characters ", ' and > needn't be escaped in text:

<?xml version="1.0"?>
<valid>"'></valid>

Attributes

The safe way is to escape all five characters in attributes. However, the > character needn't be escaped in attributes:

<?xml version="1.0"?>
<valid attribute=">"/>

The ' character needn't be escaped in attributes if the quotes are ":

<?xml version="1.0"?>
<valid attribute="'"/>

Likewise, the " needn't be escaped in attributes if the quotes are ':

<?xml version="1.0"?>
<valid attribute='"'/>

Comments

All five special characters must not be escaped in comments:

<?xml version="1.0"?>
<valid>
<!-- "'<>& -->
</valid>

CDATA

All five special characters must not be escaped in CDATA sections:

<?xml version="1.0"?>
<valid>
<![CDATA["'<>&]]>
</valid>

Processing instructions

All five special characters must not be escaped in XML processing instructions:

<?xml version="1.0"?>
<?process <"'&> ?>
<valid/>

XML vs. HTML

HTML has its own set of escape codes which cover a lot more characters.

Python – How to parse XML and count instances of a particular node attribute

I suggest ElementTree. There are other compatible implementations of the same API, such as lxml, and cElementTree in the Python standard library itself; but, in this context, what they chiefly add is even more speed -- the ease of programming part depends on the API, which ElementTree defines.

First build an Element instance root from the XML, e.g. with the XML function, or by parsing a file with something like:

import xml.etree.ElementTree as ET
root = ET.parse('thefile.xml').getroot()

Or any of the many other ways shown at ElementTree. Then do something like:

for type_tag in root.findall('bar/type'):
    value = type_tag.get('foobar')
    print(value)

And similar, usually pretty simple, code patterns.