With the release of Hydro GeoAnalyst 2012.1, we have implemented a new workflow to accommodate Electronic Data Deliverables (EDDs). This new workflow will not only make data importing much faster and easier, it will also help ensure that your imported data is free of errors.
The process has three steps:
- Design an EDD Template
- Use the HGA Quick Checker to validate data against an EDD Template
- Import a validated EDD package
Design an EDD Template
In this first step of the workflow, you can create a template for others to use to validate their data. In the EDD Template Designer you select which fields you wish to have in your template by simply dragging and dropping them onto the Template Format.
The list of available fields on the left are provided as you would be used to seeing them in the Template Manager or the Query Builder (first branch – Data Categories, next branch – Tables, and final branches – fields). As you drag and drop fields into the Template Format section you will notice that other fields may show up automatically – these are also bolded. These fields are required in order to import the data into the select table.
The Template will create a new worksheet for each table the fields were selected from. In the example above, fields were selected from both the Parameter Sample and the Parameter Result tables – and therefore, when you open this template in Excel you will find a worksheet was created for each table.
You have some additional options when creating your EDD Template, such as setting a version number (to keep tracked in case in the future you wish to update a template) and providing a submission URL (email or ftp site) to upload validated EDD’s. Additionally, you can add conditions onto the fields you have chosen that the HGA Quick Checker will verify. For example – you can make the Sample Date field required (even though it is not a required field to enter the data into the database).
Use the HGA Quick Checker to validate data against an EDD Template
The HGA Quick Checker is an Excel Plug-in. Once you install the plug-in you will find a new ribbon in Excel (this plug-in is only supported in Excel 2007 or 2010).
Here you can open the EDD template that was created in Hydro GeoAnalyst. Then the Generate Headers option will become available – this will load all the information from the EDD template into the Excel workbook. In the example from above it creates a worksheet for the Parameter Sample table and the Parameter Result table with the fields associated with each table.
Now you can populate the Parameter Sample and Parameter Result worksheets with the appropriate data. You will notice that there are comments on the column headers to help you understand what the appropriate data for that particular column is.
Once you have populated the worksheets with your data you can select the Validate & Submit button. The Quick Checker will validate all data in all the worksheets. If there are any errors (where the data fails to meet the conditions) then the cell will be highlighted red (as well as the tab).
Once you have corrected all the data you can select the Validate and Submit button again. If the Quick Checker does not find any problems with your data it will automatically open an email in Outlook with the email address specified in the EDD template and attach a *.zip_hga file which contains your excel spreadsheet and the validation information. If no email was specified in the template you can save the *.zip_hga file on your computer. This file can then be imported with the EDD import routine.
Import a validated EDD package
To launch the EDD Import routine select Modules / Import / EDD and select the *.zip_hga file that was generated within the HGA Quick Checker. Please be aware that you should not attempt to unzip or modify the file in any way as this can invalidate the file and cause difficulties importing the data.
As soon as the file is validated you will see the Validation Results dialog appear. Here you may need to answer a question before continuing. For example: the Validation found that some of the station names you are trying to import do not exist in the database – you can choose to append the new station names or reject records for stations that do not exist. Make your choice by selecting the appropriate option in the Action column. If you wish to see which stations are not currently in the database, select the See Data Link in the Description field. Now the Import dialog is updated and you can see how many records will be imported. Select the Import button to import the records and the import dialog updates to inform you that the import was successful.
The EDD workflow significantly decreases the effort necessary to import data into your data management software. By using the optional conditions, you can have your data returned to you in the specific format required by your project, saving you the frustration and time involved in reformatting.