Sporti69 has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I am writing a script to parse xml to excel with the Win32::OLE module.
Everything works, but ... when my XML hasn't got unique elements inside a main node, it fails !
The error I get is a failed hash reference.
So This does not fail:
----------------------
 <node1>

     <node1_A> Content </node1_A>

     <node1_B> Content </node1_B>

  </node1>

[download]
But this fails : ------------------- 
<node1>
     <node1_A> Content </node1_A>
     <node1_A> Content </node1_A>
  </node1>

[download]
-----------------------------------------------------
Here is my code:
foreach my $this_routine ( sort keys %subroutines ) 
  
my $element_count = scalar( keys %{$subroutines{$this_routine}} );
  $worksheet->Cells($rowCount, $colCount)->{Value} = $this_routine;
  $rowCount++;
  
  foreach my $this_type (sort keys %{$subroutines{$this_routine}} ) 
     {
    if ( exists $subroutines{$this_routine}->{$this_type}->{Type})
    {    
            $worksheet->Cells($rowCount, $colCount)->{Value} = 
                           $this_type;        
        } 
        $colCount++;
    }
      $rowCount--;
}

[download]
I guess the keys are not unique ??? Because it works with more elements in the xml.
Thanks so much for taking the time to read my question, I hope you have a clue

Replies are listed 'Best First'.
Re: XML to Excel : not Unique ??
by ikegami (Patriarch) on Oct 30, 2008 at 19:48 UTC

    You tell us the problem is related to your XML, but there's nothing relating to XML in your code. Presumably, %subroutines was created from the XML, but you neither show how that was done, nor the contents of %subroutines. 

    use Data::Dumper;
print(Dumper(\%subroutines));

    [download]
[reply]
[d/l]
[select]
Re: XML to Excel : not Unique ??
by Sporti69 (Acolyte) on Oct 31, 2008 at 09:24 UTC
    Sorry, I thought this would be enough, but I'll post my complete code now. The problem is not actually the XML
    because with unique elements it does work.
    <ROOT>

    <SUB CLASSNAME="SUB1">
        <Element1 Type="Type1">Type 1 value</Element1>
        <Element2 Type="Type2">Type 2 value</Element2>
    </SUB>

    <SUB CLASSNAME = "SUB2">
        <Element1 Type="Type1">Type 1 value</Element1>
        <Element2 Type="Type2">Type 2 value</Element2>
        <Element2 Type="Type3">Type 5 value</Element2>    
    </SUB>

    <SUB CLASSNAME = "SUB3">
        <Element1 Type="Star">content 1</Element1>
        <Element2 Type="Henry">Some stuff</Element2>        
    </SUB>

</ROOT>

    [download]
    This fails on the third element of the second node: <element2>
    This is the COMPLETE perl script: 
    #! /usr/bin/perl

use strict;
use warnings;
use Data::Dumper;
use Cwd;
use Win32::OLE;
use XML::Simple;

my $file = 'dataFile.xml';
my $xs1 = XML::Simple->new();
my $doc = $xs1->XMLin( $file, keyattr=>['CLASSNAME'], ForceContent=>1 
+);

my %subroutines = %{$doc->{SUB}};

my $application = Win32::OLE->new("Excel.Application");
my $workbook    = $application->Workbooks->Add();
my $worksheet   = $workbook->Worksheets(1);


my $rowCount = 2;
my $colCount = 2;
my $colspan = 1;

foreach my $this_routine ( sort keys %subroutines ) {
  
  my $element_count = scalar( keys  
  %{$subroutines{$this_routine}} 
);
  $worksheet->Cells($rowCount, $colCount)->{Value} = 
  $this_routine;
  $rowCount++;
  
  foreach my $this_type (sort keys %{$subroutines{$this_routine}} ) {

    if ( exists $subroutines{$this_routine}->
        {$this_type}->{Type}) 
        {    
    
            $worksheet->Cells($rowCount, 
    $colCount)->{Value} = $this_type;
            
        $colCount++;

    }

$rowCount--;

}

    [download]
    Would be great if you guys found what is wrong. Deleting the element2 in the SUB2 will generate an excel file in your current directory.
[reply]
[d/l]
[select]
      #!/usr/bin/perl --

use strict;
use warnings;

use XML::Simple;

my $xml = <<'__XML__';
<ROOT>
    <SUB CLASSNAME="SUB1">
        <Element1 Type="Type1">Type 1 value</Element1>
        <Element2 Type="Type2">Type 2 value</Element2>
    </SUB>
    <SUB CLASSNAME = "SUB2">
        <Element1 Type="Type1">Type 1 value</Element1>
        <Element2 Type="Type2">Type 2 value</Element2>
        <Element2 Type="Type3">Type 5 value</Element2>    
    </SUB>
    <SUB CLASSNAME = "SUB3">
        <Element1 Type="Star">content 1</Element1>
        <Element2 Type="Henry">Some stuff</Element2>        
    </SUB>
</ROOT>
__XML__

my $xs1 = XML::Simple->new();
my $doc = $xs1->XMLin($xml, keyattr=>['CLASSNAME'], ForceContent=>1, F
+orceArray=>1);

use Data::Dumper;
local $Data::Dumper::Indent=1;
print Dumper( $doc );

for my $sub ( keys %{ $doc->{SUB} } ){
    print  "Processing $sub\n";
    for my $elem ( keys %{ $doc->{SUB}{$sub} } ){
        print "Processing   $elem\n";
        for my $element ( @{ $doc->{SUB}{$sub}{$elem} } ){
            print "$element Type: $$element{Type}\n";
            print "$element content: $$element{content}\n";
        }
    }
}

__END__

$VAR1 = {
  'SUB' => {
    'SUB3' => {
      'Element2' => [
        {
          'Type' => 'Henry',
          'content' => 'Some stuff'
        }
      ],
      'Element1' => [
        {
          'Type' => 'Star',
          'content' => 'content 1'
        }
      ]
    },
    'SUB2' => {
      'Element2' => [
        {
          'Type' => 'Type2',
          'content' => 'Type 2 value'
        },
        {
          'Type' => 'Type3',
          'content' => 'Type 5 value'
        }
      ],
      'Element1' => [
        {
          'Type' => 'Type1',
          'content' => 'Type 1 value'
        }
      ]
    },
    'SUB1' => {
      'Element2' => [
        {
          'Type' => 'Type2',
          'content' => 'Type 2 value'
        }
      ],
      'Element1' => [
        {
          'Type' => 'Type1',
          'content' => 'Type 1 value'
        }
      ]
    }
  }
};
Processing SUB3
Processing   Element2
HASH(0x1ba4eb4) Type: Henry
HASH(0x1ba4eb4) content: Some stuff
Processing   Element1
HASH(0x1ba4f44) Type: Star
HASH(0x1ba4f44) content: content 1
Processing SUB2
Processing   Element2
HASH(0x1ba4bfc) Type: Type2
HASH(0x1ba4bfc) content: Type 2 value
HASH(0x1ba4c08) Type: Type3
HASH(0x1ba4c08) content: Type 5 value
Processing   Element1
HASH(0x1ba4c8c) Type: Type1
HASH(0x1ba4c8c) content: Type 1 value
Processing SUB1
Processing   Element2
HASH(0x1ba4980) Type: Type2
HASH(0x1ba4980) content: Type 2 value
Processing   Element1
HASH(0x1ba4a10) Type: Type1
HASH(0x1ba4a10) content: Type 1 value

      [download]
[reply]
[d/l]
        Thanks for investigating my script. But yours ignores the double element. My original version does the job very good. But when I process xml with not unique elements it gives a error: Not a hash reference.
[reply]

Back to Seekers of Perl Wisdom