excel - 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.

Answer 1

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.