lines to columns with awk

Question

I have to following sample output:

<HARDWARE>
    <NAME>WIN1</NAME>
    <OS>Windows 7</OS>
    <IP>1.2.3.4</IP>
    <DOMAIN>contoso.com</DOMAIN>
</HARDWARE>
<HARDWARE>
    <NAME>WIN2</NAME>
    <OS>Windows 8</OS>
    <IP>10.20.30.40</IP>
    <DOMAIN>contoso.com</DOMAIN>
</HARDWARE>

What is the best way to parse it so it will look like:

WIN1    Windows 7    1.2.3.4     contoso.com
WIN2    Windows 8    10.20.30.40 contoso.com

Looking for a solution to use standard tools like awk, sed etc

Answer 1

With a slight modification to your XML, wrap all your XML in a parent <DATA> tag¹, or another one of your choosing, file called data.xml:

<DATA>
<HARDWARE>
    <NAME>WIN1</NAME>
    <OS>Windows 7</OS>
    <IP>1.2.3.4</IP>
    <DOMAIN>contoso.com</DOMAIN>
</HARDWARE>
<HARDWARE>
    <NAME>WIN2</NAME>
    <OS>Windows 8</OS>
    <IP>10.20.30.40</IP>
    <DOMAIN>contoso.com</DOMAIN>
</HARDWARE>
</DATA>

Using xmlstarlet + column

 xmlstarlet sel -T -t -m /DATA/HARDWARE -v "concat(NAME,' ',OS,' ',IP,' ',DOMAIN)" -n data.xml | column -t 

gives:

WIN1  Windows  7  1.2.3.4      contoso.com
WIN2  Windows  8  10.20.30.40  contoso.com

Edit:

Based on Peter.O's great catch in the comments and his answer below, let's send pipe delimited² output to column -ts$'|', so something like:

xmlstarlet sel --indent-tab -T -t -m /DATA/HARDWARE -v "concat(NAME,'|',OS,'|',IP,'|',DOMAIN)" -n data.xml | column -ts$'|'

Now, the fields line up nicely even if they have spaces:

WIN1              Windows 7  1.2.3.4 release 5  contoso.com
Really long OS X  Windows 8  10.20.30.40        contoso.com

_{1. Or use { echo '<DATA>'; cat file_name; echo '</DATA>'; } | xmlstarlet ... as Peter.O notes in the comment below}

^{2. Using space as the delimiter does not align the columns properly}

Answer 2

With your example and GNU sed:

sed -n 's/<[^>]*>//g;s/^ *//g;/./p' file | paste -d ";" - - - - | column -t -s ";"

Output:

WIN1  Windows 7  1.2.3.4      contoso.com
WIN2  Windows 8  10.20.30.40  contoso.com

I assume that your file does not contain a ;. If you need a CSV remove | column -t -s ";".

Answer 3

The following awk script (plus column for output tabulation) will hande any sequence of placement of the sub-tags, and any whitespace separation of the tags - ie. it will handle the OP's sample input format, as well as the following sample which has no whitespace and differently ordered sub-tags:

    <HARDWARE><OS>Windows 7</OS><IP>1.2.3.4</IP><DOMAIN>contoso.com</DOMAIN><NAME>WIN1</NAME></HARDWARE><HARDWARE><NAME>WIN2</NAME><OS>Windows 8</OS><DOMAIN>contoso.com</DOMAIN><IP>10.20.30.40</IP></HARDWARE>  

---

awk 'BEGIN{ RS="[[:space:]]*</?HARDWARE>[[:space:]]*"
            FS="[[:space:]]*<|</[^<>/]+>[[:space:]]*"
            tn=split( "NAME OS IP DOMAIN", tag_order, " " ) 
     } 
     $0 { delete tag
          for( i=1;i<=NF;i++ ) if($i) { n=index($i,">"); tag[substr($i,1,n-1)]=substr($i,n+1)  } 
          for( i=1;i<=tn;i++ ) printf "%s\t", tag[tag_order[i]]; print ""
     }' file | column -ts$'\t'

output:

WIN1  Windows 7  1.2.3.4      contoso.com
WIN2  Windows 8  10.20.30.40  contoso.com

Answer 4

Please don't use awk sed etc. They cannot handle XML properly. XML does a bunch of stuff like having whitespace, linefeeds, unary tags etc. that means regular expressions aren't very robust - they break messily, following a perfectly valid change to XML down the line.

The way to handle XML is with a parser. xmlstarlet is one commonly used on Linux. Because I haven't seen it suggested yet- I'd use perl. E.g.:

#!/usr/bin/perl

use strict;
use warnings;

use XML::Twig;

my $twig = XML::Twig -> parsefile ('your_xml_file.xml'); 
foreach my $HW ( $twig -> findnodes ( '//HARDWARE' ) ) {
    print join ( "\t", map { $_ -> text } $HW -> children ),"\n";
}

• Parse the XML
• iterate the HARDWARE elements.
• Extract the text from the children
• print that.

You could extend it a little to allow you to handle e.g. different field sets/ordering:

#!/usr/bin/perl

use strict;
use warnings;

use XML::Twig;

my @fields_to_show = qw ( OS NAME ); 

my $twig = XML::Twig -> parsefile ( 'your_filename.xml' ); 
foreach my $HW ( $twig -> findnodes ( '//HARDWARE' ) ) {
    my %fields =  map { $_ -> tag => $_ -> text } $HW -> children;
    print join ("\t", @fields{@fields_to_show}),"\n"; 
}

It generates a hash (associative array) called %fields that looks like (for each element):

$VAR1 = {
          'OS' => 'Windows 7',
          'NAME' => 'WIN1',
          'DOMAIN' => 'contoso.com',
          'IP' => '1.2.3.4'
        };

And then we use @fields_to_show to specify which to display and in which order.

So this will thus print:

Windows 7   WIN1
Windows 8   WIN2

NB: I also has to 'fix' your XML, because without a single root tag it's invalid. Other answers have mentioned this. The XML spec is quite strict - broken XML should be rejected. So it's actually quite bad form to "fix" XML and normally I'd suggest hitting whoever generated it around the head with a copy of the XML spec.

Answer 5

with awk - arbitrarily set each column to be 15 characters long, left-aligned and filled with spaces:

awk ' BEGIN { FS = "<[A-Za-z/]+>" } { if ( NR % 6 == 0 ) { printf"\n" } else if ( $2 != "" ) { printf"%-15s", $2 } }' file

Or as in the other answers in combination with column

awk ' BEGIN { FS = "<[A-Za-z/]+>" } { if ( NR % 6 == 0 ) { printf"\n" } else if ( $2 != "" ) { printf"%s ", $2 } }' file | column -t