Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Reading from Excel and writing to Oracle

by loris (Hermit)
on Aug 24, 2007 at 10:50 UTC ( #634832=perlquestion: print w/replies, xml ) Need Help??
loris has asked for the wisdom of the Perl Monks concerning the following question:

Dear all,

I have been asked to extract data from an Excel sheet and write it to an Oracle database (this is all the description of the task I have so far). What would be the modules of choice to achieve this? Spreadsheet::ParseExcel, DBI and DBD::Oracle? Or is there anything else I should look at?



"It took Loris ten minutes to eat a satsuma . . . twenty minutes to get from one end of his branch to the other . . . and an hour to scratch his bottom. But Slow Loris didn't care. He had a secret . . ." (from "Slow Loris" by Alexis Deacon)
  • Comment on Reading from Excel and writing to Oracle

Replies are listed 'Best First'.
Re: Reading from Excel and writing to Oracle
by Tux (Abbot) on Aug 24, 2007 at 11:27 UTC

    Shamelessly plugging my own module Spreadsheet::Read, instead of (or better on top of) Spreadsheet::ParseExcel if you like to be independent of the underlying spreadsheet format. Re-use your script if the sheets are passed in OpenOffice in the future.

    Enjoy, Have FUN! H.Merijn
Re: Reading from Excel and writing to Oracle
by moritz (Cardinal) on Aug 24, 2007 at 11:08 UTC
    It ssems like a good (an obvious) choice.

    There's also DBD::Excel, but that doesn't look too advanced, flexible and maintained. And under the hood it still uses Spreadsheet::ParseExcel.

      DBD::Excel itself may not have changed much, but there may not be much to change (I don't use Excel so I don't know) . But it's just a shim between SpreadsheetParseExcel and SQL::Statement and DBI and all of those modules have changed. Unless you need to do manipulation of the spreadsheet or complex queries, it should be sufficient for this task. If you're reading from one database and writing to another, it makes sense to do it all in DBI. If DBD::Excel isn't suitable, then you can declare Excel as an ODBC DSN and use DBD::ODBC on it.

        Well said. DBI-1.59 was released only yesterday! Spreadsheet::ParseExcel is also well maintained.

        Same points are valid for all wrapper modules, including my Spreadsheet::Read. If Spreadsheet::ParseExcel or Spreadsheet::ReadSXC changes, the wrapper also changes.

        DBD::Excel seems like a very valid choice, but still limits you to Excel only. It won't work on OpenOffice, but it will probably make a very clear and consice conversion program. (/me imagines a DBD::Spreadsheet in the future. Not that I have current plans, but it would be fun to experiment with that. :) Problem is that there is no Spreadsheet::Write (yet), so this DBD would be read-only.

        I would personally NOT advice the use of DBD::ODBC on Excel for a plethora of reasons. I have just gone through the pain of using ODBC on windows to connect to several ODBC sources, and the number of locations where things can go (and will go) wrong is inmeasurable, ranging from non-matching buffer sizes to 32/64 bit mismatches and array fetches that don't. For the problem stated, I think that using ODBC is heading for the Yak-shaving path.

        Enjoy, Have FUN! H.Merijn
Re: Reading from Excel and writing to Oracle
by andyford (Curate) on Aug 24, 2007 at 12:00 UTC

    I've done that job a couple times with the modules you mention.

    non-Perl: Andy Ford

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://634832]
Approved by GrandFather
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (4)
As of 2017-04-23 17:15 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (431 votes). Check out past polls.