XML to CSV with nested and definite elements in Perl

Question

I need to convert my xml file to csv Format. But I in csv file I need not all Information fron the XML, just 2 elements (IP Address and id from device).

 #!/usr/bin/perl
 use strict;
 use warnings;
 use Data::Dumper;
 use XML::Simple;

  #Elements, that I want see in my csv
  my @Fields = qw{id, ipAddress};

  open(my $out, '>', 'output.csv') or die "Output: $!\n";
  print $out join(',', @Fields) . "\n";

  my $xml = XMLin('input.xml', ForceArray => ['entity']);
  foreach my $entity (@{$xml->{entity}}) {
  no warnings;
  print $out join(',', map{$_->{content}} @{$entity}{@Fields}) . "\n";

Input.xml

      <?xml version="1.0" ?>
          <queryResponse last="41" first="0" count="42" type="Devices" responseType="listEntityInstances" requestUrl="https://hostname/webacs/api/v1/data/Devices?.full=true" rootUrl="https://hostname/webacs/api/v1/data">
             <entity dtoType="devicesDTO" type="Devices" url="https://hostname/webacs/api/v1/data/Devices/20">
               <devicesDTO displayName="20" id="20">
               <clearedAlarms>1</clearedAlarms>
               <collectionDetail>&lt;status&gt;&lt;general code="SUCCESS"/&gt;&lt;/status&gt;</collectionDetail>
               <collectionTime>2017-03-30T09:47:07.606+02:00</collectionTime>
               <creationTime>2016-02-29T17:32:13.116+01:00</creationTime>
               <ipAddress>1.1.1.1</ipAddress>
               <location> </location>
               <majorAlarms>0</majorAlarms>
                <softwareType>IOS</softwareType>
                <softwareVersion>12.2(55)SE9</softwareVersion>
                <warningAlarms>0</warningAlarms>
             </devicesDTO>
          </entity>
          <entity dtoType="devicesDTO" type="Devices" url="https://hostname/webacs/api/v1/data/Devices/21">
               <devicesDTO displayName="21" id="21">
               <clearedAlarms>1</clearedAlarms>
               <collectionDetail>&lt;status&gt;&lt;general code="SUCCESS"/&gt;&lt;/status&gt;</collectionDetail>
               <collectionTime>2017-03-30T09:47:07.606+02:00</collectionTime>
               <creationTime>2016-02-29T17:32:13.116+01:00</creationTime>
               <ipAddress>2.2.2.2</ipAddress>
               <location> </location>
               <majorAlarms>0</majorAlarms>
                <softwareType>IOS</softwareType>
                <softwareVersion>12.2(55)SE9</softwareVersion>
                <warningAlarms>0</warningAlarms>
             </devicesDTO>
          </entity>
        </queryResponse>

As result I have

 id, ipAddress
 ,
 ,

I am not sure that:

     my $xml = XMLin('input.xml', ForceArray => ['entity']);
     foreach my $entity (@{$xml->{entity}})

is right in my case. Should I do it with tag entity?

You should not use XML::Simple in new code. It's not simple, often wrong and the documentation suggests to not use it. — simbabque
– simbabque, Commented Jan 12, 2018 at 15:53

simbabque · Accepted Answer · 2018-01-12 16:04:02Z

1

When dealing with XML::Simple, it's always a good idea to use Data::Dumper first to look at the data structure.

foreach my $entity ( @{ $xml->{entity} } ) {
    print Dumper $entity;

This will show you:

$VAR1 = {
          'url' => 'https://hostname/webacs/api/v1/data/Devices/20',
          'type' => 'Devices',
          'dtoType' => 'devicesDTO',
          'devicesDTO' => {
                          'displayName' => '20',
                          'creationTime' => '2016-02-29T17:32:13.116+01:00',
                          'warningAlarms' => '0',
                          'ipAddress' => '1.1.1.1',
                          'clearedAlarms' => '1',
                          'majorAlarms' => '0',
                          'collectionDetail' => '<status><general code="SUCCESS"/></status>',
                          'location' => {},
                          'collectionTime' => '2017-03-30T09:47:07.606+02:00',
                          'softwareType' => 'IOS',
                          'id' => '20',
                          'softwareVersion' => '12.2(55)SE9'
                        }
        };

So it's quite clear that your @Fields are in the wrong place. Those keys are not in $entity directly, but rather in $entity->{devicesDTO}.

There is also no need to use $_->{content} with the map. In fact, there are no content keys in that data structure.

foreach my $entity ( @{ $xml->{entity} } ) {
    print join( ',', @{ $entity->{devicesDTO} }{@Fields} ) . "\n";
}

This will produce the output

id,ipAddress
20,1.1.1.1
21,2.2.2.2

Note that you have a stray comma in your qw{} that you don't need. The idea of qw is that you do not need to use commas. You should also decide if you want your variables to have lower case letters or not. Mixing that is bad style, and the convention in Perl is to use snake case.

my @fields = qw{id ipAddress};

answered Jan 12, 2018 at 16:04

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

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

3 Comments

StayCalm Over a year ago

Thank you a lot for advise! It works! But my file output.csv is empty. Should I write open(my $out, '>', 'output.csv') or die "Output: $!\n"; print $out join(',', @Fields) . "\n"; at the end of script?

simbabque Over a year ago

@StayCalm you didn't show your full code, so I don't know. If you have a follow up question, please ask it as new question.

StayCalm Over a year ago

This peace of code is from that one, I've posted :)

Collectives™ on Stack Overflow

XML to CSV with nested and definite elements in Perl

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related