Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: Accessing excel through perl

    Hi,

    We have a excel spreadsheet sheet, which we would like to save it to the database by writing a perl script.

    1) What is the best solution?

    2) We are thinking of saving it as a .csv and bcp into the SYBASE database.
    How to save the .xls file as .csv using perl?

    I appreciate your help.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    This way would be the best:

    1.) parsing .xls-file
    2.) connect to database
    3.) save infos in database

    I would recommend to use Spreadsheet::ParseExcel for parsing .xls-file.
    To connect to the database you need DBI and the corresponding DBD-module.

    All modules are available at cpan.org

    Yesterday I have written a small script to save .xls as .csv. It's quick and dirty, but you can improve it (please let me know your changes):
    Code:
    #! /usr/bin/perl
    
    use strict;
    use warnings;
    use lib qw(./cpan/perllib);
    use Spreadsheet::ParseExcel;
    
    my $file = (-f $ARGV[0]) ? $ARGV[0] : print_error($ARGV[0]."is not a file");
    my $home = -d $ARGV[1] ? $ARGV[1] : print_error($ARGV[1]."is not a directory");
    
    print_error('wrong parameters','use') unless($file && $home);
    
    my $xls = Spreadsheet::ParseExcel::Workbook->Parse($file) or print_error($!);
    foreach my $workbook(@{$xls->{Worksheet}}){
      my $csv = $home.'/'.$workbook.'.csv';
      print "CSV: $csv\n";
      open(W_CSV,">$csv") or print_error($csv." ".$!);
      for(my $row = $workbook->{MinRow}; defined $workbook->{MaxRow} && $row <= $workbook->{MaxRow}; $row++){
        my @values = ();
        for(my $col = $workbook->{MinCol}; defined $workbook->{MaxCol} && $col <= $workbook->{MaxCol}; $col++){
          my $cell  = $workbook->{Cells}[$row][$col];
          my $val   = $cell ? $cell->Value : '';
          push(@values, $val);
        }
        print W_CSV join(';',@values),"\n";
      }
      close W_CSV;
    }
    
    sub print_error{
      print STDERR "Error: ",shift,"\n";
      if(shift eq 'use'){
        print qq~
          Usage: $0 <excel_file> <output_path>
    
    ~;
      }
      exit -1;
    }
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Jul 2003
    Posts
    34

    accessing Excel through perl

    Thank you very much.
    I will update if any changes were made to the code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •