STORET
STORET Import Module (SIM)
Version 2.0.1
User Guide and
Reference Manual
US Environmental Protection Agency
Updated June 2,2004
Technical Support:
1-800-424-9067
-------
Contents
Introducing SIM v2.0.1
Formatting Your Data
Tutorial I. Defining an Import Configuration
Tutorial II. Using SIM to Migrate Data to STORE!
Tutorial III. Advanced Functions
System Configuration Definitions
SIM v2.0.1 New Features
Appendix A: Listing of Available STORET Data Elements for Each File Type.
Appendix B: SEVI v2.0.1 Example Files
..2
..2
..4
.12
.21
.17
.23
.31
Introducing SIM v2.0.1
The STORET Import Module (SEVI) Version 2.0.1 is a software program that helps users load
data into STORET Version 2.0. STORET is an EPA-maintained database of ambient
environmental data. STORET is a distributed system where individual agencies manage their
own data at a local level and can use a national data warehouse to share it with others. Both
STORET and SIM operate in an Oracle environment and can be implemented on a personal
desktop or on a client/server basis.
National
STORET
Warehouse
SIM lets users import water quality data into STORET from existing data management systems. Once data are
migrated into a local copy of STORET, they fan be sent to the National STORET Warehouse.
To use SIM, you must perform the following tasks:
Organize your data into delimited text files
Establish SIM Import Configurations that describe the format of your text files
Import your text files into SEVI and migrate the data that passes validation into
STORET
This User Guide and Reference Manual will:
Provide guidance to you as you create your delimited text files
Teach you how to create a SIM Import Configuration
Walk you through the process of using SIM to load data into STORET
The successful use of SIM is dependant on your clear understanding of STORET. You
should establish all relevant metadata defaults in STORET before you begin to use SIM.
-------
Formatting Your Data
Before using SIM, you must organize your data into delimited text files. Most organizations
use common software products such as Excel, Access, or Lotus 1-2-3 to create data tables, which
can be saved and exported as delimited text files. Larger organizations may produce these files
as exports from existing Data Management or Laboratory Information Management Systems
(LIMS). SIM accepts six different file types (See Appendix A for more details):
1. Project Descriptions
Initiatives or reasons why data was collected.
2. Station Descriptions
Points or areas where field measurements are made or samples are collected.
3. Additional Locations
Points that define boundaries, sample locations, or observation sites within a station.
4. Wells
Wells that are constructed to gather samples or field measurements.
5. Field Measurements and Non-biological Samples
Results of field measurements or the analysis of non-biological samples.
6. Biological Samples
Results associated with biological samples.
The data elements or fields in each file type are organized in a tabular format where the
column delimiter is either a Tab ( ), Pipe (|), Tilde (~), or Comma Q.1 Additionally, using a
Back Slash (\) as a sub-delimiter is available for columns that can accept multiple values.2 The
required and optional elements for each file type are described in Appendix A. Note that many
of these data elements have a list of valid values that the user must select from. The order in
which you place the data elements in your delimited text files is not important as SIM allows you
to define that order before you import your file. Do not include a header row in the delimited
text files that you create.
1 If any of these characters appear in your data, you should NOT use that character as a delimiter since there is no
way for SIM to tell which is the delimiter and which is simply part of the data.
2 Certain fields such as Project ID in the 'Field Measurements and Non-Biological Samples' can accept multiple
values in a single column and row of the import file. See Appendix A for a list of columns that support loading
multiple values.
-------
There are several example text files in the C:\STORET\orasto2\SIM\SIM201_EXAMPLES
directory that may help you as you organize your data.
Import Order
The data imported under each of the file types complements each other, and the order in which
you migrate data files to STORET is important. Station Descriptions should always be loaded
before Additional Locations and Wells. Project Descriptions and all the Station files should
always be loaded before your Result files. This ensures that the key data elements will be
registered in STORET and are ready for results to be associated with them. Likewise, similar
rules apply when removing these files from STORET and SIM. As such, it is recommended that
when possible, files be removed in reverse chronological order.
Quick Tutorial
This tutorial is designed to provide you with basic "hands on" instruction for using SIM.
Each lesson focuses on different pieces of functionality included in SIM. There are text
descriptions of the functions and associated concepts, and then there are step-by-step instructions
for using the tools. The step-by-step instructions for you to follow on your PC are called out into
text boxes, separate from the text. For additional training, please refer to the SIM Training
Manual available on the STORET website.
LThisisanexampleofatextboxthatconta^^
-------
SIM v2.0.1 User Guide
Tutorial I. Defining an Import Configuration
The following lesson will show you how to create a custom Import Configuration that will describe the
format of your text files.
1. Begin by starting SIM.
Si i rt r«j...p< Mi Jiil
2. From this screen, you can either start a new import of data or access an existing import that you may
have brought into SIM but have yet to migrate to the STORET database.
Whrf typt of
-------
SIM v2.0.1 User Guide
4. This menu allows you to specify the Import Configuration and the path to the text file that you want
to import.
Click [Define New Import Configuration
_ r: *
mpnrtTypi :
I.ill I H:-. II III . [I II.i i .
5. From this SIM Import Configuration menu, you can:
a) Provide a name and description for the Import Configuration
b) Define the organization that this data will load into.
c) Indicate the delimiter that will be used in the text file.
d) Select the data elements that will be included in the text file.
e) Define the position or order in which the data elements will appear in the file.
f) Select a column to generate a value, which is not included in the import file.
g) Establish default values for the data elements in the import file that are left null or blank.
h) View the format that each row in the import file must adhere to if this configuration is used.
i) Establish how individual data elements will be additionally formatted (if appropriate).
j) Establish translation parameters that change values while a file is being imported.
.
|T it ID
|T if SriTfiD^le
TiptJjnr
51 Ann ID
f (idi Tips
t K| jenc t M ukitcf
[V/jlDiFiptlD
2
3
*
5
6
'
6
PVJS fl«i incwe Sersiaie Detelf
P P r
E^t r
EXj r
r r r
"spBJ^P"
r
r r
on trmiB»in
^ |c.|gmn Vist Nurtxr
ID P
r
r
r
'-
c
i
''.
J
'.
.1
..........
- T [ T- - :.
VMftll fffff.
- : : r.
i: I -
!..=:= .^ lr:
STORETTiWs
..........
--.=::.
n
Import Format |Tno lO&afcwi lOfitaton v«t WurrieiFvanct iCHAoM
-------
SIM v2.0.1 User Guide
6. Click any Translation
7. You can establish a translation by defining the "Code" that should be translated and the value that it
should be "Translated To". Users can use the arrows at the end row of each data elements to translate to
valid STORET values (if they exist). For instance, a user could translate a CAS number to an
appropriate STORET "Characteristic" name.
Click |Close| and then the [Instructions] tab on the Import Configuration menu.
- i.i
8. You can use this menu to establish general import instructions. Change an instruction by
highlighting a row and clicking the relevant arrow. If you choose to "create translations...", the
software will populate the "Code" field on the Translations menu with any values that do not match
required valid values. You must then fill in the "Translate To" field and re-import the file for the
changes to take place. Other instructions allow you to auto-generate Trip IDs and Visit Numbers.
-------
SIM v2.0.1 User Guide
Summary
The purpose of an import configuration is to set up a pattern to translate your source data file
into STORET by using SIM. SIM import configurations can help you set up the order of fields,
format data, and perform simple data translations.
In Tutorial I. Defining an Import Configuration, you learned how to:
Define a new import configuration
Use translations to change values while a file is being imported.
-------
SIM v2.0.1 User Guide
Tutorial II. Using SIM to Import & Migrate Data to STORET
This tutorial illustrates the following:
The Import of a text file into SIM so that it can be validated.
The Migration of data that passes validation from the temporary SIM tables into STORET.
1.
From the SIM Welcome screen, click
Non-biological Samples
New Import
then click
Field Measurements
to reach the Import File menu for Field Measurements and
and]
Non
Biological Samples.
hip an Typo
Impnrt
Tail lie nani f '.-jiCHEv-
2. You must first select an existing Import Configuration. Click the down-turned arrow at the
end of the line labeled "Import Configuration".
i .r
3. This screen provides a list of Import Configurations that have been created in SIM
Highlight "Example-Field Measurements" and click |OK
Import lypa |
aJ
nD [
[>
-------
SIM v2.0.1 User Guide
4. The name of the Import Configuration and the Organization to which the data will be
associated should now appear on the Import File menu. You can review the configuration
details by clicking the button labeled "Edit Existing Import Configuration".
You must now define the path to the file that you want to import. From the Import File menu,
click Browse.
Wp* rrrt-i fwii "ici-nfi ffcMininY frrji r i
5. This menu allows you to navigate your drives and directories to locate the appropriate file.
Select "C:\STORET\orasto2\SIM\SIM201 EXAMPLESVExample - Field Measurements" and
click Open. Note - This is the default path for a stand-alone installation. Based on your
original installation parameters, the example files may be located on a different path on your
system.
knpnnrypu
hipnrt
Eib Edflm I
6. The name of the text file should now appear on the Import File menu.
Now click |IMPORT| and then |OK| when the import is complete.
-------
SIM v2.0.1 User Guide
7. SIM checked the file for errors that would prevent the data from being loaded to STORET
and it has imported the data into a series of temporary tables.
From this Import Status menu, you can:
a) Review the number of records that that SIM imported, the number of those records that
had errors, and the number of those records that are ready to be migrated to STORET.
b) View import errors for the entire file and/or preview records with errors on a record-by-
record basis.
c) Delete an import from SEVI's temporary tables. All data remains in the SIM temporary
tables until the import is deleted. You should delete your imports after migration once
you are satisfied that your data has been accurately loaded to STORET.
d) Export records with errors after an Export Path and an Export File Name have been
designated.
e) Reference and/or edit the Import Configuration that was used for this import.
f) Click [Migrate Records to STORET| and move the data you imported from the SIM to
STORET.
& Import Status
Import ID | :£.-
DngID |.n Mir
Impart File 1C VaiarfD?
ImparlTypg p~
^iMVEiumpJc Fed MMzjicmcnli 111
Import Conliguration |E*amci& - Feld HeaitJiemsntt
Edit Iripnt Confiouralnn
(-1
Data Imported
Raws with Errors
ResuHs Ready tn Migrate
AjctMtiesReacVtQ Migrate
M-16-2IO-'
View liripat Eircfs
Y\ Piexien Recuids wlh Erofs
cords io STORE!
Tipofl Fiorn EIH
c^panro i em
d
ExpOfl Path |::'-
Export File |Lnmioi«.t-!t
1
Hrlp dote
10
-------
SIM v2.0.1 User Guide
Summary
In Tutorial II, Using SIM to Import and Migrate Data to STORET, you learned how to:
Import files to SIM using a pre-defined import configuration and text file.
Migrate data into STORET.
11
-------
SIM v2.0.1 User Guide
Tutorial III. Advanced Functions
This tutorial illustrates the following:
1. Reordering field positions in an Import Configuration
2. Saving and Loading Import Configurations
3. Viewing data that has been imported to SIM
4. Viewing the status of import files brought into SIM but not migrated or deleted
5. Removing (undo) a migration of data from STORET
6. Generating an error report to print or save
1. Reordering field positions in an Import Configuration
Field or column positions can be reordered from the Import Configuration menu. Suppose
Start Date needs to be in the second position instead of the third. You can change the
position number from 3 to a number between 1 and 2 (such as 1.5) and click |Save| to reorder
the columns.
12
-------
SIM v2.0.1 User Guide
2. Saving and Loading Import Configurations
From the Import Configuration menu: you can save import configurations to a external file
and load copied configurations using the |Copy ConfigurationsI and [Load Configurations! buttons.
3. Viewing Data that has been imported to SIM
View data that is in SIM using the View.. .List capability that can be found under the
Projects, Stations, or Results and Activities menus. Double-click an individual record to
recall and view the data. Each record will have a status of:
Approved for migration to STORET
Not approved for migration to STORET
Exported (migrated) from SIM to STORET
Uploaded if SIM copied data from STORET itself
13
-------
SIM v2.0.1 User Guide
4. View the status of an import that was brought into SIM but was not migrated
or deleted
There may be cases where you have previously imported files to SIM, but did not migrate
them to STORET. This may occur because you had to stop your session, you wanted to review a file,
or for another reason. Other times, you may not recall whether you migrated a particular file to
STORET, or removed the imported data from STORET. The following outlines reviewing your
imports to determine their status.
From the SIM Welcome menu, click [Existing Imports
There are four different categories of imports that you can access: Projects, Stations, Results,
and Biological. To review the status of the import that was performed in Tutorial II, click
RESULTS!
What type of Existing Import do
you wish to view?
PROJECTS STATIONS
14
-------
SIM v2.0.1 User Guide
Click on the row that represents the relevant import and click [Status) at the end of the line.
You will return to the Import Status menu that you saw in Step 4 of Tutorial II where
depending on the status of the import, a variety of actions can be performed.
bnport bnpDrt
Typ_o fib
T .-"""
On
k*giijt«1
On
finwj RITA'S
oiiKi ». Em?-j Hrmty Bendy
(iLLllj
Euwfe-n«Ki:ni
;.«*-E>»p'j.>
E-!«ivni*-;«,
K-IUHfU
nMOJtKM
DMOZW
ne-i^ziw
oe-ii LW
Dt-ll 2Wd
!
1
"
>
5
0
3W1I
auii
---
'.-.V
,.,
,,,
:-.'
-'
.
J
5. Remove or undo a migration of data from STORET
Select "Advanced" then "Migration Log" from the SIM windows menu.
Highlight the migration that you would like to remove from STORET and click the
appropriate
Clear Migration from STORET button
jS=-S
15
-------
SIM v2.0.1 User Guide
**Note that SIM cannot undo a migration once the import has been deleted from SIM.
Additionally, you cannot undo a migration once any changes or additions have been made to
the migrated data through the STORET Data Entry Module.
Although you can unmigrate files in any order, it is recommended that possible files be
removed in reverse chronological order to avoid complications with parent/child
relationships between files.
6. Generate an error report file to print or save
Select "Advanced" then "Import Error Report" from the SIM windows menu.
Use the down-turned arrow to select the file that was imported. Next, click [Run Report
11
ol
'
Import
Status Report
BdnipdinwilHlGCTTlam4ipraTie£[TtrTipDriedtln. SHetf
t. lh£n clrta Ihfr Vj.in ft^pi^rf hydjlijr. t rJ i r p LjV' ' "Y»1 *m;r5
P
'lltit'i '/ft
l"lf>lfi
Au>R
-------
SIM v2.0.1 User Guide
SIM v2.0.1 New Features
New Data Elements
SIM v2.0.1 has been enhanced to support the following data elements.
1. Project Descriptions
Document/Graphic
2. Station Descriptions
Document/Graphic
Primary Estuary State
Required
Conditional
Yes
3. Additional Locations
No new additions
4. Wells
[Required
No new additions
5. Field Measurements and Non-biological Samples
Visit Document/Graphic
Parent Sample ID
Cooperating Organization
Activity Document/Graphic
Particle Size Basis
Result Document/Graphic
Lab Remark Codes
PDL Line Number
PDL Line Name
Required
Conditional
Must exist in STORE!
or in same data file
Must Exist in STORE!
Yes
Yes. Separate with "\"
for Composites with
Parents
Yes. Separate with "\"
Yes. Separate with "\"
6. Biological Samples
Visit Document/Graphic
Parent Sample ID
Cooperating Organization
Activity Document/Graphic
Particle Size Basis
Result Document/Graphic
Lab Remark Codes
Conditional
Must Exist in STORET
or Current Data File
Must Exist in STORET
Yes
Yes. Separate with "\"
for Composites with
Parents
Yes. Separate with "\"
Yes. Separate with "\"
17
-------
SIM v2.0.1 User Guide
Instructions on the use of many of these new data elements are outlined in the paragraphs
below.
Document/Graphics
Document or graphic files can now be imported into SIM and migrated to STORET for
Projects, Stations, Station Visits, Activities, and Results. To add documents to your import:
1. In the SIM Import Configuration form, select to include the Document/Graphic
column(s).
2. Place your documents in the SIM201_Documents folder. For Personal Oracle
Installations this will be located under your STORET/Orasto2/SIM folder. For
Client/Server installation, the documents folder will be located on the Server. As
such, you should contact your STORET administrator for the full name and location
of this folder.
3. Include the complete file name, without the path, of each image to your import file.
4. Initiate the standard import process.
5. Note: The images must remain in the SIM201_Documents folder until they have
been migrated into STORET.
Composites with Parents
Composite with Parents data can now be imported into SIM and migrated to STORET for
either Chemical or Biological Results. To import composite with parents results:
1. Parent Activity samples must either be contained in the same data file as the Child
records or must exist in STORET.
2. Add the Parent Sample ID column to the import configuration for Child samples.
3. Add multiple Parent Sample ID's to the data file separated by a 'V for each.
4. Initiate the standard import process.
Cooperating Organizations
Multiple Cooperating Organizations can now be associated with an Activity. To add
cooperating organizations to your imports:
1. Add the new Cooperating Organization column to a results import configuration
2. Add Cooperating Organization to the import file.
3. If importing multiple cooperating organizations for an activity, use 'V to separate
each.
4. Initiate the standard import process.
Lab Remark Codes
Multiple Lab Remark Codes can now be associated with a result. To add lab remark codes to
your imports:
1. Add the new Lab Remark Code column to a results import configuration
2. Add Lab Remark Codes to the import file.
3. If importing multiple lab remark codes for a result, use 'V to separate each.
4. Initiate the standard import process.
18
-------
SIM v2.0.1 User Guide
Portable Data Loggers
Portable Data Logger (PDL) data can now be imported into SIM and migrated to STORET.
To import portable data logger results:
1. Add the new PDL Line Number and PDL Line Name columns to the import
configuration.
2. Add the Data Logger Line Numbers and Line Names to your import file.
3. Set the Activity Type to 'Field Msr/Obs' in the import file.
4. Set the Activity Category to 'Portable Data Logger' in the import file.
5. Initiate the standard import process.
For a working example of loading PDL results, access the Portable Data Logger
configurations and example files provided with the SIM application.
New Features:
In addition to the new data elements and their processes, SIM v2.0.1 has added other new
features. These are described below:
New in Configuration Settings: Generation of Columns not included in the import file
When an import configuration is created, users now have the option to generate columns that
the user has not included in the import file. To generate columns during import:
1. Open the Import Configuration Window.
2. Check the Generate Check box for all columns that will be imported into SIM but not
included in the import file.
3. Enter the default value that SIM will use for each generated column.
4. For Station Visits and Trips, the default column can be left blank if an instruction has
been chosen that will generate these values automatically.
5. Save your configuration and initiate the standard import process.
Export of Additional Locations and Wells Errors to a file
During the data import process, the Import Status form displays the number of lines with
errors. In SIM v2.0 and earlier, an option exists for Project, Station, and Results to export
these error records into a text file, correct the records, and re-import the data into SIM. New
to SIM v2.0.1, this functionality has been added to the Location and Wells import processes.
To export records with errors:
1. Access the Import Status window.
2. Enter the desired output file name in the Export File field.
3. Enter the desired output file path in the Export Path field.
4. Click the 'Export Records with Errors' button.
System-Defined Habitat Assessments
In SIM v2.0.1, System-Defined Habitat Assessments may be migrated to STORET in much
the same manner as the User-Defined Habitat Assessments that were already supported by
SIM in previous versions. To import Habitat Assessments:
1. First create a Habitat Assessment Characteristic Group in STORET.
19
-------
SIM v2.0.1 User Guide
2. Include an activity type of 'Field Msr/Obs' and an activity category of 'Routine
Habitat Assessment' or 'Replicate Habitat Assessment' in the data file.
3. Include the Habitat Assessment Characteristic Group and Row Ids along with the
appropriate result values in the data file.
4. Initiate the standard import process.
Updated example files and configurations are available to test the new features that SIM
v2.0.1 provides.
The SINGLE_USER_ENVIRONMENT system configuration setting was removed. SIM
is now always able to handle multiple users.
SIM will now accept Well ID's that are alphanumeric.
Free text values can now be translated in SIM.
Primary Estuary State is now needed in addition to Primary and Secondary Estuary in
order to determine the exact area.
The short name for Sample Matrix is required in the data file or translation in place of the
code.
Imports for Created from Sample are available for both Chemical and Biological
samples.
The layout to view activity data in SIM has been rearranged so that Activity Depth and
Activity Parent data are now on separate forms.
The SIM time format of HHMM is now equivalent to a twenty-four hour day to provide
consistency with the STORET Data Entry Application.
20
-------
SIM v2.0.1 User Guide
System Configuration Definitions
Under the Advanced/System Config menu option in SIM v2.0.1, there are several user-defined
settings used to configure your system. The definitions for each of the configuration settings are
listed below.
BUILD_NUMBER
The Build Number configuration item displays the four-digit release number for this
installation for SIM. This number is displayed in the description column. The value column
is not used.
DFLT ORG
The Default Organization configuration item allows the user to identify a STORET
organization that will default throughout the SIM application when the user is prompted to
enter an organization. This will not prohibit the user from selecting another valid
organization if desired. Value is set to organization "DEMOTEST" by default.
IMPORT PATH
The Import Path configuration item allows the user to set a path to a directory where text
files are located for import into SIM. When the user clicks the Browse button in the Import
File form, the user will automatically be directed to the directory named in the Import Path
configuration item value. If the ALLOW_LONG_LINES configuration item is set to "Yes",
the import path must be entered as it would be mapped from the database server and the
browse button will be disabled.
DFLT_EXPORT_PATH
The Default Export Path configuration item allows the user to set a path to a directory where
files will be created if errors were found in the import. In the Import Status window, the user
can view/edit the export path and click the Export Records with Errors button to create the
export file.
DFLT_EXPORT_FILE
The Default Export File configuration item allows the user to define the default name of a
file, which will be created if errors were found in the import. In the Import Status window,
the user can view/edit the export filename and click the Export Records with Errors button to
create the export file.
HELP_PATH
The Help Path configuration item allows the user to point to a directory that contains the files
needed to access the general help window. The chosen directory path must contain the files
sim2_gen.gid and sim2_gen.hlp. In a standard SIM v2.0.1 installation, the files will be
contained in the directory where the software was installed.
21
-------
SIM v2.0.1 User Guide
COPY PATH
The Copy Path configuration item allows the user to set a default path to a directory where
the copy/load utility will read/write the import configuration files. In the Copy and Load
Configuration forms, the user can view/edit the default copy path and file name.
ALLOW_LONG_LINES
The Allow Long Lines configuration item allows the user to configure the system to read
ASCII files with very long lines. When the configuration value is set to "Yes", the user can
import files with lines longer than 1000 characters. When the system is configured to allow
long lines, the import file is parsed by the database rather than the client machine. As a
result, the browse button is disabled and you must enter the full path and file name as it could
be found from the database server. Value is set to "No" by default.
22
-------
SIM v2.0.1 User Guide
Appendix A:
Listing of Available STORET Data Elements
for Each File Type
23
-------
SIM v2.0.1 User Guide
Overview:
The SIM v2.0.1 application supports imports for Projects, Stations, Additional Locations, Wells,
Field Measurements and Non-Bio Results, and Biological Results. The tables below list all of the
STORET data elements supported by each of these import types.
The Required column indicates for each data element if it is required by SIM /STORET, is
conditionally required, or is optional.
The Allowable Values column indicates if there is a set of valid values for the data element, which
are enforced by STORET. For additional information on the STORET Tables and Allowable Values
by column, review the Data Structure List with definitions.pdfmcludQd in the documentation on the
STORET v2.0 CD.
For the result imports, an additional column has been added to the tables to indicate where multiple
values can be imported separated by a Back Slash (\) for a single data element on a single data line.
1. Project Descriptions
Data Element
Project ID
Name
Start Date
Duration
Purpose
Contact
Document/Graphic
Required
Yes
Yes
Yes
Yes
Yes
Allowed Values
2. Station Descriptions
Data Element
Station ID
Station Name
Primary Type
Secondary Type
Establishment Date
Point Name
Water Depth
Water Depth Units
Station Description
Document/Graphic
EPA Key Identifier
Point Name
Latitude
Latitude Direction
Longitude
Longitude Direction
Lat/Long Measurement Date
Geopositioning Datum
Geopositioning Method
Scale
State
County
HUC
Ecoregion Name
NRCS Watershed ID
Influence Area
Required
Yes
Yes
Yes
Conditional
Yes
Defaults to "N"
Yes
Defaults to "W
Yes
Yes
Conditional
Yes
Yes
Allowed Values
Yes
Yes
Yes
Yes
Yes
Yes
Yes
24
-------
SIM v2.0.1 User Guide
Data Element
Travel Directions
ZID Relation
Native American Land Name
Native American Land State
Elevation
Elevation Units
Elevation Method
Elevation Datum
Elevation Measurement Date
Ocean Name
Shore Relation
Additional Ocean Name
Ocean Station Dist to Shore
Ocean Station Dist to Shore Units
Ocean Station Ref Point
Ocean Station Bottom Topography
Primary Estuary
Primary Estuary State
Secondary Estuary
Other Estuary
Additional Estuary Name
Estuary Distance to Shore
Estuary Dist to Shore Units
Estuary Reference Point
Great Lake
Additional Great Lake Name
Great Lake Dist to Shore
Great Lake Dist to Shore Units
Great Lake Reference Point
Required
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Allowed Values
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
3. Additional Locations
Data Element
Station ID
Point Type
Point Name
Sequence Number
Latitude
Latitude Direction
Longitude
Longitude Direction
Lat/Long Measurement Date
Geopositioning Datum
Geopositioning Method
Elevation
Elevation Units
Elevation Method
Elevation Datum
Elevation Measurement Date
Scale
Required
Yes
Yes
Yes
Yes
Defaults to "N"
Yes
Defaults to "W"
Yes
Yes
Conditional
Conditional
Conditional
Conditional
Allowed Values
Must exist in STORET
Yes
Yes
Yes
Yes
Yes
Yes
Yes
25
-------
SIM v2.0.1 User Guide
4. Wells
Data Element
Station ID
Well Number
Well Name
Well Status
Well Use
Well Head Latitude
Well Head Latitude Direction
Well Head Longitude
Well Head Longitude Direction
Well Head Geopositioning Datum
Well Head Geopositioning Method
Well Head Scale
Well Head Measurement Date
Well Head Elevation
Well Head Elevation Units
Well Head Elevation Method
Well Head Elevation Datum
Elevation Measurement Date
Required
Yes
Yes
Yes
Yes
Yes
Conditional.
Defaults to "N"
Conditional.
Defaults to "N"
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Allowed Values
Must Exist in STORE!
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
5. Field Measurements and Non-biological Samples
Data Element
Trip ID
Trip Start Date
Trip Stop Date
Trip Name
Station ID
Point Type
Sequence Number
Well or Pipe ID
Additional Location Information
Station Visit Number
Station Visit Arrival Date
Visit Comments
Visit Document/Graphic
Project ID
Activity ID
Medium
Activity Type
Activity Category
Trip QC Type
QC Indicator
Sample Matrix
Chain of Custody ID
Replicate Number
Parent Sample ID
Activity Start Date
Activity Start Time
Activity Start Time Zone
Activity End Date
Activity End Time
Activity End Time Zone
Total Sample Weight
Total Sample Weight Units
Depth to Activity
Depth to Activity Units
Required
Yes
Yes
Conditional
Conditional
Yes
Yes
Yes
Yes
Yes
Conditional
Conditional
Conditional
Conditional
Yes
Conditional
Conditional
Conditional
Conditional
Allowed Values
Must Exist in STORET
Must Exist in STORET
Must Exist in STORET
Must Exist in STORET
Yes
Yes
Yes
Yes
Yes
Yes
Must exist in STORET
or in same data file
Yes
Yes
Yes
Yes
Multiple Allowed
Yes. Separate with "\"
Yes. Separate with "\"
for Composites with
Parents
26
-------
SIM v2.0.1 User Guide
Data Element
Relative Depth
Depth Measured From
Lower Depth
Upper Depth
Upper/Lower Depth Units
Depth Zone Type
Thermocline
Halocline
Pycnocline
Personnel
Cooperating Organization
Activity Comments
Activity Document/Graphic
Sample Collection Procedure ID
Gear ID
Gear Configuration ID
Gear Deployment Comments
Sample Preservation, Transport & Storage ID
Sample Transport and Storage Comments
Field Set Name
Field Set ID
Detection Condition
Characteristic Group ID
Characteristic Row ID
Characteristic Name
Result Value
Result Value Units
Result Status
Sample Fraction
Statistic Type
Value Type
Precision
Confidence Level
Bias
CL Corrected for Bias
Duration Basis
Temperature Basis
Weight basis
Particle Size Basis
Result Comment
Result Document/Graphic
Laboratory ID
Field/Lab Procedure
Field/Lab Procedure Source
Laboratory Certified
Laboratory Batch ID
Lab Remark Codes
Analysis Date
Analysis Time
Analysis Time Zone
Lab Sample Prep Procedure
Lab Sample Prep Procedure Source
Quantification Low
Quantification High
Detection Limit
Detection Limit Unit
Detection Limit Comment
PDL Line Number
PDL Line Name
Required
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Defaults to
"F"
Conditional
Defaults to
"Actual"
Conditional
Conditional
Conditional
Conditional
Conditional
Allowed Values
Yes
Yes
Yes
Yes
Yes
Yes
Must Exist in STORET
Must Exist in STORET
Must Exist in STORET
Yes
Must Exist in STORET
Must Exist in STORET
Yes
Must Exist in STORET
Must Exist in STORET
Yes
Yes*
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Must Exist in STORET
Must Exist in STORET
Yes
Yes
Yes
Yes
Must Exist in STORET
Yes
Yes
Multiple Allowed
Yes. Separate with "\"
Yes. Separate with "\"
Yes. Separate with "\"
*Result values can be numbers or, in some cases they are chosen from lists of allowable values.
27
-------
SIM v2.0.1 User Guide
6. Biological Samples
Data Element
Trip ID
Trip Start Date
Trip Stop Date
Trip Name
Station ID
Point Type
Sequence Number
Well or Pipe ID
Additional Location Information
Station Visit Number
Station Visit Arrival Date
Visit Comments
Visit Document/Graphic
Project ID
Activity ID
Medium
Activity Type
Activity Category
QC Indicator
Sample Matrix
Chain of Custody ID
Replicate Number
Intent
Parent Sample ID
Community
Bio Part
Subject Taxon
Subject Taxon Species Number
Total Sample Weight
Total Sample Weight Units
Activity Start Date
Activity Start Time
Activity Start Time Zone
Activity End Date
Activity End Time
Activity End Time Zone
Depth to Activity
Depth to Activity Units
Relative Depth
Depth Measured From
Lower Depth
Upper Depth
Upper/Lower Depth Units
Depth Zone Type
Thermocline
Halocline
Pycnocline
Personnel
Cooperating Organization
Activity Comments
Activity Document/Graphic
Sample Collection Procedure ID
Gear ID
Gear Configuration ID
Gear Deployment Comments
Required
Yes
Yes
Conditional
Conditional
Yes
Yes
Yes
Yes
Yes
Yes
Conditional
Yes
Conditional
Conditional
Conditional
Conditional
Conditional
Yes
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Allowed Values
Must Exist in STORET
Must Exist in STORET
Must Exist in STORET
Yes (Biological)
Yes
Yes
Yes
Yes
Yes
Must Exist in STORET
or Current Data File
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Must Exist in STORET
Must Exist in STORET
Must Exist in STORET
Yes
Must Exist in STORET
Multiple Allowed
Yes. Separate with "\"
Yes. Separate with "\"
for Composites with
Parents
Yes. Separate with "\"
Yes. Separate with "\"
28
-------
SIM v2.0.1 User Guide
Data Element
Sample Preservation, Transport & Storage ID
Sample Transport and Storage Comments
Field Set ID
Field Set Name
Distance Fished
Distance Fished Units
Fished Duration
Fished Duration Units
Trap or Net Sampling Duration
Trap or Net Sampling Duration Units
Relative Current Direction
Relative Wind Direction
Orientation to Current
Trap or Net Comments
Bio Results Group ID
Bio Results Type
Bio Results Group Description
Bio Group Count Type
Total Number in Group
Frequency Analysis Type
Primary Class Descriptor
Secondary Class Descriptor
Primary Class Value
Secondary Class Value
Common Class Descriptor
Common Class Descriptor Units
Sex
Life Stage
Frequency Class Count
Lower Class Bound
Upper Class Bound
Number of Individuals in Group
Individual Number
Detection Condition
Characteristic Group ID
Characteristic Row ID
Characteristic Name
Characteristic Species Number
Result Value
Result Value Units
Result Status
Sample Fraction
Statistic Type
Value Type
Precision
Confidence Level
Bias
CL Corrected for Bias
Duration Basis
Temperature Basis
Weight basis
Particle Size Basis
Cell Form
Cell Shape
Habit
Voltinism
Taxon Pollution Tolerance
Trophic Level
Functional Feeding Group
Required
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Conditional
Defaults to
"F"
Conditional
Defaults to
"Actual"
Allowed Values
Must Exist in STORET
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Must Exist in STORET
Must Exist in STORET
Yes
Yes*
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Multiple Allowed
*Result values can be numbers or, in some cases they are chosen from lists of allowable values.
29
-------
SIM v2.0.1 User Guide
Data Element
Result Comment
Result Document/Graphic
Laboratory ID
Field/Lab Procedure
Field/Lab Procedure Source
Laboratory Certified
Laboratory Batch ID
Lab Remark Codes
Analysis Date
Analysis Time
Analysis Time Zone
Lab Sample Prep Procedure
Lab Sample Prep Procedure Source
Quantification Low
Quantification High
Detection Limit
Detection Limit Unit
Detection Limit Comment
Required
Conditional
Conditional
Conditional
Allowed Values
Must Exist in STORET
Must Exist in STORET
Yes
Yes
Yes
Yes
Must Exist in STORET
Yes
Yes
Multiple Allowed
Yes. Separate with "\"
30
-------
SIM v2.0.1 User Guide
Appendix B:
SIM v2.0.1 Example Files
31
-------
SIM v2.0.1 User Guide
Overview:
The SIM v2.0.1 application contains eighteen examples of various import types listed as follows:
Projects, Stations, Locations, Wells, Regular Results, and Biological Results. At least one example of
each of these types is included to demonstrate the correct use of the data elements required for valid
entry into STORE! v2.0.
The data imported under each of the file types complements each other, and the order in which you
migrate data files to STORET is important. Station Descriptions should always be loaded before
Additional Locations and Wells. Project Descriptions and all the Station files should always be loaded
before your Result files. This ensures that the key data elements will be registered in STORET and are
ready for results to be associated with them. Likewise, similar rules apply when removing these files
from STORET and SIM. As such, it is recommended that when possible, files be removed in reverse
chronological order.
Getting Started:
In order to import example data into SIM and STORET, a valid import configuration and data file is
required as shown in Tutorial II of this document. The SIM v2.0.1 installation pre-loads the import
configurations for each of these examples into SIM.
The SIM v2.0.1 installation also installs the associated import data files in the following directory for
Personal Oracle users: C:\STORET\Orasto2\SIM\SIM201_EXAMPLES. TheSIMv2.0.1 installation
process sets the IMPORT_PATH System Configuration setting to this path so that the example data
files are readily accessible for use. The directory path may be slightly different for use on the server.
Contact your Database Administrator for the correct example file path and directory if the Server
installation was completed.
All of the import configurations are created to utilize the DEMOTEST organization which is pre-
loaded in the STORET v2.0 database and will not impact your actual water quality data. Please note
that if your server installation of the STORET database was converted from version 1.2 to version 2.0,
the DEMOTEST organization will not automatically be updated. In such cases, the SIM example files
may generate error messages during the import process. If desired, scripts to update the DEMOTEST
data are provided on the STORET v2.0 CD.
A breakdown of each example by file type, import configuration, and data file is displayed below.
No
1
2
3
4
5
6
7
8
9
10
File Type
Project
Station
Station
Station
Station
Location
Well
Results
Results
Results
Import Configuration
Example - Projects
Example - Estuary Station
Example - Great Lake Station
Example - Ocean Station
Example - Other Stations
Example - Additional Sample Points
Example - Wells
Example - Field Measurements
Example - Portable Data Logger with
Characteristic Groups
Example - Portable Data Logger with
Import Data File
Example - Projects.txt
Example - Estuary Station.txt
Example - Great Lake Statioatxt
Example - Ocean Statioatxt
Example - Other Stations.txt
Example - Additional Sample Points.txt
Example - Wells.txt
Example - Field Measurements.txt
Example - Portable Data Logger with
Characteristic Groups.txt
Example - Portable Data Logger with
32
-------
SIM v2.0.1 User Guide
11
12
13
14
15
16
17
18
Results
Results
Results
Biological
Biological
Biological
Biological
Biological
Characteristic Names
Example - Chem Results with Char
Groups and Rows
Example - Chem Results with Char
Names
Example - Grain Size Analysis
Example - Individual Intent
Example - Individual Intent - Created
from Sample
Example -MTPC STFC
Example - Single Taxon Group Summary
Example - Single Taxon Individuals
Characteristic Names.txt
Example - Chem Results with Char
Groups and Rows.txt
Example - Chem Results with Char
Names.txt
Example - Grain Size Analysis.txt
Example - Individual Intent.txt
Example - Individual Intent - Created
from Sample.txt
Example - MTPC STFC.txt
Example - Single Taxon Group
Summary.txt
Example - Single Taxon Individuals.txt
Example File Overview:
Projects
Example - Projects
Things to note:
All three projects are required for the Results and Biological example files.
One of the projects contains a Document/Graphic called Chesapeake_Bay_EMAP.pdf.
Stations
Example - Estuary Station
Things to note:
Illustrates the usage of the Primary Estuary, Secondary Estuary, and Estuary State.
Since the names of certain Primary and Secondary Estuaries can be the same in
multiple areas, the inclusion of Estuary State ensures that the correct combination of
three is migrated to STORET.
Illustrates the use of specific formats for latitude, longitude, date, and time.
The station contains a Document/Graphic called Station_CBC050.jpg.
Example - Great Lake Station
Things to note:
Illustrates the usage of required data elements needed to define a Great Lake Station.
The station contains a Document/Graphic called Station_CBC051.jpg.
Example - Ocean Station
Things to note:
Illustrates the usage of required data elements needed to define an Ocean Station.
The station contains a Document/Graphic called Station_CBC052.jpg.
Example - Other Stations
33
-------
SIM v2.0.1 User Guide
Things to note:
Illustrates the usage of required data elements needed to define a River/Stream, Well,
and Lake Station.
Two of the stations contain Document/Graphics called Station_CBC053.jpg and
Station_CBC056.jpg.
Additional Station Locations
Example - Additional Sample Points
Things to note:
Illustrates the usage of required data elements needed to define the additional sampling
or boundary locations for a station.
The Other Station example file must be migrated to STORET before this example file
can be imported to SIM.
Wells
Example - Wells
Things to note:
Illustrates the usage of required data elements needed to define additional wells and
their usage for a station.
The Other Station example file must be migrated to STORET before this example file
can be imported to SIM.
Results
Example - Chem Results with Char Groups and Rows
Things to note:
Illustrates the usage of required data elements needed to define a User-Defined Habitat
Assessment and Routine Sample when accessing a STORET Characteristic Group
and Row to provide additional information.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
Example - Chem Results with Char Names
Things to note:
Illustrates the usage of required data elements needed to define a Routine Sample with
characteristics details loaded in the file. No STORET characteristic group is needed
to load this file.
Illustrates the usage of required data elements for a single trip with multiple visits to a
station.
Illustrates the entry of multiple lab remark codes.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
34
-------
SIM v2.0.1 User Guide
Example - Field Measurements
Things to note:
Illustrates the usage of required data elements needed to define a Field Measurement
and Observation using a STORET Characteristic Group and Row.
Illustrates the use of choice list Characteristics and corresponding Result Values.
No example files need to be migrated to STORET before the example is imported to
SIM.
Example - Grain Size Analysis
Things to note:
Illustrates the usage of required data elements needed to define Routine Sample with a
medium of Sediment using a STORET Characteristic Group and Row.
Both the Projects and Ocean Station example files must be migrated to STORET
before importing this example to SIM.
Example - Portable Data Logger with Groups and Rows
Things to note:
Illustrates the usage of required data elements needed to define a Portable Data Logger
category using a STORET Characteristic Group and Row.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
Example - Portable Data Logger with Char Names
Things to note:
Illustrates the usage of required data elements needed to define a Portable Data Logger
import.
Both the Projects and Ocean Station example files must be migrated to STORET
before importing this example to SIM.
Biological Results
Example - Individual Intent
Things to note:
Illustrates the usage of required data elements needed to define a Routine Sample for a
medium of Biological and intent of Individual.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
The example contains the parent activities for another example file.
Related Business Rules:
When importing parent and child activities in separate data files, the parent activities
must always be migrated to STORET before the children can be imported to SIM.
35
-------
SIM v2.0.1 User Guide
In order to clear a migration of parent activities from STORET, the child activities
must be completely removed from STORET and SIM if they have been added
through a separate data file or direct use of the STORET interface.
When importing parent and child activities in the same data file, the parent activities
must always precede the child activities.
Example - Individual Intent - Created From Sample
Things to note:
Illustrates the usage of required data elements needed to define a Created from Sample
for a Biological Sample. Note the use of the Parent Sample ID column, which
references the parent activity created in the Individual Intent example file.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
The example contains the child activities created from the parent activities in the
Individual Intent example file.
Related Business Rules:
When importing parent and child activities in separate data files, the parent activities
must always be migrated to STORET before the children can be imported to SIM.
When importing parent and child activities in the same data file, the parent activities
must always precede the child activities.
Example-MTPCSTFC
Things to note:
Illustrates the usage of required data elements needed to define intent of Taxon
Abundance and bio results type of Multi-Taxon Population Census and Single Taxon
Frequency Classes.
Illustrates the use of frequency analysis type of both Biological and Physical for Single
Taxon Frequency Class.
Both the Projects and Estuary Station example files must be migrated to STORET
before importing this example to SIM.
Example - Single Taxon Group Summary
Things to note:
Illustrates the usage of required data elements needed to define intent of Taxon
Abundance and bio results type of Single Taxon Group Summary.
Both the Projects and Other Stations example files must be migrated to STORET
before importing this example to SIM.
Example - Single Taxon Individual
Things to note:
Illustrates the usage of required data elements needed to define intent of Taxon
Abundance and bio results type of Single Taxon Individuals.
36
-------
SIM v2.0.1 User Guide
Both the Projects and Estuary Station example files must be migrated to STORET
before importing this example to SIM.
37
------- |