Hi I have a script that coverts an xlsx file to a csv file, and then creates individual files based on column [0]. I then run another script that then coverts the new csv files back to xlsx and deletes the csv files. run a seperate scripts they produce what i want.

However i cannot get them to work if i combine them into a single script. The xlsx files are empty and the files are not deleted.

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::BasicRead;
use Excel::Writer::XLSX;
local $" = "'\n'";        
    my @classes;    
    our $Header_row;
                    #Set-up Files
    my $xlsx_WSD = ( "C:\\Temp\\Data\\data.xlsx"),, 1;
    my @csvtemp;
        if ( -e $xlsx_WSD ) {
    my $ss   = new Spreadsheet::BasicRead($xlsx_WSD) or die;
    my $col = '';
    my $row  = 0;
        while ( my $data = $ss->getNextRow() ) {
        $row++;
        $col= join( "\t", @$data );
            push @csvtemp,  $col . "\n" if ( $col ne "" );
    }
}    
    my @arraynew;
    my %seen;
        $Header_row = shift (@csvtemp);  # this is where the headers a
+re stored.

        foreach (@csvtemp){
        chomp;
                 $_ = uc $_ ;
                 $_ =~ s/,//g;                                        
+#removes commas
                $_ =~ s/\t/,/g;                                    #ch
+ange from tab to  csv
            push @arraynew, $_ . "\n" if !$seen{$_}++ ;    #remove any
+ dupes
}    
        $Header_row =~ s/\t/,/g ;
    my (%seena, %clss_out);
    my @class_file;
        foreach (@arraynew) {
        chomp;
    my ($clss_col, $data) = $_ =~ m{^([A-z]*),(.*)};
            next if $seen{$clss_col}{$data}++;
    append_data($clss_col, $_, \%clss_out);
    
}    
csv_xlsx ();
#-------------------------------------------------------------------
        
sub append_data {
    my ($clss_col, $line, $clss_out) = @_;
            if (! $clss_out->{$clss_col}) {
      open($clss_out->{$clss_col}, '>', "C:\\Temp\\Data\\$clss_col.csv
+") or die "Unable to open '$clss_col.csv' for appending: $!\n";
        print { $clss_out->{$clss_col} } $Header_row;
    push @class_file, "C:\\Temp\\Data\\$clss_col.csv";
    }
        print { $clss_out->{$clss_col} } $line . "\n";

}



sub csv_xlsx{
    my $dir = "C:\\Temp\\Data";
            foreach my $fp (glob("$dir/*.csv")) {
        open my $fh, "<", $fp or die "can't read open '$fp'";
    my $xlsx = $fp =~ s\csv\xlsx\;
    my $workbook  = Excel::Writer::XLSX->new("$fp");
    my $worksheet = $workbook->add_worksheet();
    my ( $x, $y ) = ( 0, 0 );
            while (<$fh>) {
    my @list = split /,/;
            foreach my $c (@list) {
                        $worksheet->write( $x, $y++, $c );
            }
                        $x++;
                        $y = 0;
        }
                        $workbook->close();
        close $fh or die "can't read close '$fp'";
    }
        unlink glob "C:\\Temp\\Data\\*.csv" or warn $! ;
}
[download]

data

CLASS,PUPIL,M/F,Year
ZEBRAS,JAMES,M,3
LIONS,AMIE,M,1
GIRAFFES,AMES,M,R
MEERKATS,JOHN,M,4
LEOPARDS,JIM,M,2
HIPPOS,PETER,M6
MONKEYS,PELE,M,5
ZEBRAS,DAN,M,3
MONKEYS,MINNIE,F,6
HIPPOS,REG,M,6
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.