Re: Any Excel Experts

Fellow database fans,

We're looking for someone who lives and breathes Microsoft Excel to detangle a large collection of worksheets and work it into a more generalizable format for publication as part of an environmental open data project. We're talking 5-6 years of data collected willy-nilly into 120 worksheets and held together with links, formulas, and duct tape. The ideal candidate will be:

* An expert in Excel 10
* Able to consolidate the worksheets down to a single workbook.
* Able to downgrade the worksheet to Excel 97 format
* Able to convert the worksheet to OpenOffice.org or Gnumeric and fix the breakage
* Available to complete this task before June 15th.
* Has experience and interest in the publication of open data

Anybody qualified for this, or know someone who is?

Thanks!

David

On Thu, 5 May 2011, David E. Wheeler wrote:

> We're looking for someone who lives and breathes Microsoft Excel to
> detangle a large collection of worksheets and work it into a more
> generalizable format for publication as part of an environmental open data
> project. We're talking 5-6 years of data collected willy-nilly into 120
> worksheets and held together with links, formulas, and duct tape. The
> ideal candidate will be:

Hey, David!

I suspect you'll not find anyone with those qualifications. My limited
experience with environmental data in spreadsheets is that those who use a
spreadsheet rather than a database have no clues what they're doing. I spent
several weeks extracting permit compliance monitoring data for one year, and
didn't get all of it yet. There are about 30 more years to go. Yuck!

What you ought to consider is someone who knows grep, sed, and awk and can
apply those to data exported from Excel as .csv, one per tab/spreadsheet.
Then you can concatenate the data in a uniform format and put them in a
spreadsheet or database for the project. Perhaps someone in India for either
direct Excel tweaking or exported data tweaking?

I won't do it because you'd not have the budget for the hours required and
I have other project priorities that are more fun and satisfying.

Good luck in your search,

Rich

David,
There's an app for that!
... sort of.
Spreadsheet::ParseExcel (current maintainer is John McNamara) reads and parses Excel files. I've used it for grabbing data from spreadsheets to put into my lab's database. It works great. I've not used it with cells populated by functions using cells from other worksheets however.

TomK
MMI DNA Services Core Facility
503-494-2442
kellert at ohsu.edu
Office: 6588 RJH (CROET/BasicScience)

On May 5, 2011, at 3:20 PM, David E. Wheeler wrote:

> Fellow database fans,
>
> We're looking for someone who lives and breathes Microsoft Excel to detangle a large collection of worksheets and work it into a more generalizable format for publication as part of an environmental open data project. We're talking 5-6 years of data collected willy-nilly into 120 worksheets and held together with links, formulas, and duct tape. The ideal candidate will be:
>
> * An expert in Excel 10
> * Able to consolidate the worksheets down to a single workbook.
> * Able to downgrade the worksheet to Excel 97 format
> * Able to convert the worksheet to OpenOffice.org or Gnumeric and fix the breakage
> * Available to complete this task before June 15th.
> * Has experience and interest in the publication of open data
>
> Anybody qualified for this, or know someone who is?
>
> Thanks!
>
> David
>
>
>
>
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug

On May 5, 2011, at 5:06 PM, Tom Keller wrote:

> There's an app for that!
> ... sort of.
> Spreadsheet::ParseExcel (current maintainer is John McNamara) reads and parses Excel files. I've used it for grabbing data from spreadsheets to put into my lab's database. It works great. I've not used it with cells populated by functions using cells from other worksheets however.

Alas, it's not just the data we need from the spreadsheet. It's the algorithms. Also, they're a mess, with decidedly un-normalized data. They need to be detangled before they can be used programmatically. And therein lies our need.

Thanks,

David

On May 5, 2011, at 3:48 PM, Rich Shepard wrote:

> I won't do it because you'd not have the budget for the hours required and
> I have other project priorities that are more fun and satisfying.

You words fill me with joy. :-(

David

On Thu, 5 May 2011, David E. Wheeler wrote:

>> I won't do it because you'd not have the budget for the hours required and
>> I have other project priorities that are more fun and satisfying.
>
> You words fill me with joy. :-(

David,

I wasn't being flippant. I spent many days with this effort. It involved
moving blocks of data around on a single page so everything was in one
contiguous block, exporting each tab as a separate file, writing a python
script or figuring out whether grep, sed, or awk could reformat the data,
then trying to copy it into the postgres table and fixing the errors the
dbms found.

Many organizations have SOPs on written documents: format, style, word
usage and so on. I don't know of any such organization that has defined
standards for spreadsheet usage. People become familiar with spreadsheets
when they become familiar with word processors (or shortly thereafter), but
databases are not taught in schools or colleges. A dbms is an engine that
needs to be used in specific applications, it's not an icon on the Windows
desktop that opens a screen for immediate use. It takes thought and
understanding, and these are not sufficiently common.

From your description the work will be very labor intensive and require
someone who deeply understands the need and what has to be done with the
displayed data to fulfill that need. That's why I suggested an overseas
organization like those that used to be used for digitizing paper maps
accurately and inexpensively.

Years ago I had some map data conversion done at the medium-security
prison in Salem. Getting that project going was in interesting experience
since I'd never before been in a prison. But the guys did an excellent job
and it was part of their rehabilitative training. Perhaps they have people
who can do your data conversion. And, the people doing the work have nothing
else to do with their days and they're a higher class of prisoner in these
programs than at the maximum security prisons.

Rich

(Bcc pdxpug)

Ok, this thread is done, Richard. Direct comments about the gig posting to
David, privately, folks.

Please, lets get back to talking about databases.

-selena
On May 6, 2011 6:03 AM, "Rich Shepard" <rshepard(at)appl-ecosys(dot)com> wrote:
> On Thu, 5 May 2011, David E. Wheeler wrote:
>
>>> I won't do it because you'd not have the budget for the hours required
and
>>> I have other project priorities that are more fun and satisfying.
>>
>> You words fill me with joy. :-(
>
> David,
>
> I wasn't being flippant. I spent many days with this effort. It involved
> moving blocks of data around on a single page so everything was in one
> contiguous block, exporting each tab as a separate file, writing a python
> script or figuring out whether grep, sed, or awk could reformat the data,
> then trying to copy it into the postgres table and fixing the errors the
> dbms found.
>
> Many organizations have SOPs on written documents: format, style, word
> usage and so on. I don't know of any such organization that has defined
> standards for spreadsheet usage. People become familiar with spreadsheets
> when they become familiar with word processors (or shortly thereafter),
but
> databases are not taught in schools or colleges. A dbms is an engine that
> needs to be used in specific applications, it's not an icon on the Windows
> desktop that opens a screen for immediate use. It takes thought and
> understanding, and these are not sufficiently common.
>
> From your description the work will be very labor intensive and require
> someone who deeply understands the need and what has to be done with the
> displayed data to fulfill that need. That's why I suggested an overseas
> organization like those that used to be used for digitizing paper maps
> accurately and inexpensively.
>
> Years ago I had some map data conversion done at the medium-security
> prison in Salem. Getting that project going was in interesting experience
> since I'd never before been in a prison. But the guys did an excellent job
> and it was part of their rehabilitative training. Perhaps they have people
> who can do your data conversion. And, the people doing the work have
nothing
> else to do with their days and they're a higher class of prisoner in these
> programs than at the maximum security prisons.
>
> Rich
>
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug