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

I need to parse some fairly simple excel spreadsheets and store the data. I have looked at several modules at CPAN. Most of the modules for parsing appear to use Spreadsheet::ParseExcel. What's the best module for parsing basic excel spreadsheets? thanks again. randy t

Replies are listed 'Best First'.
Re: Parsing Excel Files?
by bart (Canon) on Mar 11, 2005 at 09:09 UTC
    Well, you've said it yourself: SpreadSheet::ParseExcel. On Windows and provided you have Excel on your system, you can also use Win32::OLE, but IMO it's harder. The former is platform-independent and is standalone — no need for Excel.

    A quick look on CPAN showed me there's Spreadsheet::BasicRead too, but that is built on top of SpreadSheet::ParseExcel. You can see for yourself if you like it.

[reply]
Re: Parsing Excel Files?
by jmcnamara (Monsignor) on Mar 11, 2005 at 09:18 UTC

    The Excel parser with the easiest interface is Spreadsheet::ParseExcel::Simple. You could start with that and then move on to to Spreadsheet::ParseExcel if you need more features.

    --
    John.

[reply]
Re: Parsing Excel Files?
by Ninthwave (Chaplain) on Mar 11, 2005 at 11:15 UTC

    I am doing it currently using ODBC and DBI.

    # Set the DSN for ODBC connection

my $DSN        = "driver=Microsoft Excel Driver (*.xls);dbq=$SpreadShe
+et";

# Connect to the Spread Sheet

my $ExcelHandle = DBI    ->connect("dbi:ODBC:$DSN", '','')
    or die "$DBI::errstr\n";

    [download]

    This code will connect using the Excel ODBC driver to the file specified by $SpreadSheet. The tables will be the name of the work sheets. 

    my $Table    = '[Sheet1$]';  # This translates to Sheet1

# This is an example query statement to read a worksheet.

my $QueryStatement = "SELECT * FROM $Table";

my $ExcelQuery    = $ExcelHandle    -> prepare( $QueryStatement );

$ExcelQuery    -> execute();

    [download]

    Row one is used for the column names in the table. And the rest of the data can be accessed with SQL queries. I find it works nice. If I am transfering data from spreadsheets to databases I find it easier to work in an all database mindset.


    "No matter where you go, there you are." BB
[reply]
[d/l]
[select]
Re: Parsing Excel Files?
by dragonchild (Archbishop) on Mar 11, 2005 at 13:20 UTC
    Take a look at DBD::Excel. If you're comfortable with SQL, that is probably the easiest option.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]

Back to Seekers of Perl Wisdom