Governor Phil Murphy • Lt. Governor Tahesha Way
  Search  
new jersey department of environmental protection
NJ Home Page Services A to Z NJ FAQs NJ Departments/Agencies departments
site remediation program

SRP Home | DEP Home

Crumb1 Crumb2 Current

  

SRWM Rules & Regulations Guidance Documents SRP-EDI Manual

Previous Table of Contents

4.0 Using the Spreadsheet Format (.WK1) Option

A Lotus-compatible spreadsheet format is acceptable for submitting electronic data. Using the spreadsheet option requires strict adherence to the format defined below.

NOTE: the column header names in the .WK1 format differ slightly from the field names in the .DBF format. If you modify a .WK1 and then save it as a .DBF, you must change the field names to match those of the .DBF structure in section 3.0 of this manual prior to submittal to the department.

Lotus-Compatible Spreadsheet Option: Electronic Submittal Requirements and Process

You can choose to submit sample and results data in a Lotus .WK1 (1-2-3) compatible spreadsheet format that conforms to the structure below. For instance, you may use Microsoft Excel to create a worksheet, then save the data as a .WK1 (1-2-3) file. When completing the spreadsheet, one record should be entered per row, with the fields for each record laid out in columns as described below. All date fields in the spreadsheet submission should be in the format MM/DD/YYYY. Refer to Section 8.0, Discussion of Fields in this manual for complete field descriptions and acceptable entries.

DATASET File

The Dataset can be defined as the electronic equivalent of the cover page for the sampling and analytical results that are submitted to the SRP via hard copy. The DATASET file provides basic information about the sampling event, including site description, investigation phase, consultant, submittal date, and other information. There should be only one record in each Dataset file. The Dataset may represent one or more sampling episodes at a site. For example, the sampling and analytical results of four rounds of quarterly monitoring (at the same site) can be submitted as a single dataset.

The Dataset spreadsheet below shows you the number of columns required for each Dataset Record, the Name of each column, the Column Type (character, date, or numeric) and the Width of each column.

SPREADSHEET 4.1 – DATASET: This spreadsheet is to be named DTST.WK1

Column

Column Header

Column Description

Data Length

Type

*A)

Directory

Directory Name

8

Alphanumeric

*B)

Desc

Description

40

Alphanumeric

*C)

SRP ID
(SRP ID)

Site Remediation Program Identification

16

Alphanumeric

*D)

Consultant

Consultant

40

Alphanumeric

E)

Phase

Remedial Phase

12

Alphanumeric

F)

Status

Status of Dataset

10

Alphanumeric

G)

Transmit

Transmittal Method

1

Alphanumeric

*H)

Submit Date
(Submit Date)

Submittal Date

8

Date

I)

Pack Num
(Pack Num)

Package Number

2

Numeric

 

KEY: (Mandatory fields)
*  If not completed, resubmission will be required.

NOTE: Exact Column Headers and Data Lengths are needed so that the information can be accurately inserted into the existing SRP database. If the submitted names or widths are not compatible with the SRP database, the information may not be transferred accurately to the SRP database, and could result in data loss. In such cases, the file would be rejected.

An example of a DTST.WK1 is provided below (Note: this example file was prepared in Microsoft Excel, but saved as a .WK1 file).

Example of DTST.WK1 Worksheet Screen
Example of DTST.WK1 Worksheet Screen

 

SAMPLE File

The second file you will create is the SAMPLE file. The Sample file includes the date and location of the sample and other important information as shown in the spreadsheet below (Spreadsheet 4.2). Each Sample Record requires a specific number of fields, a Name for each field, the Field Type (character, date, or numeric) and the Width of each field.

The SAMPLE file contains information about each sample collected at a site. The information is roughly equivalent to field notes, and includes: sample number, date, matrix, field identification, location information, etc. There should be one sample record for each sample collected.

A unique sample record is created collectively by the following fields: the SRP ID, the Sample Date, and the Sample Number. Therefore, there can be identical Sample Numbers in a dataset as long as those samples were collected on different dates.

SPREADSHEET 4.2 – SAMPLE: This spreadsheet is to be named HZSAMPLE.WK1

Column

Column Header

Column Description

Data Length

Type

*A)

SRP ID
(SRP ID)

Site Remediation Program Identification

16

Alphanumeric

*B)

Sample Date
(Sample Date)

Sample collection date (MM/DD/YYYY)

8

Date

*C)

Sample Number
(Sample Number)

Unique identification number for sample

7

Alphanumeric

D)

Sample Time
(Sample Time)

Sample collection time

5

Alphanumeric

*E)

Duplicate

Duplicate sample (Yes/No field)

1

Alphanumeric

*F)

Matrix

Acceptable entries defined in section 8.0

15

Alphanumeric

*G)

Field ID
(Field ID)

Field Number for Sample

12

Alphanumeric

H)

AOC ID
 (AOC ID)

Area of Concern (text or number)

16

Alphanumeric

+I)

Lat Degrees
(Lat Degrees)

Latitude Degrees

2

Alphanumeric

+J)

Lat Minutes
(Lat Minutes)

Latitude Minutes

2

Alphanumeric

+K)

Lat Seconds
(Lat Seconds)

Latitude Seconds

7

Alphanumeric

+L)

Lon Degrees
(Lon Degrees)

Longitude Degrees

3

Alphanumeric

+M)

Lon Minutes
(Lon Minutes)

Longitude Minutes

2

Alphanumeric

+N)

Lon Seconds
 (Lon Seconds)

Longitude Seconds

7

Alphanumeric

+O)

SP X-Coord
 (SP X-Coord)

State Plane X Coordinate

14

Alphanumeric

+P)

SP Y-Coord
(SP Y-Coord)

State Plane Y Coordinate

14

Alphanumeric

*Q)

Depth Top
(Depth Top)

Depth at top of sample (ft)

6

Alphanumeric

R)

Depth Botm
(Depth Botm)

Depth at bottom of sample (ft)

6

Alphanumeric

S)

Ground Elev
(Ground Elev)

Ground Elevation (ft)

6

Alphanumeric

T)

Well Elev
(Well Elev)

Well Elevation (ft)

6

Alphanumeric

*U)

Sample Type
(Sample Type)

Acceptable entries defined in section 8.0

15

Alphanumeric

*V)

Date to Lab
(Date to Lab)

Date sample was delivered to lab

8

Date

W)

Sample Meth
(Sample Meth)

Field sampling method

15

Alphanumeric

X)

Sample Note
(Sample Note)

Notes about Sample Method or Type

10

Memo

Y)

Submit Date
(Submit Date)

Date package submitted to SRP

8

Date

Z)

QAQC

Leave Blank, NJDEP use only

1

Alphanumeric

 

KEY: (Mandatory fields)
*  If not completed, resubmission will be required.
+  Either the complete Latitude and Longitude OR State Plane X, Y Coordinates must be submitted for each environmental sample collected.

NOTE: Exact Column Headers and Data Lengths are needed so that the information can be accurately inserted into the existing SRP database. If the submitted names or widths are not compatible with the SRP database, the information may not be transferred accurately to the SRP database, and could result in data loss. In such cases, the file would be rejected.

Shown below is an example of HZSAMPLE.WK1 spreadsheet and some of the fields that are required (Note: the file was prepared in Microsoft Excel, but saved as a .WK1 file).

Example of HZSAMPLE.WK1 Worksheet Screen
Example of HZSAMPLE.WK1 Worksheet Screen

 

RESULT File

The third (last) file you will create is the RESULT file. This spreadsheet includes the sample date, result type and analytical concentration of the sample and other important information as shown in the Results spreadsheet below.

The RESULT file contains the result of the analysis of the sample. The Result file includes the Sample Number, Sample Date, Lab ID, the name of the analyte or parameter, the concentration of the result, QA Qualifier, Method Detection Limit, etc. Each compound analyzed for in each sample collected requires a Result record.

SPREADSHEET 4.3 – RESULT: This spreadsheet is to be named HZRESULT.WK1

Column

Column Header

Column Description

Data Length

Type

*A)

SRP ID
(SRP ID)

Site Remediation Program Identification

16

Alphanumeric

*B)

Sample Date
(Sample Date)

Sample Collection Date (MM/DD/YYYY)

8

Date

*C)

Sample Number
(Sample Number)

Unique identification number for sample

7

Alphanumeric

*D)

Lab ID
(Lab ID)

Lab Number for Sample

12

Alphanumeric

*E)

Date Analy
(Date Analy)

Laboratory Analysis Date

8

Date

F)

Lab Name
(Lab Name)

Name of Lab performing analysis

20

Alphanumeric

*G)

Lab Cert Num
(Lab Cert Num)

NJDEP Lab Certification Number

5

Alphanumeric

*H)

Result Type
(Result Type)

A for Analyte, P for Parameter, T for TIC

1

Alphanumeric

*I)

AnaltParam

Identify Analyte, Parameter or TIC name

60

Alphanumeric

*J)

CAS

CAS number for Analyte

15

Alphanumeric

K)

Filt/Unfilt

Indicate F for Filtered, U for Unfiltered

1

Alphanumeric

*L)

Conc

Concentration resulting from Analysis

12

Alphanumeric

*M)

Units

Concentration Units, ppm, ppb, or text

15

Alphanumeric

N)

QA Qualifier
(QA Qualifier)

Quality Assurance qualifiers - See List 4.3

7

Alphanumeric

^O)

MDL

Method Detection Limit

12

Alphanumeric

^P)

Quant Type
(Quant Type)

Quantitation Type (PQL, CRDL, CRQL)

8

Alphanumeric

^Q)

Quant Level
(Quant Type)

Value of PQL, CRDL, CRQL, etc.)

12

Alphanumeric

*R)

Anlys Mthd
(Anlys Mthd)

Analysis Method, EPA (or other)

15

Alphanumeric

S)

QAQC

Leave blank, NJDEP use only

1

Alphanumeric

 

KEY: (Mandatory fields)
*  If not completed, resubmission will be required.
^  Either MDL OR Quant Type and Quant Level.

NOTE: Exact Column Headers and Data Lengths are needed so that the information can be accurately inserted into the existing SRP database. If the submitted names or widths are not compatible with the SRP database, the information may not be transferred accurately to the SRP database, and could result in data loss. In such cases, the file would be rejected.

Next