Fist

Q: XML to CSV

What is the best way to convert a XML file into a tab delimited csv file?

 

When i get an XML file, it should be converted into an CSV file to use it in adobe applications.

 

Anyone any good idea's to get started on this on?

 

regards

 

F

MacBook Pro, Mac OS X (10.7.5)

Posted on Aug 18, 2015 12:02 AM

Close

Q: XML to CSV

  • All replies
  • Helpful answers

  • by Tony T1,

    Tony T1 Tony T1 Aug 18, 2015 4:39 AM in response to Fist
    Level 6 (9,249 points)
    Mac OS X
    Aug 18, 2015 4:39 AM in response to Fist

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

  • by VikingOSX,

    VikingOSX VikingOSX Aug 18, 2015 9:25 AM in response to Fist
    Level 7 (20,819 points)
    Mac OS X
    Aug 18, 2015 9:25 AM in response to Fist

    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.

  • by Fist,

    Fist Fist Aug 18, 2015 10:07 PM in response to VikingOSX
    Level 1 (5 points)
    Mac OS X
    Aug 18, 2015 10:07 PM in response to VikingOSX

    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

  • by VikingOSX,Helpful

    VikingOSX VikingOSX Aug 19, 2015 3:56 AM in response to Fist
    Level 7 (20,819 points)
    Mac OS X
    Aug 19, 2015 3:56 AM in response to Fist

    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>
    
    
    
  • by Fist,

    Fist Fist Aug 19, 2015 5:10 AM in response to VikingOSX
    Level 1 (5 points)
    Mac OS X
    Aug 19, 2015 5:10 AM in response to VikingOSX

    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

  • by VikingOSX,

    VikingOSX VikingOSX Aug 19, 2015 5:16 AM in response to Fist
    Level 7 (20,819 points)
    Mac OS X
    Aug 19, 2015 5:16 AM in response to Fist

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

     

    Screen Shot 2015-08-19 at 8.15.37 AM.png

  • by Fist,

    Fist Fist Aug 19, 2015 5:21 AM in response to VikingOSX
    Level 1 (5 points)
    Mac OS X
    Aug 19, 2015 5:21 AM in response to VikingOSX

    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.

  • by Hiroto,Helpful

    Hiroto Hiroto Aug 20, 2015 5:42 AM in response to Fist
    Level 5 (7,286 points)
    Aug 20, 2015 5:42 AM in response to Fist

    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"
  • by Fist,

    Fist Fist Aug 20, 2015 10:20 PM in response to Hiroto
    Level 1 (5 points)
    Mac OS X
    Aug 20, 2015 10:20 PM in response to Hiroto

    Thanks for all the effort. I was in contact with StefanK and he helped me out as well.

     

    Regards

     

    F

  • by brsm1990,

    brsm1990 brsm1990 Jun 23, 2016 2:43 AM in response to Hiroto
    Level 1 (44 points)
    Mac OS X
    Jun 23, 2016 2:43 AM in response to Hiroto

    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.

  • by Hiroto,

    Hiroto Hiroto Jun 23, 2016 5:10 AM in response to brsm1990
    Level 5 (7,286 points)
    Jun 23, 2016 5:10 AM in response to brsm1990

    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