how should I go about converting xml into csv

Question

I am trying to take some xml code, a sample being below:

  <time_report>

 <project_ID>4</project_ID>

 <project_status>close</project_status>

 <client_ID>6001</client_ID>

     <time_record>

            <project_start_time>15:02:33</project_start_time>

            <project_end_time>15:07:44</project_end_time>

            <project_total_time>PT00H05M11S</project_total_time>

     </time_record>

 <employee_ID>10001</employee_ID>

 <employee_name>Mary Beth</employee_name>

 <date_created>2009-08-25</date_created>

</time_report>

and then output it so it is in the following format:

project_id, project_status, client_id, project_start_time, project_end_time,  project_total_time, employee_ID, employee_name, date_created

4, close, 6001, 15:02:33, 15:07:44, PT00H05M11S, 10001, Mary Beth, 2009-08-25

I have been trying to use xmllint to do this, but have unfortunately not been able to make any progress, having said that I was wondering if anyone would have a suggestion as to what I should do? I would be doing this in a bash/shell environment. any help would be much appreciated, thanks!

also forgot to mention that I can get the correct results if I open the xml file up in excel and then save as csv, just looking for a way to do it in linux

    project_ID,project_status,client_ID,project_start_time,project_end_time,project_total_time,employee_ID,employee_name,date_created
4,close,6001,15:02:33,15:07:44,PT00H05M11S,10001,Mary Beth,8/25/2009
5,open,6003,12:00:00,12:45:00,PT00H45M00S,10003,Michelle,9/11/2009
2,close,6002,10:00:00,10:30:00,PT00H30M00S,10002,Joe,8/25/2009
2,open,6004,12:00:00,3:27:05,PT03H23M05S,10004,Mike,8/13/2009

Duplicate: https://stackoverflow.com/questions/14368347/convert-xml-file-to-csv-in-shell-script — Vanuan, Oct 26 '17 at 00:26
@Vanuan the post you linked is a duplicate of mine as the linked post was posted after I had already posted mine. — lacrosse1991, Oct 27 '17 at 16:23

score 4 · Accepted Answer · answered Nov 09 '12 at 23:40

4

xmlstarlet is a very powerful command line tool which lets you query XML or run XSLT translations. There's some XSLT XML->CSV examples floating around but the following one-liner gives you what you need:

xmlstarlet sel -B -t -m "//time_reports/time_report" -n -m "*" -v . -o , input.xml

The only problem was that I needed to wrap <time_report> with a root level tag called <time_reports>

answered Nov 09 '12 at 23:40

Alastair McCormack

26,573
8
77
100

hello, thanks! I've gone ahead and given this a try, but unfortunately when I execute the command, nothing is outputted. this is the file itself http://i.minus.com/ixkoCbKj30tXq.png and this is what happens when I execute xmlstarlet http://i.minus.com/ieYnWqj7MLLWY.png, not sure if i am just making a dumb mistake or something of the sort though, thanks again for your help! – lacrosse1991 Nov 11 '12 at 19:17
oh, i was actually able to get it to work properly, thanks for your help! – lacrosse1991 Nov 12 '12 at 03:14

score 3 · Answer 2 · answered Nov 09 '12 at 23:43

To transform your XML to CSV (with e.g. xsltproc) you can use an XSL stylesheet like this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" />
    <xsl:template match="/">
        <xsl:for-each select="//time_report[position()=1]/*">
            <xsl:if test="not(position()=1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            <xsl:value-of select="name()" />
        </xsl:for-each>
        <xsl:text>&#13;</xsl:text>
        <xsl:for-each select="//time_report">
            <xsl:for-each select="./*">
                <xsl:if test="not(position()=1)">
                    <xsl:text>,</xsl:text>
                </xsl:if>
                <xsl:value-of select="normalize-space(.)" />
            </xsl:for-each>
            <xsl:text>&#13;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

BeniBela · Answer 3 · 2012-11-10T00:14:24.730

2

You could also use my Xidel: (assuming you have no empty fields in your xml)

 xidel /tmp/test.xml -e '//time_report/string-join(.//text()[normalize-space(.)], ", ")'

standard XPath 2, no need to remember the names of different command line parameters...

or without that assumption:

 xidel /tmp/test.xml -e '//time_report/string-join(.//*[not(*)], ", ")'

edited Nov 10 '12 at 00:14

answered Nov 09 '12 at 23:59

BeniBela

16,412
4
45
52

how should I go about converting xml into csv

3 Answers3

Linked