XML Data to CSV

Well-structured lines can be parsed with sed:

sed -En 's|^([^,]+),.*<DN1>(.+)</DN1>.*<AMOUNT>(.+)</AMOUNT>.*|\1,\2,\3|p' file

• -E enables extended regular expressions
• -n suppresses automatic printing of read lines
• s|...|___| searches for line matching the ... part and replaces them by ___
• ^([^,]+), matches the beginning until the first , and puts it into \1
• <DN1>(.+)</DN1> matches theDN1element and puts its content into\2`
• <AMOUNT>(.+)</AMOUNT> does the same for AMOUNT
• \1,\2,\3 substitutes the result
• p makes sure any matching line gets printed on stdout

can be done with following awk and sed command:

awk 'BEGIN { FS="AMOUNT|,|DN1" ;OFS=","}; {print $1,$3,$5}' xmlfile | sed 's/<\|>\|\///g' > output.csv

This case is a little odd to be processed by oneliner and I would stick with processing XML as an XML object not as a string thus I took Python. It parses date as string and find an XML string to be used to load. This approach gives you more flexibility if you would like to append the script in the future for more nodes from XML or other fields in the input line.

The first argument passed to the script is your input file.

#!/usr/bin/python

import sys
import xml.etree.ElementTree as ET


def get_lines():

    file_name = str(sys.argv[1])
    f = open(file_name, 'r')

    return f.readlines()


def print_header():

    print("Time,DN1,AMOUNT")


def process_xml(xml):

    doc = ET.ElementTree(ET.fromstring(xml))
    elements = [
            doc.find("DN1").text,
            doc.find("AMOUNT").text
            ]

    return (",").join(elements)


def process_date(line):

    date = line.split()[:2]
    date = " ".join(date).split(",")[0]

    return date


def process_line(line):

    fields = []
    date = process_date(line)
    xml = process_xml(line.split("<?xml version=\"1.0\"? >")[1][:-3])

    fields.append(date)
    fields.append(xml)

    return (",").join(fields)


def process_all(lines):

    print_header()
    for line in lines:
        print(process_line(line))


if __name__ == "__main__":

    lines = get_lines()
    process_all(lines)