EQuIS consulting, training, and mentoring

Disclosure: You came here looking for data management tips and I'm going to pitch you consulting services- just once. And here's why....

My previous employer was hit hard by the recession and unfortunately, that meant that I was laid off.

Not to worry though- I've already had resumé requests and it seems like as the impending economic recovery has created new job opportunities. In the meantime, I would really enjoy getting out and meeting more environmental data managers in order to learn how you're doing your job, as well as to share some of my tools and techniques with you.

I'd be happy to come by your office for a couple of hours just to meet you (or re-acquaint with you) and share data management ideas with you.  Of course, we can talk on the phone or use web-meeting software as well.  If you need more in-depth help such as data migration or report customization then I'd be honored to help you out as an outside consultant.

You can download my resumé here.

As always, feel free to contact me with specific data management questions.

I look forward to hearing from you,

Dan

dan@danhigginsdesigns.com

P.S. The Environmental Data Management Conference in on for June 9 and 10, 2011 at the Edgefield here in Portland. There will more info here soon. Also, Earthsoft will posting information on their community site soon and everyone on Jennifer Rea's contact list will get an email announcement.

Migrating Historical Data

We're just wrapping up a big historical data migration into an EQuIS database. The data are needed to support a site remedial investigation.  This migration had an interesting twist I thought I'd share with you. First a little background on data migration.

In the worst cases historical data comes from a mish-mash of hard-copy reports, EDDs in various formats, and Excel tables. Crunching through these reports and figuring out how the results tie into various location name spellings is tedious and error-prone.  For example, are "S1-MW01" and "SMW1" the same location? Conversely, the best cases provide you with an Access database that's somewhat normalized with unique lists of valid values and obvious relationships between key data tables. Between these two extremes lies the task of crunching a pile of cross-tab Excel tables. 

The intrepid (curious and determined) data cruncher can take an Excel table and "unwind" it into a columnar database-friendly form using a macro that reads through the table using the same loop within a loop procedural logic I presented here.  The columnar output can be easily rearranged into an ESBASIC EDD using Access. I like Access for this task rather than Excel. In Excel I find I spend a lot of time running the lookup function and creating key columns with formulas that have to be pasted down. In Access I set up a ESBASIC template and then build queries that move the "unwound" columnar data from a table in Access to the ESBASIC EDD. I'll even link my queries to reference tables to get the proper valid values applied to the EDD. Then, I export the data from the ESBASIC template and load it into EQuIS. If the EDD has errors and won't load, I can make an a adjustment or two in the Access query and give it another try. This allows quick, successive iterations of the migration actions to ESBASIC and saves a ton of time. Conversely, it's easy to make a little error when using Excel to make the transition to ESBASIC that forces you to start your multi-step process all over.

The unusual data migration we're finishing now began as an 800+ page pdf!! No kidding. The cool part was that all the data was collected by one consultant and the tables had clearly been created from a well organized database. No, we couldn't get the database- wouldn't that have been nice? We tried reading the data into a text file with optical character recognition (OCR), but the tables had these alternating light gray bands that messed with the software's ability to read the characters accurately.

So, we hand entered it all. Really.  For tables like VOCs with lot's of NDs we created a template and then copy-pasted each new table. The new tables values were compared to the pdf and updated in cases where the detection limit varied or a spurious detect occurred. Once the tables were all created I used the Excel unwinding procedure I described above to create columnar data.

Tables with metals and wet-chem data that contained lot's of detects were a little harder. To handle these I created an Excel data entry form that builds the EDD as the data is entered. The tab order for the each record was set up to match the order the chemicals appear in the pdf tables.



As usual, we used Access to create the ESBASIC EDDs. We loaded the all EDDs into EQuIS, then created crosstab data tables and hand-checked EVERY record against the source pdfs.

I'm very pleased because the whole process went rather quickly considering the scale of the migration. A couple of years ago I would have bid this much higher that I did.  End the end we came out on budget and have nice database that the project team can count on to start their remedial investigation.