Convert rows of spreadsheet to separate XML files

Question

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.

At first you should choose whether you need to do this from within Excel - open the file in Excel and generate XML then. Or whether you need to do this from outside Excel - run a program which reads the data from *.xls(x) or *.csv file and generate XML from those data. If the first, you could use VBA and the MSXML.DOMDocument. If the second you should mention which programming language you prefer to use. — Axel Richter, Nov 24 '14 at 07:08
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. — djc123, Nov 24 '14 at 11:25

jarondl · Answer 1 · 2014-11-24T08:56:20.963

I would go the xslt2 path. Save the file as csv, and then you can use a xslt template such as : http://andrewjwelch.com/code/xslt/csv/csv-to-xml_v2.html to convert it to xml.

Edit the template to create a new xsl:result-document whenever you want your new file. Basically you should edit the lines containing

<row>
<xsl:variable name="lineItems" select="fn:getTokens(.)" as="xs:string+" />

Where a new <row> element is created, and replace them with something like:

<xsl:variable name="lineItems" select="fn:getTokens(.)" as="xs:string+" />
<xsl:result-document href="$lineItems[1]">

Also replace </row> with </xsl:result-document> of-course you should change the template to your output needs, and change <xsl:param name="pathToCSV" select="'file:///c:/csv.csv'" /> to reference to your file.

The code above is untested, but I believe it should work.

Edit 1:

xslt2 bindings exist in many programing languages, and there are stand-alone versions as well. You can also split the process in two: save the xml version in excel, and transform the resulting xml to many xml files.

score 0 · Accepted Answer · answered Nov 24 '14 at 12:14

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

Thanks Axel - that shows exactly what I need to achieve. – djc123 Nov 25 '14 at 00:21 — djc123, Nov 25 '14 at 00:21

score 0 · Answer 3 · edited Aug 17 '17 at 15:46

thanks for the great example. When converting a very large excel spreadsheet with more than 25 rows, I would recommend the following:

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

Again, thanks for the great example, it did a great job converting very large spreadsheets into XML files suitable from import into other systems.

Convert rows of spreadsheet to separate XML files

3 Answers3

Edit 1:

Linked