Using the HZST Format

<< Click to Display Table of Contents >>

EQuIS 7  >>  EDP > EDP Format Files, Open, Data Review, Tools, EDP Professional and EDP Enterprise > EDP Format Files >

Using the HZST Format

Format Information

 

The Hazardous Site (HZST) Format is used to submit data to the New Jersey Department of Environmental Protection (NJDEP) Site Remediation Program (SRP). For further details on the submittal process and specific submission guidelines read here: http://www.nj.gov/dep/srp/hazsite/.

 

As of November 15, 2012, the HZST Format has been updated to comply with the new requirements outlined by the New Jersey Department of Environmental Protection. For further details, read here: http://www.nj.gov/dep/srp/hazsite/news/20121102_compliance_date.htm.

 

NJDEP Hazsite Table Definitions and Field Definitions: http://www.nj.gov/dep/srp/hazsite/docs/edi/srpedi04.htm.

 

The NJHZ Site Format is available to EQuIS 6 clients who have the PlusD, PlusDG, PremierD, PremierDG and Premier Ultimate Professional Product Libraries.

 

Remapping for NZST Site Format

 

In order to comply with the NJDEP SRP submission guidelines, you may need to use the EQuIS Remap tool to manage synonyms on both import and export of data using the format. The HZST Format .zip will contain a guidance file, NJDEP_HZST_REMAP_GUIDE.xlsx, on creating remaps for this format.

 

Note: All Internal Values provided in the NJDEP_HZST_REMAP_GUIDE file are provided as examples only. Internal values should be reviewed to ensure the values in the database match those used in your remaps.

 

RT_REMAP_DETAIL Table

 

EXTERNAL_FIELD: Column headers in the HZST format.

 

EXTERNAL_VALUE: Data populated in the column indicated in EXTERNAL_FIELD. Please reference the HZST EDD description for the data type and field length of each column, e.g. numeric, 20 characters.

 

INTERNAL_VALUE: This is data that exists in your database reference tables. The EXTERNAL_VALUES will be remapped to the field indicated in this column when the EDD is loaded into the database. The HZST EDD Description also indicates what fields may need remapping in the Database Mapping column.

 

Generate an HZST Description EDD

1.Open EDP.

2.Load the HZST format.

3.Click on the EDD Description button in the ribbon.

EDP_EDD_Description

4.Excel will open a workbook. Each tab along the bottom will go to a sheet that describes an individual file.

 

By default, the NJDEP HZST format includes some remaps, a few of which are piped values (i.e. many external values mapped to one internal value). Any of the external values entered as piped data in the RT_REMAP_DETAIL.EXTERNAL_VALUE will remap to the internal value entered in the RT_REMAP_DETAIL.INTERNAL_VALUE field. The pipe is treated as an "or" remapping accordingly.

 

For each RT_REMAP_DETAIL.REMAP_CODE listed, the EXTERNAL_FIELD must be unique. Similarly, for each RT_REMAP_DETAIL.REMAP_CODE listed, the INTERNAL_FIELD must be unique. As such, if there are multiple external values that need to be remapped to one internal value, the external values will need to be entered in the same field using a pipe [|] to separate the values. The remap function will work as intended if the piped data is entered as described.

 

On export, the entire external value will be exported (not just the first value) when it contains a pipe. However, format exports do not use the same code, so they can be customized to export the whole list, a single value, or to use the original value, if stored in a custom field. If this customization is desired, please contact support@earthsoft.com.

 

Adding the NJHZ Site Format to your EQuIS Installation

 

1.Download the Format and unblock your download.

2.Save the format_HZST file to the C:\Program Files\EarthSoft\EQuIS\Formats folder.

3.Create a folder in the same \Formats folder called NJDEP.

4.Extract all the files in the format_HZST_[version].zip file and add the format specific files to your NJDEP file.

 

Note: The zip file may contain another zip for the format. Be sure to take the zip file that contains the HZST.dll, HZST.vbe, HZST.xml, HZST.xse, HZST-enum.xml, HZST-export.xme, and the NJDEP_HAZST_REMAP_GUIDE and extract it to your newly created NJDEP file.

 

5.Open EQuIS Professional, navigate to the Government Exports tab, and click the HazSite button.

6.EDP will open and the HZST format will either open or you will be prompted to select the HZST format.

 

Troubleshooting

 

When using the HZST format to load data through EQuIS Professional EDP, additional remaps will need to be implemented in RT_REMAP and RT_REMAP_DETAIL to avoid the following error message during the Create step:

 

Cannot set column 'matrix_code'. The value violates the MaxLength limit of this column.

 

Cause: HZSAMPLE.MATRIX data type = Text(15); however the database mapping DT_SAMPLE.MATRIX_CODE = VARCHAR (10)

 

Resolution: Populate RT_REMAP and RT_REMAP_DETAIL to remap the imported data to meet the database requirements. Please note RT_REMAP.REMAP_CODE and RT_REMAP_DETAIL.REMAP_CODE must be populated with "HZST" in order for the remap to automatically occur during the create process.

 

Example:

 

Populate RT_REMAP:

RT_REMAP.REMAP_CODE = HZST

RT_REMAP.STATUS_FLAG = A

 

40034-rt_remap

 

Populate RT_REMAP_DETAIL:

RT_REMAP_DETAIL.REMAP_CODE = HZST

RT_REMAP_DETAIL.STATUS_FLAG = A

RT_REMAP_DETAIL.EXTERNAL_FIELD = HZSAMPLE.MATRIX

RT_REMAP_DETAIL.EXTERNAL_VALUE = GROUND WATER

RT_REMAP_DETAIL.INTERNAL_VALUE = WG **