0

Need some quick help as I am not able to get the desired output:

2020-05-19 19:03:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><TYPE>RCTRFREQ</TYPE><DN1>99847</DN1><AMOUNT>49</AMOUNT></COMMAND> - 
2020-05-19 19:05:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><PE>RC</PE><DN1>92847</DN1><AMOUNT>19</AMOUNT></COMMAND> - 
2020-05-19 19:05:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><DN1>947</DN1><TYPE>RC</TYPE><AMOUNT>29</AMOUNT></COMMAND> - 

Required Output:

Time,DN1,AMOUNT
2020-05-19 19:03:07,99847,49
2020-05-19 19:05:07,92847,19
2020-05-19 19:05:07,947,29 
2
1
$ awk '
    BEGIN { FS=",|</?(DN1|AMOUNT)>"; OFS=","; print "Time", "DN1", "AMOUNT" }
    { print $1, $3, $(NF-1) }
' file
Time,DN1,AMOUNT
2020-05-19 19:03:07,99847,49
2020-05-19 19:05:07,92847,19
2020-05-19 19:05:07,947,29

The above tells awk to separate the input into fields separated by strings that match the regexp being stored in FS (i.e. ,, <DN1>, </DN1>, <AMOUNT>, and </AMOUNT>), and then print the 1st, 3rd, and 2nd-last such fields.

Here's how the above splits each record into fields:

$ awk -F',|</?(DN1|AMOUNT)>' '{print "----" ORS $0; for (i=1;i<=NF;i++) print NR, i "/" NF, $i}' file
----
2020-05-19 19:03:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><TYPE>RCTRFREQ</TYPE><DN1>99847</DN1><AMOUNT>49</AMOUNT></COMMAND> -
1 1/6 2020-05-19 19:03:07
1 2/6 135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><TYPE>RCTRFREQ</TYPE>
1 3/6 99847
1 4/6
1 5/6 49
1 6/6 </COMMAND> -
----
2020-05-19 19:05:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><PE>RC</PE><DN1>92847</DN1><AMOUNT>19</AMOUNT></COMMAND> -
2 1/6 2020-05-19 19:05:07
2 2/6 135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><PE>RC</PE>
2 3/6 92847
2 4/6
2 5/6 19
2 6/6 </COMMAND> -
----
2020-05-19 19:05:07,135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND><DN1>947</DN1><TYPE>RC</TYPE><AMOUNT>29</AMOUNT></COMMAND> -
3 1/6 2020-05-19 19:05:07
3 2/6 135 INFO [Container : 8504] [HttpUtil.java]requestXML: <?xml version="1.0"? ><COMMAND>
3 3/6 947
3 4/6 <TYPE>RC</TYPE>
3 5/6 29
3 6/6 </COMMAND> -
11
  • 1
    Thanks Ed Morton!!!!! it worked.
    – amit10504
    May 22 '20 at 5:49
  • 1
    +1 Interesting way to specify field separators. Didn't know that it was possible.
    – Cbhihe
    May 22 '20 at 21:06
  • 1
    Sure. I just had never seen ERE and boolean operators in the specification of FS. I guess I missed out on the section of man awk, that explicitly says that, when it comes to the string value of FS, "ERE tokens or arbitrary expressions, shall be interpreted in the same manner as the rhs of the ~ or the !~ operators."
    – Cbhihe
    May 23 '20 at 8:49
  • 1
    I updated my answer to explain those points and show how the record is decomposed into fields. If you want a more flexible, extensible solution that'll work for any tags in any order of input and can print their values in any order in the output then ask a new question and make sure you state that as the goal and include the tags in mixed positions in the sample input.
    – Ed Morton
    May 24 '20 at 13:53
  • 1
    @EDMorton Thanks a lot!!! Excellent explanation . Helped me to fetch any number of tag I want. Thankyou.
    – amit10504
    May 24 '20 at 14:04
0

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
3
  • Thanks !!! can you please explain the code. Also just to highlight tags are not in any specific place.
    – amit10504
    May 21 '20 at 16:18
  • my assumption, DN1 is before AMOUNT as in the sample. if not this code cannot be useful, so could you please clarify?
    – binarysta
    May 21 '20 at 17:44
  • 1
    You never need sed when you're using awk. awk '{print $1,$3,$5}' file | sed 's/<\|>\|\///g' = awk '{$0=$1 OFS $3 OFS $5; gsub("[<>/]",""); print}' file.
    – Ed Morton
    May 21 '20 at 21:17
0

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)

4
  • 2
    Hi Mateusz; can you explain why "this approach gives you more flexibility" ? Cheers.
    – Cbhihe
    May 22 '20 at 21:04
  • Hi cbhihe, maybe "flexible" is just a wrong word I used. It is much more readable for me to add some items to be replaced here in python script than using regular expressions. May 28 '20 at 7:43
  • Don't get me wrong: your answer is functional and I like Python very much. However, at worst, encapsulating the accepted answer by @EdMorton in a 5 line shell-script with script arguments as variables for the awk logic, is far simpler than calling the heavy cavalry with 6 functions definitions and 2 module imports in 30 some lines. Am I being weird ?
    – Cbhihe
    May 28 '20 at 9:01
  • 1
    Thank you for your opinion, and I agree with you, but it all depends on the needs, so I presented different approach, finally it's up to the guy who asks the quesitons which solution is more comprehensive for him. May 28 '20 at 18:22
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.