View Source: Excel2CSV - Waikato Linux Users Group

You are viewing an old revision of this page. View the current version.

[Perl] script to convert [Microsoft] Excel Spreadsheets to [CSV] (Command Seperated Values)

Requires [Perl] Modules:

#Spreadsheet::!ParseExcel
#Getopt::Long

See Also: [http://search.cpan.org/~tmtm/Spreadsheet-ParseExcel-Simple-1.01/] for Simple Excel Parsing.

----
  use strict;
  use Spreadsheet::!ParseExcel;
  use Getopt::Long;

  my $oExcel = new Spreadsheet::!ParseExcel;
  my $excelfile ;
  my %options;


  $options{'suffix'} = ".csv";
  $options{'sep'} = ";";

  Getopt::Long::!GetOptions( \%options, 'help','quiet', 'suffix=s', 'sep=s') or exit;

  if (@ARGV == 0 or defined $options{'help'}) {
     die <<'EOF';
  Usage: excel2csv [[ options ] files
      where the options specify
  	--help		this helpful help
  	--quiet		no progress information given
  	--suffix	output file suffix (default ".csv")
  	--sep		output field separator (default ";")
  EOF
  }


  #1.1 Normal Excel97
  foreach $excelfile (@ARGV)
  {
      my $oBook = $oExcel->Parse($excelfile);
      my($iR, $iC, $oWkS, $oWkC, $file);
      for(my $iSheet=0; $iSheet < $oBook->{!SheetCount} ; $iSheet++) {
          $oWkS = $oBook->{Worksheet}[[$iSheet];
          open $file, "> " . $oWkS->{Name} . $options{'suffix'} or die "Can not open file for writing!" ;
          print $oWkS->{Name} . "\n" if not defined $options{'quiet'};
          for(my $iR = $oWkS->{!MinRow} ;
              defined $oWkS->{!MaxRow} && $iR <= $oWkS->{!MaxRow} ; $iR++) {
              for(my $iC = $oWkS->{!MinCol} ;
                  defined $oWkS->{!MaxCol} && $iC <= $oWkS->{!MaxCol} ; $iC++) {
                  $oWkC = $oWkS->{!Cells}[[$iR][[$iC];
                  print $file $oWkC->Value if($oWkC);
                  print $file $options{'sep'};
              }
              print $file "\n";
          }
          close $file;
      }
  }
----

No page links to Excel2CSV.

Version 2, saved on Friday, February 20, 2004 2:06:15 pm by DrewBroadley

Edit PageHistory Diff Info LikePages