On Wed, 12 Oct 2011, Keith Lofstrom wrote: > In support of Aaron's excellent reply, I want to add that for large > volumes of data, spreadsheets are not the best tool. I haven't used > xlrd (or the likely Perl equivalent) but I have exported spreadsheets > as CVS and processed them with 10 line Perl scripts, even vi sometimes.
Before you all think I'm totally incapable let me try to explain. When I had the original spreadsheets I did export as .csv and used awk or python scripts to reorganize the data. In this case I was sent the whole spreadsheet with the latest data added to one of the tabs. I need to extract only the added data so I can insert them into the database table. The data still need a lot of adjusting because spreadsheets don't validate cell entries for proper format or enforce any standards. To isolate the newly added cells, I opened the newest spreadsheet in LO, then told it to compare it with the original spreadsheet. This highlighted the additions. Instead of either accepting or rejecting the changes, I saved the marked new spreasheet (nice red cell boundaries around the new data) and killed the compare documents dialog box. Worked like a charm. Over the past 30 years that I've worked with microcomputers I've rarely used a spreadsheet. I do for preparing project budgets and other simple tasks, but never for anything complicated. I have written databases in dBASE II and III, Paradox/DOS, and since migrating to linux in 1997, postgres and sqlite. I've had this discussion of spreadsheets vs. databases many times. I've concluded that when folks learn to use 'office' applications on a computer they learn to use a spreadsheet because that's part of what they have available. They're not taught database design, SQL, or anything else relevant to databases. So, since the only tool they know are spreadsheets, they use them for everything. Even when they trip over the limitations. Clients send me data in spreadsheets and, when I need to provide more than an analytical report, they get a database application back. That's why I'm now developing an extensive environmental model using python, wxPython, postgres, GRASS, and R. Both my client and the state regulators will be able to use it on their Microsoft machines when it's done. Rich _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
