-
All replies
-
Helpful answers
-
Aug 18, 2015 4:39 AM in response to Fistby Tony T1,There are a few converters, google: os x xml to csv
-
Aug 18, 2015 9:25 AM in response to Fistby VikingOSX,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.
-
Aug 18, 2015 10:07 PM in response to VikingOSXby Fist,Thanks Tony for the google findings.
VikingOSX, it seems to me it cannot be so hard to put the tag on the first line which and the value under need it.
e.g.
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Job>
<Order>
<OrderID>123</OrderID>
<CreationDate>27/06/2014</CreationDate>
should become (as txt or CSV)
OrderID,CreationDate
123,27/06/2014
-
Aug 19, 2015 3:56 AM in response to Fistby VikingOSX,★HelpfulStefanK 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 "
" rows. If the destination of the output is on Windows, then the line termination should be "
" 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>
</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>
</xsl:text> </xsl:if> </xsl:for-each> </xsl:template> </xsl:stylesheet>
-
Aug 19, 2015 5:10 AM in response to VikingOSXby Fist,Wow, a lot of info. I'm not familiar with XSLT, however the AS from StefanK could do the trick.
With a little tweaking it could work. The only thing is that not all the tags (XML elements) are pulled out from the XML.
Here is the xml.
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Job>
<Order>
<OrderID>123</OrderID>
<CreationDate>27/06/2014</CreationDate>
<Name>Your name</Name>
<CustumerName>his name</CustumerName>
<TotalQuantity>400</TotalQuantity>
</Order>
<ArticleComponent>
<ArticleComponentID>DDDD</ArticleComponentID>
<ComponentType>xxx</ComponentType>
</ArticleComponent>
</Job>
this is the result:
OrderID,CreationDate,Name,CustumerName,TotalQuantity
123,27/06/2014,Your name,his name,400
DDDD,xxx
-
-
Aug 19, 2015 5:21 AM in response to VikingOSXby Fist,Exactly. I'm missing the elements ComponentType and ArticleComponentID which are in the <Articlecomponent>section.
So the scripts only look between <Order> and </Order>
I was modifying the Applescript because I have a little experience with that.
-
Aug 20, 2015 5:42 AM in response to Fistby Hiroto,★HelpfulHello
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" -
Aug 20, 2015 10:20 PM in response to Hirotoby Fist,Thanks for all the effort. I was in contact with StefanK and he helped me out as well.
Regards
F
-
Jun 23, 2016 2:43 AM in response to Hirotoby brsm1990,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.
-
Jun 23, 2016 5:10 AM in response to brsm1990by Hiroto,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
