In Reply Refer To:

Mail Stop 412 September 28, 2006

####### **Office of Water Quality Technical Memorandum 2006.04**

Subject: Data Bases\--Electronic Transmission of Analytical Laboratory
Data for Entry into\
QWDATA Release 4.6

The Water Resources Discipline (WRD) of the U.S. Geological Survey
(USGS) collects a vast array of samples from numerous media (water,
sediment, biological tissue, etc.), which are analyzed at various
laboratories. The Water-Quality System (QWDATA) of the National Water
Information System (NWIS) is the primary, official repository of all
discrete sample data collected by WRD. The National Water Quality
Laboratory (NWQL) allows for electronic transmission and loading of
analytical data into QWDATA. Data transmissions for numerous other
laboratories, including both internal USGS labs (Geologic Discipline,
Biological Discipline, WRD research labs, Water Science Center labs) and
external (university, other government agency, and commercial) labs are
in a variety of paper and electronic formats, and some of these
laboratories have begun to provide data on formats compatible with
QWDATA. The purpose of this memorandum is to provide guidance for the
electronic transmission of analytical data into QWDATA for these other
laboratories.

Prior to the QWDATA 4_1 Release, the batch format (1- and \*-cards) for
QWDATA was difficult for laboratories to produce. This has had several
undesirable consequences. These include: (1) data not being entered into
QWDATA; (2) data being incompletely or incorrectly entered into QWDATA
(desired fields are not populated; transcription errors, etc.); (3)
Water Science Center (WSC) users and database administrators
independently inventing methods to manage data; and (4) WSC users and
database administrators spending an inordinate amount of time entering
data into QWDATA by hand. The QWDATA 4_1 Release provided a new
tab-delimited batch format for electronic transmission of data.

The NWQL and selected USGS WRD laboratories now provide data to QWDATA
using the tab-delimited format. Continued incremental improvements to
QWDATA have resulted in new sample- and result-level fields that have
been added to this batch file format. This memo documents the current
(2006) batch file format and provides reference documents that will
assist laboratories that produce batch file format data for entry into
QWDATA. The "1- and-\* card" format is not addressed in this memo
because its use is being phased out because this format cannot be used
to enter data for all sample- and result-level fields. For additional
information, users can reference the QWDATA User's Manual available
on-line at:
<http://wwwnwis.er.usgs.gov/currentdocs/qw/QW.user.book.html>

Attachments to this memo provide information on the QWDATA 4_6 Release
batch format. Attachment 1 is designed to be an independent document
that can be provided directly to laboratories that will be producing the
batch files. References to internal documents are not included in this
attachment. Attachment 2 is for internal WRD users and provides more
detail on the batch processing. It includes references to internal
documents and QWDATA software operations. Users will need to provide
information for selected attributes to laboratories because all
documentation on QWDATA processing is not available to the public. This
memo and attachments are available on-line (internal USGS only) at:
<http://phoenix.cr.usgs.gov/training/training.html>. Attachments 1 and 2
will be updated in the future as needed to reflect future batch file
format changes.

Questions can be emailed to <phoenix@usgs.gov>.

> Timothy L. Miller
>
> Chief, Office of Water Quality

This memorandum supersedes Office of Water Quality Technical Memorandum
2002.06.

Distribution: All WRD Employees

####### **Attachment 1. QWDATA tab-delimited batch-file format**

Data transmittal for automated data loading into the U.S. Geological
Survey's Water-Quality System (QWDATA) is done through the use of batch
files. The batch processing utilizes two files: a sample-level batch
file and a result-level batch file. In general, the sample-level file
contains information about the sample-collection activity, such as the
site identification number, sample collection date and time, and sample
medium. A sample collection activity may produce multiple bottles that
are sent to laboratories for different analytical requests. The
result-level file contains information about the individual analytical
results. One row in the result table is for the analytical result for
one chemical constituent. The relationship between the two-files is
one-to-many; that is, a row of sample-level data may be related to many
rows of result-level data.

The batch-format files are tab-delimited ASCII text files that require
specific information to be displayed in specific fields. Each attribute
must be separated from the previous attribute with a \<tab\>. Missing
attributes must have a \<tab\> inserted. All fields are left justified.
Padding with blank characters to create fields with full-length entries
is not necessary. The files are related through a surrogate primary key
called the sample integer (SINT). The description of the format of the
sample-level and result-level files follows.

######## Sample-level batch file: 

The format of the sample-level batch file is defined in table 1. The
tab-delimited format is designed to accommodate 22 attributes for a row
of data. Many of these attributes are specific to field-collection
activities and are not required for the batch processing of laboratory
data. Therefore, these attributes do not need to be stored in a
laboratory's database and will not have values transmitted in the batch
files. However, the positions must be held in the file, so a \<tab\>
must be inserted to separate these fields. Mandatory fields that the
laboratory must supply in the batch file are indicated. The file
containing the sample information is usually named '**qwsample'**.

Table 1: Sample-level batch file format

+------+----------------+---------------------+--------------+--------+
| **Co | **Attribute    | **Description**     | **Attribute  | **Man  |
| lumn | name**         |                     | Type\        | datory |
| ord  |                |                     | (maximum     | field  |
| er** |                |                     | length, or   | for    |
|      |                |                     | required     | batch  |
|      |                |                     | format)**    | file** |
+------+----------------+---------------------+--------------+--------+
| 1    | Sample Integer | Integer used to     | Integer(18)  | Yes    |
|      |                | link sample and     |              |        |
|      | (SINT)         | result information  |              |        |
|      |                | between the two     |              |        |
|      |                | batch files         |              |        |
+------+----------------+---------------------+--------------+--------+
| 2    | user code      | \-\--               |              |        |
+------+----------------+---------------------+--------------+--------+
| 3    | agency_cd      | Agency code         | Char(5)      | Yes    |
+------+----------------+---------------------+--------------+--------+
| 4    | site_no        | Station             | Char(15)     | Yes    |
|      |                | identification      |              |        |
|      |                | number              |              |        |
+------+----------------+---------------------+--------------+--------+
| 5    | s              | Sample start date   | Date         | Yes    |
|      | ample_start_dt | and time            | (y           |        |
|      |                |                     | yyymmddhhmm) |        |
+------+----------------+---------------------+--------------+--------+
| 6    | sample_end_dt  | Sample end date and | Date         |        |
|      |                | time                | (y           |        |
|      |                |                     | yyymmddhhmm) |        |
+------+----------------+---------------------+--------------+--------+
| 7    | medium_cd      | Medium code         | Char (1)     | Yes    |
+------+----------------+---------------------+--------------+--------+
| 8    | lab_no         | Lab identification  | Char (**7**) |        |
|      |                | number              |              |        |
+------+----------------+---------------------+--------------+--------+
| 9    | project_cd     | Project code        | Char (9)     |        |
+------+----------------+---------------------+--------------+--------+
| 10   | aqfr_cd        | Aquifer code        | Char (8)     |        |
+------+----------------+---------------------+--------------+--------+
| 11   | samp_type_cd   | Sample type         | Char (1)     |        |
+------+----------------+---------------------+--------------+--------+
| 12   | anl_stat_cd    | Analysis status     | Char (1)     |        |
+------+----------------+---------------------+--------------+--------+
| 13   | anl_src_cd     | Analysis source     | Char (1)     |        |
+------+----------------+---------------------+--------------+--------+
| 14   | hyd_cond_cd    | Hydrologic          | Char (1)     |        |
|      |                | condition           |              |        |
+------+----------------+---------------------+--------------+--------+
| 15   | hyd_event_cd   | Hydrologic event    | Char (1)     |        |
+------+----------------+---------------------+--------------+--------+
| 16   | tu_id          | Tissue sample       | Integer      |        |
|      |                | identifier          |              |        |
+------+----------------+---------------------+--------------+--------+
| 17   | body_part_id   | Body part code      | Integer      |        |
+------+----------------+---------------------+--------------+--------+
| 18   | la             | Lab sample comment  | Varchar      |        |
|      | b_sample_cm_tx |                     | (300)        |        |
+------+----------------+---------------------+--------------+--------+
| 19   | fiel           | Field sample        | Varchar      |        |
|      | d_sample_cm_tx | comment             | (300)        |        |
+------+----------------+---------------------+--------------+--------+
| 20   | tz_cd          | Time-zone code      | Char (6)     |        |
+------+----------------+---------------------+--------------+--------+
| 21   | tm_d           | Time-datum          | Char (1)     |        |
|      | atum_rlblty_cd | reliability code    |              |        |
+------+----------------+---------------------+--------------+--------+
| 22   | coll_ent_cd    | Collecting agency   | Char(8)      |        |
|      |                | code                |              |        |
+------+----------------+---------------------+--------------+--------+

Further information for the sample-level attributes follows:

**Column 1: Sample integer (Mandatory, Primary Key)**

The laboratory must generate the sample integer. This integer is
critical for linking the sample-level data to the result-level data. The
maximum length for this integer is 18 digits and they must be in
sequential order in the file.

**Column 2: User code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 3: Agency code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 4: Station number (Mandatory)**

Station number is an 8-digit or 15-digit number assigned to a sampling
site. The field contains only numeric characters. The USGS personnel
must supply the station number to the laboratory when samples are
submitted for analysis.

**Column 5: Begin date and time (Mandatory)**

Begin date and begin time describe when the sample was collected (or the
begin time for a sample that is collected over a range of dates and/or
times). The date format is yyyymmdd. The time format is hhmm. These
fields are concatenated together (not separated by a \<tab\>). The USGS
personnel must supply this information to the laboratory when samples
are submitted for analysis.

**Column 6: End date and end time**

The end date is the end date of a sample that is collected or over a
range of dates. The format is yyyymmdd. The end time is the end time of
a sample that is collected over a range of dates and/or times. The
format is hhmm. These fields are concatenated together (not separated by
a \<tab\>). The end date and time are usually blank. If it is not blank,
the USGS personnel must supply this information to the laboratory when
samples are submitted for analysis.

**Column 7: Medium code (Mandatory)**

Medium code is the medium from which the sample was collected (i.e.
surface water, ground water, sediment, tissue). This field has a valid
domain in the QWDATA software. The USGS personnel must supply a valid
value for the medium code to the laboratory when samples are submitted
for analysis.

**Column 8: Lab number**

This field is reserved for laboratory use. Lab number is the
analytical-laboratory identification number given to a bottle or group
of bottles for samples that are received together in a shipment. The
field can contain numeric and alpha characters.

**Column 9: Project number**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 10: Aquifer code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 11: Sample type**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 12: Analysis status**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 13: Hydrologic condition**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 14: Hydrologic event**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 15: Analysis source**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 16: Tissue sample identifier**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 17: Body part code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 18: Lab sample comment**

Lab sample comment is a free-form text field to hold information from
the lab about a sample that cannot be defined by the coded information
in the system. This field is reserved for laboratory use. Laboratories
can send text up to 300 characters in length. The lab sample comment is
not mandatory. Laboratories sending comments should be aware that
usually only the first 50 characters of the comment are displayed.

**Column 19: Field sample comment**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 20: Time-zone code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 21: Time-datum reliability code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 22: Sample collecting agency code**

The sample collecting agency is a code identifying the agency primarily
responsible for collecting the sample. This field has a valid domain in
the QWDATA software. The USGS personnel must supply a valid collecting
agency code to the laboratory when samples are submitted for analysis or
the default code will be automatically entered when the data are loaded
into QWDATA.

**[Result-level batch file:]{.underline}**

The result-level file includes analytical results and metadata for each
analytical result. The tab-delimited format is designed to accommodate
20 attributes for a row of data. Mandatory fields that the laboratory
must supply in the batch file are indicated. The format of the
result-level batch file is defined in table 2. The file containing the
results information is usually named '**qwresult'**.

Table 2. Result-level batch file format

+------+-------------+-----------------------+-------------+---------+
| **Co | **Column    | **Description**       | **Format**  | **Ma    |
| lumn | name**      |                       |             | ndatory |
| ord  |             |                       |             | field   |
| er** |             |                       |             | for     |
|      |             |                       |             | batch   |
|      |             |                       |             | file**  |
+------+-------------+-----------------------+-------------+---------+
| 1    | Sample      | Integer used to link  |             | Yes     |
|      | Integer     | sample and result     | I           |         |
|      |             | information between   | nteger---up |         |
|      | (SINT)      | the two batch files   | to 18       |         |
|      |             |                       | digits      |         |
+------+-------------+-----------------------+-------------+---------+
| 2    |             | Parameter code        | Char (5)    | Yes     |
|      | p           |                       |             |         |
|      | arameter_cd |                       |             |         |
+------+-------------+-----------------------+-------------+---------+
| 3    | result_va   | Result value          | Float       | Yes     |
+------+-------------+-----------------------+-------------+---------+
| 4    | remark_cd   | Remark code           | Char (1)    | Cond    |
|      |             |                       |             | itional |
+------+-------------+-----------------------+-------------+---------+
| 5    | qa_cd       | Quality-assurance     | Char (1)    |         |
|      |             | code                  |             |         |
+------+-------------+-----------------------+-------------+---------+
| 6    | meth_cd     | Method code           | Char (5)    |         |
+------+-------------+-----------------------+-------------+---------+
| 7    | result_rd   | Rounding code         | Char (1)    |         |
+------+-------------+-----------------------+-------------+---------+
| 8    |             | Value qualifiers      | Char (3)    |         |
|      | val_qual_cd |                       |             |         |
+------+-------------+-----------------------+-------------+---------+
| 9    | rpt_lev_va  | Report level          | Float       | Cond    |
|      |             |                       |             | itional |
+------+-------------+-----------------------+-------------+---------+
| 10   | rpt_lev_cd  | Report level type     | Varchar (6) | Cond    |
|      |             |                       |             | itional |
+------+-------------+-----------------------+-------------+---------+
| 11   | dqi_cd      | Data quality          | Char (1)    |         |
|      |             | indicator             |             |         |
+------+-------------+-----------------------+-------------+---------+
| 12   |             | Null-value qualifier  | Char (1)    | Cond    |
|      | null_       |                       |             | itional |
|      | val_qual_cd |                       |             |         |
+------+-------------+-----------------------+-------------+---------+
| 13   |             | Preparation set       | Char (12)   |         |
|      | prep_set_no | number                |             |         |
+------+-------------+-----------------------+-------------+---------+
| 14   | anl_set_no  | Analytical set number | Char (12)   |         |
+------+-------------+-----------------------+-------------+---------+
| 15   | anl_dt      | Analysis date         | Date        |         |
|      |             |                       | (yyyymmdd)  |         |
+------+-------------+-----------------------+-------------+---------+
| 16   | prep_dt     | Preparation date      | Date        |         |
|      |             |                       | (yyyymmdd)  |         |
+------+-------------+-----------------------+-------------+---------+
| 17   |             | Laboratory result     | Varchar     |         |
|      | r           | comment               | (300)       |         |
|      | esult_cm_tx |                       |             |         |
|      | (where      |                       |             |         |
|      | r           |                       |             |         |
|      | esult_cm_tp |                       |             |         |
|      | = L)        |                       |             |         |
+------+-------------+-----------------------+-------------+---------+
| 18   |             | Field result comment  | Varchar     |         |
|      | r           |                       | (300)       |         |
|      | esult_cm_tx |                       |             |         |
|      | (where      |                       |             |         |
|      | r           |                       |             |         |
|      | esult_cm_tp |                       |             |         |
|      | = F)        |                       |             |         |
+------+-------------+-----------------------+-------------+---------+
| 19   | lab         | Laboratory standard   | Float       |         |
|      | _std_dev_va | deviation             |             |         |
+------+-------------+-----------------------+-------------+---------+
| 20   | anl_ent_cd  | Analyzing entity code | Char (8)    |         |
+------+-------------+-----------------------+-------------+---------+

**Column 1: Sample integer (Mandatory)**

The laboratory must generate the sample integer. This integer is
critical for linking the result-level data to the sample-level data. The
maximum length for this integer is 18 digits and the integers must be in
sequential order in the file.

**Column 2: Parameter code (Mandatory)**

Each analytical result value must be reported with a parameter code that
defines the constituent name, phase, reporting form, and reporting
units. USGS personnel will provide a look-up table for use by the
laboratory for the needed parameter codes.

**Column 3: Result value (Mandatory)**

The result value is a numeric value produced by an analytical method for
a constituent. The field is stored as float. The '#' sign can be used to
tell the software that the value is "null" (missing). The laboratory
must supply a null value qualifier code or a null value remark code (M,
N, or U) if the laboratory sends a null value.

**Column 4: Remark code (Only mandatory if result value is null and
null-value qualifier code is not supplied)**

Remark codes provide additional information about the magnitude (or
absence) of a value. A remark code is not required on every value; it is
only required where it is needed to avoid misinterpretation of a value.
If a remark is supplied, the remark code is viewed with the value for
publication or data dissemination. If the laboratory chooses to transmit
null values, the laboratory must also provide a null value remark code
(M, N, U) or Null-value qualifier code to identify why the value is
missing. The remark codes are case sensitive. The remark codes are
listed in table 3.

Table 3: Remark codes

  ---------- -----------------------------------------------------------------
  **Code**   **Description**

  \<         Actual value is known to be less than the value shown.

  \>         Actual value is known to be greater than the value shown.

  E          Value is estimated

  A          Value is an average

  V          Analyte was detected in both the environmental sample and the
             associated blanks.

  S          Most probable value.

  **Null     
  Value      
  Remark     
  Codes**    

  M          Presence of material verified but not quantified.

  N          Presumptive evidence of presence of material.

  U          Material specifically analyzed for but not detected.
  ---------- -----------------------------------------------------------------

**Column 5: Quality-assurance code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 6: Method codes**

An analytical method can be coded in the database by use of a
five-character alphanumeric code in the method code field. The USGS
personnel must supply the laboratory with a look-up table of the
parameter code-method code pair that defines the chemical constituent
and analytical method. A blank method code is allowed. When reported,
method codes are all upper case. **Note:** The laboratory may need to
ask the USGS contact to request a new code if an appropriate code does
not exist in NWIS.

**Column 7: Result rounding code**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 8: Value-qualifier codes**

Value-qualifier codes provide additional qualifying information about
the value. Up to three value qualifiers can be stored with any single
result. The value qualifiers are case sensitive (all lower case). If a
laboratory supplies more than one value qualifier code, the individual
codes are concatenated (not separated by \<tab\>s). Value-qualifier
codes are not mandatory. The value- qualifier codes are listed in table
4.

Table 4. Value-qualifier codes

  ------------------- ---------------------------------------------------------------
  **Value-qualifier   **Description**
  codes**             

                      **Raised reporting Level**

  d                   Diluted sample: method high range exceeded

  q                   Insufficient sample received

  s                   Instrument sensitivity problem

  x                   Analyte interference from environmental sample matrix

                      **Method Problems**

  a                   Value was extrapolated above highest calibration standard,
                      method range, or instrument linear range.

  b                   Value was extrapolated below lowest calibration standard,
                      method range, or instrument linear range

  f                   Sample field preparation problem. Problem described in result
                      comment.

  i                   Result may be affected by interference(s).

  l                   Sample lab preparation problem. Problem described in result
                      comment.

  m                   Highly variable compound using this method, questionable
                      precision and (or) accuracy. Citation of OFR or NWQL Technical
                      Memo in result comment.

  n                   Below the laboratory reporting level and above the long-term
                      method detection level.

  o                   Result determined by alternate method. Reason described in
                      result comment.

  t                   Below the long-term MDL.

  w                   High variability: questionable precision and (or) accuracy.
                      Cause explained in result comment.

                      **Rerun**

  h                   Compound identification verified by rerun using a different
                      method; Alternate method identified in result comments.

  p                   Value reported is preferred; explanation in result comments

  r                   Quantification verified by rerun using the same method

  u                   Value reported not confirmable due to interference

  y                   Sample variability described in result comments.

  z                   Quantification verified by rerun using a different method

                      **Other**

  \+                  Improper preservation.

  @                   Holding-time violation.

  \*                  Sample warm when received.

  c                   See laboratory comments for this result

  e                   See field comments for this result

  v                   Analyte detected in laboratory blank

  \$                  Used incorrect bottle type as requested

                      **Biological**

  &                   Biological organism estimated as dominant

  g                   Biological organism count less than 0.5 percent; may be only
                      observed.

  j                   Biological organism count greater than or equal to 15 percent
                      (dominant)

  k                   Results based upon colony counts outside the acceptable range
  ------------------- ---------------------------------------------------------------

**Column 9: Report level (Only mandatory if report level type code is
populated)**

The report level is associated with the analytical method. This is
usually the minimum value that is reported for the laboratory method at
the time the sample is analyzed, based on some sort of statistical
analysis of the method. If a value falls above this value, usually the
value is reported without a remark code. If a report level is included
in a batch file, a report-level type code must also be present.

**Column 10: Report-level type code (Only mandatory if report level is
populated)**

The report-level type code identifies the type of report level used for
the method that is associated with the report level. If a report level
type code is included in a batch file, a report level must also be
present. The domain for the report level type code is shown in table 5.

Table 5. Report level type codes

  ---------------- ------------------ ---------------------------------------------
  **Report-Level   **Definition**     **Description**
  Type Code**                         

  MRL              Minimum Reporting  Smallest measured concentration of a
                   Level              constituent that can be reliably measured
                                      using a given analytical method.

  MDL              Method Detection   Minimum concentration of a substance that can
                   Limit              be measured and reported with a 99%
                                      confidence that the analyte concentration is
                                      greater than zero. It is determined from the
                                      analysis of a sample in a given matrix
                                      containing the analyte.

  LT-MDL           Long-Term Method   A detection level derived by determining the
                   Detection Limit    standard deviation of a minimum of 24 MDL
                                      spike sample measurements over an extended
                                      period of time. LT-MDL data are collected on
                                      a continuous basis to assess year-to-year
                                      variations in the LT-MDL.

  LRL              Laboratory         Equal to twice the yearly-determined LT-MDL.
                   Reporting Level    At the LRL, the probability of a false
                                      negative is less than or equal to 1 percent.

  IRL              Interim Reporting  A temporary reporting level used for new or
                   Level              custom schedules when LT-MDL data are
                                      unavailable and a LRL has not yet been
                                      established.

  SSMDC            Sample-Specific    A reporting level that varies for each
                   Minimum Detectable sample, primarily used in radiochemical
                   Concentration      analyses. Radiochemical measurements are
                                      typically not censored by the laboratory.

  Blank            \-\--              A blank report-level code should only be
                                      stored when no reporting level is stored. If
                                      a report level value is entered, a report
                                      level code must also be stored.
  ---------------- ------------------ ---------------------------------------------

 

**Column 11: Data-quality indicator code**

Nothing is required for this field, but it must be separated with a
\<tab\>. Laboratories may supply one of the following DQI codes: 'S' =
presumed satisfactory, 'U' = research or unapproved method or
laboratory, or 'I' = awaiting review. If nothing is supplied, then 'S'
will be used.

**Column 12: Null-value qualifier code (Only mandatory if result value
is null and a null value remark code is not supplied)**

The laboratory has the ability to transmit null results for a given
parameter code. A null result means that a requested analytical
procedure was not performed or the result was not quantified. If the
laboratory chooses to transmit null values, the laboratory must also
provide a null-value qualifier code or a null value remark code (M, N,
U, Table 3) to identify why the value is missing. The one-character code
is case sensitive (lower case). The domain for the null-value qualifier
code is listed in table 6.

> Table 6: Null-Value Qualifiers

+-----------------+----------------------------------------------------+
| **Null-Value**  | **Description**** **                               |
|                 |                                                    |
| **Qualifiers**  |                                                    |
+-----------------+----------------------------------------------------+
| a               | Planned measurement was not made                   |
+-----------------+----------------------------------------------------+
| b               | Sample broken/spilled in shipment                  |
+-----------------+----------------------------------------------------+
| c               | Sample lost in lab                                 |
+-----------------+----------------------------------------------------+
| e               | Required equipment not functional or available     |
+-----------------+----------------------------------------------------+
| f               | Sample discarded: improper filter used             |
+-----------------+----------------------------------------------------+
| i               | Required sample type not received                  |
+-----------------+----------------------------------------------------+
| l               | Analysis discarded: low surrogate recovery         |
+-----------------+----------------------------------------------------+
| m               | Results sent by separate memo                      |
+-----------------+----------------------------------------------------+
| o               | Insufficient amount of water                       |
+-----------------+----------------------------------------------------+
| p               | Sample discarded: improper preservation            |
+-----------------+----------------------------------------------------+
| q               | Sample discarded: holding time exceeded            |
+-----------------+----------------------------------------------------+
| r               | Sample ruined in preparation                       |
+-----------------+----------------------------------------------------+
| u               | Unable to determine due to interference.           |
+-----------------+----------------------------------------------------+
| w               | Sample discarded: warm when received.              |
+-----------------+----------------------------------------------------+
| x               | Failed quality-assurance review.                   |
+-----------------+----------------------------------------------------+

**Column 13: Preparation set identifier**

Preparation set identifier is a field used to store the set
identification code of the preparation set at the laboratory. The field
can be variable in length, up to a maximum of 12 characters. The field
can contain mixed numeric and alpha characters. The preparation set
identifier is not mandatory.

**Column 14: Analysis set identifier**

Analysis set identifier is a field used to store the set identification
code of the analysis set at the laboratory. The field can be variable
length, up to a maximum of 12 characters. The field can contain mixed
numeric and alpha characters. The analysis set identifier is not
mandatory.

**Column 15: Analysis date**

The analysis date field is used to identify the date when an analysis
was performed at the laboratory. The format of the date is yyyymmdd. The
analysis date is not mandatory.

**Column 16: Preparation date**

The preparation date field is used to identify the date when a sample
was prepared for analysis at the laboratory. The format of the date is
yyyymmdd. The preparation date is not mandatory.

**Column 17: Laboratory result comment**

Result lab comment is a free-form text field to hold information from
the lab about a result that cannot be defined by the coded information
in the system. This field is reserved for laboratory use. Laboratories
can send text up to 300 characters in length. The result lab comment is
not mandatory.

**Column 18: Field result comment**

Nothing is required for this field, but it must be separated with a
\<tab\>.

**Column 19: Laboratory standard deviation**

Not mandatory. If present, must be numeric and greater than zero.
Provides the laboratory standard deviation associated with the method of
analysis at the time the measurement was made. (Laboratory standard
deviation typically varies over time and concentration range.) In
practice, this value is an estimate of laboratory standard deviation,
since the true standard deviation is always unknown. Three significant
digits are recommended, with a minimum of two significant digits.

**Column 20: Analyzing entity**

The agency, organization, group, or company that analyzes the result is
known as the analyzing entity. This is an eight-character field used to
identify the entity that analyzed the value for the parameter and method
code listed. The USGS personnel must supply the laboratory with the
appropriate code for the analyzing entity. This code is not mandatory,
but it is highly recommended.

The following examples are provided to illustrate how the sample-level
batch file (table 7) and the result-level batch file (table 8) look with
test data populated in the fields.

######### Table 7: Sample-level batch file format example

<table>
<colgroup>
<col style="width: 5%" />
<col style="width: 3%" />
<col style="width: 3%" />
<col style="width: 8%" />
<col style="width: 7%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 2%" />
<col style="width: 3%" />
<col style="width: 3%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 3%" />
<col style="width: 2%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 3%" />
<col style="width: 4%" />
<col style="width: 6%" />
</colgroup>
<tbody>
<tr class="odd">
<td><strong>Sample integer (SINT)</strong></td>
<td><strong>User code</strong></td>
<td><strong>Agency code</strong></td>
<td><strong>Site number</strong></td>
<td><strong>Sample begin date/time</strong></td>
<td><strong>Sample end date/<br />
time</strong></td>
<td><strong>Medium code</strong></td>
<td><p><strong>Lab Number</strong></p></td>
<td><strong>Project code</strong></td>
<td><strong>Aquifer code</strong></td>
<td><strong>Sample Type code</strong></td>
<td><strong>Analysis status code</strong></td>
<td><strong>Analysis source code</strong></td>
<td><strong>Hydro-logic condition code</strong></td>
<td><strong>Hydro-logic event code</strong></td>
<td><strong>Tissue ID</strong></td>
<td><strong>Body part code</strong></td>
<td><strong>Lab sample comment</strong></td>
<td><strong>Field sample comment</strong></td>
<td><strong>Time-zone datum</strong></td>
<td><strong>Time datum reliability</strong></td>
<td><strong>Sample collecting agency</strong></td>
</tr>
<tr class="even">
<td>0200100376</td>
<td></td>
<td></td>
<td>462448104303901</td>
<td>200105211000</td>
<td></td>
<td>6</td>
<td>0640017</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>Sample water turbid.</td>
<td></td>
<td></td>
<td>K</td>
<td>USGS-WRD</td>
</tr>
<tr class="odd">
<td>0200100945</td>
<td></td>
<td></td>
<td>06334630</td>
<td>200106041200</td>
<td></td>
<td>9</td>
<td>0640024</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td> </td>
<td></td>
<td></td>
<td>K</td>
<td>USGS-WRD</td>
</tr>
<tr class="even">
<td>0200100946</td>
<td></td>
<td></td>
<td>06334630</td>
<td>200106041200</td>
<td></td>
<td>C</td>
<td>0640024</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td> </td>
<td> </td>
<td></td>
<td>K</td>
<td>USGS-WRD</td>
</tr>
</tbody>
</table>

 

Table 8: Result-level batch file format example 

 

<table>
<colgroup>
<col style="width: 7%" />
<col style="width: 5%" />
<col style="width: 3%" />
<col style="width: 4%" />
<col style="width: 2%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 4%" />
<col style="width: 3%" />
<col style="width: 3%" />
<col style="width: 2%" />
<col style="width: 4%" />
<col style="width: 5%" />
<col style="width: 7%" />
<col style="width: 5%" />
<col style="width: 5%" />
<col style="width: 5%" />
<col style="width: 4%" />
<col style="width: 5%" />
<col style="width: 7%" />
</colgroup>
<tbody>
<tr class="odd">
<td><p><strong>Sample integer</strong></p>
<p><strong>(SINT)</strong></p></td>
<td><strong>Parameter code</strong></td>
<td><strong>Result value</strong></td>
<td><strong>Remark code</strong></td>
<td><strong>QA code</strong></td>
<td><strong>QW method code</strong></td>
<td><strong>Rounding code</strong></td>
<td><strong>Value qualifiers</strong></td>
<td><strong>Report level</strong></td>
<td><strong>Report level code</strong></td>
<td><strong>DQI code</strong></td>
<td><strong>Null value qualifier code</strong></td>
<td><strong>Preparation set number</strong></td>
<td><strong>Analytical set number</strong></td>
<td><strong>Analysis date</strong></td>
<td><strong>Preparation date</strong></td>
<td><strong>Laboratory result comment</strong></td>
<td><strong>Field result comment</strong></td>
<td><strong>Laboratory<br />
standard<br />
deviation</strong></td>
<td><strong>Analyzing entity</strong></td>
</tr>
<tr class="even">
<td>0200100376</td>
<td>00940</td>
<td>18</td>
<td></td>
<td></td>
<td>IC022</td>
<td></td>
<td></td>
<td>0.08</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200114801</td>
<td>AKTO01150A</td>
<td>20010530</td>
<td>20010528</td>
<td></td>
<td></td>
<td>10.1</td>
<td>USGSNWQL</td>
</tr>
<tr class="odd">
<td>0200100376</td>
<td>00945</td>
<td>170</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>0.11</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200114801</td>
<td>AKTO01150A</td>
<td>20010530</td>
<td>20010528</td>
<td>Instrument run by KRM</td>
<td> </td>
<td>51.2</td>
<td>USEPA</td>
</tr>
<tr class="even">
<td>0200100376</td>
<td>01020</td>
<td>400</td>
<td></td>
<td></td>
<td>IP107</td>
<td></td>
<td></td>
<td>13</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200114801</td>
<td>AKTO01150A</td>
<td>20010530</td>
<td>20010528</td>
<td> </td>
<td> </td>
<td>202.</td>
<td>USGSNWQL</td>
</tr>
<tr class="odd">
<td>0200100945</td>
<td>00631</td>
<td>0.020</td>
<td></td>
<td></td>
<td>COL41</td>
<td></td>
<td></td>
<td>0.005</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200115903</td>
<td>1200101162A</td>
<td>20010611</td>
<td>20010608</td>
<td> </td>
<td> </td>
<td>0.0189</td>
<td>USGSNWQL</td>
</tr>
<tr class="even">
<td>0200100945</td>
<td>00666</td>
<td>0.06</td>
<td>&lt;</td>
<td></td>
<td>KJ005</td>
<td></td>
<td>s</td>
<td>0.06</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200115903</td>
<td>1200101162A</td>
<td>20010611</td>
<td>20010608</td>
<td> </td>
<td> </td>
<td>0.123</td>
<td>USGSNWQL</td>
</tr>
<tr class="odd">
<td>0200100945</td>
<td>00677</td>
<td>0.03</td>
<td></td>
<td></td>
<td>PHM04</td>
<td></td>
<td></td>
<td>0.01</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200115903</td>
<td>1200101162A</td>
<td>20010611</td>
<td>20010608</td>
<td> </td>
<td> </td>
<td>0.0834</td>
<td>USGSNWQL</td>
</tr>
<tr class="even">
<td>0200100946</td>
<td>49258</td>
<td>#</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>0.10</td>
<td>MRL</td>
<td></td>
<td>r</td>
<td>200115903</td>
<td>GCMS162A</td>
<td>20010611</td>
<td>20010608</td>
<td> </td>
<td> </td>
<td></td>
<td>USEPA</td>
</tr>
<tr class="odd">
<td>0200100946</td>
<td>39350</td>
<td>0.2</td>
<td>&lt;</td>
<td></td>
<td>GC096</td>
<td></td>
<td></td>
<td>0.10</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200115903</td>
<td>GCMS162A</td>
<td>20010611</td>
<td>20010608</td>
<td></td>
<td></td>
<td>1.8</td>
<td>USEPA</td>
</tr>
<tr class="even">
<td>0200100946</td>
<td>39371</td>
<td>0.08</td>
<td></td>
<td></td>
<td>GC054</td>
<td></td>
<td>xiz</td>
<td>0.01</td>
<td>MRL</td>
<td></td>
<td></td>
<td>200115903</td>
<td>GCMS162A</td>
<td>20010611</td>
<td>20010608</td>
<td></td>
<td></td>
<td>0.113</td>
<td>USEPA</td>
</tr>
</tbody>
</table>

#### Attachment 2. Additional batch processing information

This attachment describes additional information on batch processing of
files that WRD users should be familiar with when requesting
transmission of electronic data from laboratories. Certain fields are
required in QWDATA that are not required for the laboratory batch file.
If users log samples into QWDATA prior to the batch entry of laboratory
results, then the user can properly code all the required QWDATA fields.
If samples are not logged into QWDATA prior to batch entry of laboratory
results, the QWDATA software will set default values for selected
attributes since laboratories will not be storing or transmitting these
attributes. This section further describes the mandatory fields
identified in Attachment 1 and the defaults for selected attributes that
will be set during batch processing for samples not logged in prior to
batch entry of laboratory data.

**[Sample-level batch file:]{.underline}**

**Sample integer**

This field is used only for batch processing and is not stored in
QWDATA.

**Agency code**

This field will default to 'USGS' for samples not logged in prior to
batch processing of laboratory data.

**Station number**

Station number is the unique 8-digit or 15-digit number that is assigned
to a sampling site. The field contains only numeric characters. The
8-digit numbers are generally downstream order numbers used for
surface-water locations on streams or rivers. The 15-digit numbers are
generally a combination of the latitude, longitude, and sequence number
of the location, most often used for wells. A sequence number is used to
separate locations that are very close to each other. This field must be
supplied to the laboratory when samples are submitted for analysis.

**Begin date and time**

Begin date and begin time describe when the sample was collected (or the
begin time for a sample that is collected over a range of dates and/or
times). This field must be supplied to the laboratory when samples are
submitted for analysis.

**End date and time**

The end date is the end date and end time of a sample that is collected
over a range of dates and/or times. The end date and time are usually
blank. If it is not blank, this field must be supplied to the laboratory
when samples are submitted for analysis.

**Medium code**

Medium code is the medium from which the sample was collected (i.e.
surface water, ground water, sediment, tissue). This field must be
supplied to the laboratory when samples are submitted for analysis.
Different medium codes are used for quality-control samples, than are
used for environmental samples. The most commonly used codes are 6 for
ground water and 9 for surface water. A complete list of the valid
medium codes can be found in Appendix A of the User Manual.

**Project number**

This field will default to blank for samples not logged in prior to
batch processing of laboratory data.

**Aquifer code**

This field will default to blank for samples not logged in prior to
batch processing of laboratory data.

**Sample type**

This field will default to '9' (regular) for samples not logged in prior
to batch processing of laboratory data.

**Analysis status**

This field will default to 'H' (initial entry) for samples not logged in
prior to batch processing of laboratory data.

**Hydrologic condition**

For samples not logged in prior to batch processing, the default for
this field depends on the medium code. If medium code = 9, 1, H, L, M,
N, O, P,R, V, or W, the default is 9; other medium codes are set to X.

**Hydrologic event**

For samples not logged in prior to batch processing, the default for
this field depends on the medium code. This field will default to '9'
(routine) unless the medium code = A, 6, C, D, F, B, E, G, J, 5, Q, S,
X, Y, or Z; these medium codes are set to X.

**Analysis source**

This field will default to '9' (not determined) for samples not logged
in prior to batch processing of laboratory data.

**Tissue sample identifier**

This field will default to '0' (unknown) for tissue samples (medium
codes C, D, X, or Y) not logged in prior to batch processing of
laboratory data.

**Body part code**

This field will default to '94' (unknown) for tissue samples (medium
codes C, D, X, or Y) not logged in prior to batch processing of
laboratory data.

**Field sample comment**

No sample field comment is created during batch processing of laboratory
data.

**Time-zone code**

This field will default to the sample time datum set in the site file.
If the sample-collection time is referenced to a different time datum,
then USGS users must identify the correct time datum in QWDATA.

**Time-datum reliability code**

This field will default to 'K' (Known).

#### 

#### [Result-level batch file:]{.underline}

**Sample integer**

This field is used only for batch processing and is not stored in
QWDATA.

**Parameter code**

Each analytical result must be stored in QWDATA with a parameter code
that defines the constituent name, phase, reporting form, and reporting
units. The Parameter Code Dictionary (PCD) should be used for looking-up
valid parameter codes in QWDATA. The PCD is available through the QWDATA
menu "Support Files" option. The Water Science Center (WSC) users must
work with the laboratory to assign the proper parameter codes for the
analytical data being produced. The WSC user should produce a look-up
table for use by the laboratory for the required parameter codes.

New parameter codes may need to be requested from the Office of Water
Quality for constituents that do not fit within an existing
parameter-code definition (new chemical, different units, etc.). Users
must request parameter codes using the request form at
<http://water.usgs.gov/usgs/owq/parm_request.html>. The PCD should be
searched prior to requesting new parameter codes. Some lead-time is
necessary to obtain new parameter codes because updates are made on a
quarterly schedule.

**Quality-assurance code**

This field will default to 'A' (not determined) during batch processing
of laboratory data.

**Method codes**

An analytical method can be coded in the database by use of a
five-character alphanumeric code in the method code (meth_cd) field.
Each method code assignment is specific to a particular analytical
method. Thus, method 'IC022' for parameter code 00940 is the same as
method 'IC022' for parameter code 00945. The Parameter-Method Table
(PMT) should be used for looking-up valid parameter-method code
combinations in QWDATA. The PMT is available through the QWDATA menu
"Support Files" option. The WSC users must work with the laboratory to
assign the proper parameter and method codes for the analytical data
being produced. Users must submit method codes requests using the
request form at <http://water.usgs.gov/usgs/owq/parm_request.html>.
Until a method code is assigned, laboratories can briefly describe the
method in the "laboratory result comment" field, for example, by use of
shorthand such as "ICP following acid digestion." A blank method code is
allowed, but may cause NWIS to inappropriately round the result value.

**Result rounding code**

This field will default to a rounding code obtained from the
parameter-method table during batch processing. (See also: Tip Sheet 22,
[http://wwwnwis.er.usgs.gov/currentdocs/qw/QW-TipSheet5.22.pdf](http://wwwnwis.er.usgs.gov/currentdocs/qw/QW-TipSheet5.22.pdf )
)

**Data-quality indicator (DQI) code.**

Data-quality indicator (DQI) code indicates the review status of a
result, controls the ability of a batch input program to overwrite a
value, and affects the inclusion of a result in output. If blank, this
field will default to 'S' ("presumed satisfactory") during batch
processing of laboratory data. The DQI for data for analytes from
methods that are under development, such as methods that have not been
approved by the USGS or the USEPA, or data from unapproved laboratories
should be coded from the laboratory as 'U' ("unapproved").

**Field result comment**

No field result comment is created during batch processing of laboratory
data.

**Laboratory standard deviation**

Provides the laboratory standard deviation associated with the method of
analysis at the time the measurement was made. (Laboratory standard
deviation typically varies over time and concentration range.) In
practice, this value is an estimate of laboratory standard deviation,
since the true standard deviation is always unknown. Three significant
digits are recommended, with a minimum of two significant digits.

The laboratory standard deviation can be stored with each result and
will be used to round the result value if it exists. This value is used
with the ASTM E29-93a standard to determine the appropriate rounding for
the result value. To retrieve the lab standard deviation, select a
by-result output format and use the ALPHA parameter code of LSDEV. See
also: Tip Sheet 22,
[http://wwwnwis.er.usgs.gov/currentdocs/qw/QW-TipSheet5.22.pdf](http://wwwnwis.er.usgs.gov/currentdocs/qw/QW-TipSheet5.22.pdf )
.

**Analyzing entity**

The analyzing entity is an optional field that identifies the agency,
organization, group, or company that analyzes the result. Valid
analyzing entity codes can be found in *[Appendix K]{.underline}
<http://wwwnwis.er.usgs.gov/nwisdocs4_6/qw/QW-AppxK.pdf>* , Protocol
Organization Codes and is described in *[Section 2.5.11]{.underline}*
<http://wwwnwis.er.usgs.gov/nwisdocs4_6/qw/QW_Sect2.pdf> of the QWDATA
User Manual. To request a new analyzing entity code, submit requests
using the request form at
<http://water.usgs.gov/usgs/owq/parm_request.html>.