XML to CSV

There are a few converters, google: os x xml to csv

From what I have seen, XML data files vary in their organization, and you find yourself tweaking some code that tailors a converter to your particular data file. It also means that with a goal of generating a CSV file, you will need to quote your captured content to allow for possible embedded commas in the data.

StefanK over at MacScripter posted an AppleScript XML to CSV solution. Copy and paste his second (04:54:35 am) example code (that writes a CSV data file) into your AppleScrpt editor, compile, and then save as a Text (xml2csv.applescript) document. Run it, and it will prompt you for your XML data file. When it is done parsing that, it will then prompt you for a filename to save too. You can also save it as an App on your Desktop. The output format is your preferred result.

There are Python and Ruby solutions that are much shorter in code, and easier to understand — but require non-standard library and gem installations — something I avoid in posted solutions, so I have omitted these here.

Another solution is to use an XSLT stylesheet with the OS X xsltproc command line XSLT processor. If we use the data format that you provided, and place it in an asc.xml file, and apply the test.xsl XSLT stylesheet — the following syntax will provide the desired output:

xsltproc test.xsl asc.xml > asc.csv

Result:

OrderID,CreationDate

123,27/06/2014

146,30/06/2014

The idea (and code) for the following XSLT stylesheet came from here.

test.xsl

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="iso-8859-1"/>


<!-- The first code block will dynamically obtain the tag names and
     use them in the header CSV row. The second code block captures the
     tag data, and outputs it in newline "&#xA;" rows. If the destination
     of the output is on Windows, then the line termination should be
     "&#xD;&#xA;"


     If it is desired to place quotes around the header, and data CSV
     items, then uncomment the <xsl:text>"</xsl:text> blocks.
-->


<xsl:template match="/*">
<xsl:for-each select="*[1]/*">
<!-- <xsl:text>"</xsl:text> -->
<xsl:value-of select="name()"/>
<!-- <xsl:text>"</xsl:text> -->
<xsl:if test="position() != last()">,</xsl:if>
<xsl:if test="position() = last()">
<xsl:text>&#xA;</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:apply-templates/>
</xsl:template>


<xsl:strip-space elements="*" />


<xsl:template match="/*/child::*">
<xsl:for-each select="child::*">
<xsl:if test="position() != last()"><!--<xsl:text>"</xsl:text>-->
<xsl:value-of select="normalize-space(.)"/><!--<xsl:text>"</xsl:text>-->,</xsl:if>
<xsl:if test="position()  = last()"><!--<xsl:text>"</xsl:text>-->
<xsl:value-of select="normalize-space(.)"/><!--<xsl:text>"</xsl:text>-->
<xsl:text>&#xA;</xsl:text>
</xsl:if>
</xsl:for-each>
</xsl:template>


</xsl:stylesheet>

I just ran the unmodified test.xsl stylesheet against your latest xml data, and here is the output:

Hello

You might try something like the following AppleScript script which is a simple wrapper of Perl script. It will extract tag name and text content of every leaf node in XML and print the tag names as field names in header row and text contents as field values in a single body row in output CSV file.

Good luck,

H

set infile to (choose file of type {"xml"} with prompt "Choose input xml file")'s POSIX path set outfile to (choose file name default name "out.csv" with prompt "Specify output csv file name and location")'s POSIX path do shell script "/usr/bin/perl -CSA -w <<'EOF' - " & infile's quoted form & " > " & outfile's quoted form & " use strict; use XML::LibXML; my $parser = XML::LibXML->new(); my $doc = $parser->parse_file(shift); my $leaves = $doc->find( q(//*[not(*)]) ); my (@keys, @values); map { push @keys, $_->tagName; push @values, $_->textContent } @{$leaves}; { # CSV spec # - field separator = comma # - record separator = LF # - every field is quoted local $, = qq(,); local $\\ = qq(\\n); print map { s/\"/\"\"/g; q(\").$_.q(\") } @keys; print map { s/\"/\"\"/g; q(\").$_.q(\") } @values; } EOF"

This doesn't work for me.

line 1: I get:syntax error near unexpected token '('

line 1: 'set infile to (choose file of type {"xml"} with prompt " Chose input xml file")'s POSIX path'

How can I fix it? Is there a better solution? This seems like such a simple thing to do but I can't find any software to do it, only "online converters" but I don't want to upload my data.

Hello

The script I posted is an AppleScript script which is supposed to be compiled and run in (Apple)Script Editor. Your errors indicate you run it in shell.

Should you find the script does not do what you want when run properly in Script Editor, please start new thread for you own question including detailed descriptions of your input data and expected output data. Of course, you'd redact them.

Regards,

H