On 6/30/06, Mr. Shawn H. Corey <[EMAIL PROTECTED]> wrote:
On Fri, 2006-30-06 at 17:07 +0100, Gary Stainburn wrote: > Hi folks > > I'm just about to look into writing a perl prog to read in an excel > spreadsheet and us that to populate a SQL database. > > The idea is to save the spreadsheet as a .xml file (using Office XP), use a > perl module to read in the file, and using the parsed data update the > database. > > Would anyone like to pass on any experiences / recommendations before I dive > headlong in.
[snip]
You may also want to consider exporting your data as a CSV or DSV file. There are modules in CPAN that can read these formats and other modules that can read an Excel file directly.
I just want to second that. XML is a long, slow, complicated, error-prone way to get already structured data from a speadsheet to a database, especially with large files. Save it at tab or CSV. Most databases can read those formats natively; you may not even need a perl script at all. If you do (e.g. to do a little manipulation or handle the connection) the scripting will be lightweight, and the modules are speedy and reliable. And by speedy, I'm talking orders of magnitude. YMMV depending on the number of rows and columns in your dataset as well as the makeup of individual records, but I've found that parsing 10,000+ row address lists in CSV is anywhere from 10 to 1,000+ times faster than parsing equivalent datasets in XML. Don't forget that XML export adds a great deal of metadata used only by the XML engine that must be parsed and stored in memory. For spreadsheet exports--at least when you're using it a spreadsheet to store numbers, and not as a "poor man's database" to store quantities of text--the size of the XML markup is often far larger than the size of the actual data. When moving data from spreadsheets to databases, split() and Text::CSV are your best friends. Just my $.02 -- jay -------------------------------------------------- This email and attachment(s): [ ] blogable; [ x ] ask first; [ ] private and confidential daggerquill [at] gmail [dot] com http://www.tuaw.com http://www.dpguru.com http://www.engatiki.org values of β will give rise to dom!
