Creating bash script to parse xml file to csv

Question

I'm trying to create a bash script to parse an xml file and save it to a csv file.

For example:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <List>
    <Job id="1" name="John/>
    <Job id="2" name="Zack"/>
    <Job id="3" name="Bob"/>
</List>

I would like the script to save information into a csv file as such:

John | 1
Zack | 2
Bob  | 3

The name and id will be in a different cell.

Is there any way I can do this?

Might have just edited the old question (stackoverflow.com/q/21495533/3076724) rather than posting a new one, but you should definitely at least link to it when posting similar questions. — Reinstate Monica Please
– Reinstate Monica Please, Commented Feb 2, 2014 at 6:43

Community · Accepted Answer · 2017-05-23 11:56:06Z

5

You've posted a query similar to your pervious one. I'd again suggest using a XML parser. You could say:

xmlstarlet sel -t -m //List/Job -v @name -o "|" -v @id -n file.xml

It would return

John|1
Zack|2
Bob|3

for your sample data.

Pipe the output to sed: sed "s/|/\t| /" if you want it to appear as in your example.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 2, 2014 at 6:57

devnull

124k33 gold badges247 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Reinstate Monica Please · Accepted Answer · 2014-02-02 06:40:22Z

2

Try something like this

#!/bin/bash
while read -r line; do
  [[ $line =~ "name=\""(.*)"\"" ]] && name="${BASH_REMATCH[1]}" && [[ $line =~ "Job id=\""([^\"]+) ]] &&  echo "$name | ${BASH_REMATCH[1]}"
done < file

The line with John is malformed. With it fixed, example output

John | 1
Zack | 2
Bob | 3

answered Feb 2, 2014 at 6:40

Reinstate Monica Please

11.7k3 gold badges29 silver badges49 bronze badges

3 Comments

BMW Over a year ago

in this instance name="John/>, there is no double quota after John, so recommend to replace [[ $line =~ "name=\""(.*)"\"" ]] to [[ $line =~ "name=\""([^\"|/]*) ]]

Reinstate Monica Please Over a year ago

@BMW Thanks. I assumed it shouldn't be malformed xml, but if it is could do that or something like ([A-Za-z]*)

Dominik Over a year ago

dude, can u elaborate on that short script? I am quite confused. :) nevertheless its looking crazy good.

Vanuan · Accepted Answer · 2019-08-30 08:56:46Z

2

Extending xmlstarlet approach:

Given this xml file as input:

<DATA>
  <RECORD>
    <NAME>John</NAME>
    <SURNAME>Smith</SURNAME>
    <CONTACTS>
      "Smith" LTD,
      London, Mtg Str, 12,
      UK
    </CONTACTS>
  </RECORD>
</DATA>

And this script:

xmlstarlet sel -e utf-8 -t \
  -o "NAME, SURNAME, CONTACTS" -n \
  -m //DATA/RECORD \
  -o "\"" \
  -v $"str:replace(normalize-space(NAME), '\"', '\"\"')" -o "\",\"" \
  -v $"str:replace(normalize-space(SURNAME),      '\"', '\"\"')" -o "\",\"" \
  -v $"str:replace(normalize-space(CONTACTS), '\"', '\"\"')" -o "\",\"" \
  -o "\"" \
  -n file.xml

You'll have the following output:

NAME, SURNAME, CONTACTS
"John", "Smith", """Smith"" LTD, London, Mtg Str, 12, UK"

edited Aug 30, 2019 at 8:56

answered Oct 26, 2017 at 0:24

Vanuan

33.8k11 gold badges103 silver badges105 bronze badges

2 Comments

Diego1974 Over a year ago

This is a good solution, and elegant. Just I got: compilation error: element with-param XSLT-with-param: Failed to compile select expression 'str:replace' because of unclosed parenthesis in normalize-space call; should read "str:replace(normalize-space(NAME) , '\"', '\"\"')"

Neek Over a year ago

Thanks for this. Anyone else extracting URLs from XML may find the & isn't escaped. Fix this by adding -T after the sel command, e.g. xmlstarlet sel -T -e utf-8...... (see stackoverflow.com/questions/46255304/…)

BMW · Accepted Answer · 2014-02-03 05:29:48Z

1

Using sed

sed -nr 's/.*id=\"([0-9]*)\"[^\"]*\"(\w*).*/\2 | \1/p' file

Additional, base on BroSlow's cript, I merge the options.

#!/bin/bash

while read -r line; do
  [[ $line =~ id=\"([0-9]+).*name=\"([^\"|/]*) ]] && echo "${BASH_REMATCH[2]} | ${BASH_REMATCH[1]}"
done < file

edited Feb 3, 2014 at 5:29

answered Feb 2, 2014 at 10:30

BMW

45.6k13 gold badges105 silver badges123 bronze badges

Collectives™ on Stack Overflow

Creating bash script to parse xml file to csv

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related