Convert XML to (meaningful) CSV
Convert xml to excel/csv
Quick convert XML to Excel - Stack Overflow
Converting CSV file to XML
How to Convert XML to CSV via Python?
Can XML file be converted to Excel?
Steps to Convert XML to Excel:
- Upload XML file
- Click 'Run Conversion' button
- When the file is converted - download Excel file
How do I open an XML file in Excel?
Steps to use XML to Excel Converter:
- Upload XML file
- Click 'Run Conversion' button
- When the file is converted - download Excel file
- Open Excel file in Microsoft Excel
Videos
Hello Redditors. I have a question that has landed on my desk, and which way outside of my practical skill set.
One party has an application that they are heavily invested in. They need to send data - say a list of orders - to another party. The only option for doing so is an XML export file. Let us presume that the vendor for this software is no longer available, so the XML is what it is.
The second party is setup in a similar way. They are used to ingesting lists of orders into their software by way of CSV files, setup in a particular format. Each column has a header and represents a given data set.
The question that I am looking at is how difficult would it be to convert source XML A into CSV B, with the appropriate data fields in the right places. We would need to do this on a regular basis. Keeping in mind that we have little to know development ability on staff, now might we achieve something along these lines? Specifically...
What might this process look like? Excel macro? Web app?
Where might we look to develop this process, whatever it looks like?
Any thoughts at all, given all of this rambling?
Thanks in advance guys.
Why so complicated? Just open the File with File->Open choose xml and load it. See what'll happen.
If you're using Excel 2007 and want to use XSLT, your best bet would probably be use the EXPath Zip Module features to modify an existing Excel .xslx file.
My preferred option, however, would be to use a small Excel VBA Macro.
I've included sample code below for a VBA procedure called 'load' - this sample uses the XML DOM, so all 112K rows of your XML will be first loaded into memory, but if performance isn't an issue its simpler than the SAX alternative.
You would need to modify xpathToExtractRow to suit your XML input structure. There is also an assumption that the immediate child nodes of the XML row element contain the cell data you wish to import as text nodes, if not, you will need to use a SelectNode call to get the data you require.
Private dom As DOMDocument60
Public Sub load()
Dim nodeList As IXMLDOMNodeList
Dim nodeRow As IXMLDOMNode
Dim nodeCell As IXMLDOMNode
Dim rowCount As Integer
Dim cellCount As Integer
Dim rowRange As Range
Dim cellRange As Range
Dim sheet As Worksheet
Dim xpathToExtractRow As String
xpathToExtractRow = "/feed/row"
Set dom = New DOMDocument60
dom.load ("c:\test\source.xml")
Set sheet = ActiveSheet
Set nodeList = dom.SelectNodes(xpathToExtractRow)
rowCount = 0
For Each nodeRow In nodeList
rowCount = rowCount + 1
cellCount = 0
For Each nodeCell In nodeRow.ChildNodes
cellCount = cellCount + 1
Set cellRange = sheet.Cells(rowCount, cellCount)
cellRange.Value = nodeCell.Text
Next nodeCell
Next nodeRow
End Sub
Sample Input XML:
<?xml version="1.0" encoding="utf-8"?>
<feed>
<row>
<firstname>joe</firstname>
<lastname>smith</lastname>
<country>jamaica</country>
</row>
<row>
<firstname>bill</firstname>
<lastname>coots</lastname>
<country>uk</country>
</row>
</feed>