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.
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
|
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.
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
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.
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.
|
Alternate Format of the Entire Guidance Document
Help
for Using Files |
