TUTORIAL Importing and Deleting - ESdat

1 TUTORIAL - Importing , Editing and DeletingFor use with ESdat version 5 (version 5 software updates available from ) Using the ESdat Environmental Database System Function TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|1 Function TUTORIAL Importing ,Editingand DeletingTable of Import 1:ImportTemplates-Adding New 2: Import Templates-Updating or Overwriting and 3: Editing 4: Deleting 5: Creating a custom drop down : Importing Import TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|2 SummaryThistutorial: highlightsthe general principles of Importing data into ESdat using templates illustratesmethods for editing, overwriting and Deleting data usestheSampleHydrogeology and Contamdatabase(and therefore can be usedwith the demonstration version of ESdat ) iscompleted using theAccess versionof ESdat (guidancecommentsas todifferencesbetween Access and SQLare provided within this TUTORIAL ) requires approximately 90-120minutes to completePre-requisitesThis tutorialalso forms a usefulpre-requisite to subsequentESdattutorialswhichdescribehow toimportspecificdata-typesinto ESdat (such as laboratory data, environmentalstandards, and historical chemistry).

2 For a full list of the tutorials available for use with ESdat , please see theTutorial LearningFollowing completion of this TUTORIAL ,the user will have developed skills in the following: UsingImport Templatesto import data into ESdat Editing imported data within ESdat Deleting (removing) imported data from ESdat Overwritingimported data within ESdat Creation of drop down lists to encourage accurate data entry Managing documents using ESdatIntroductionESdat users typically have a range of datatypesto be managed,including: Sample locationinformation Co-ordinateinformation(such as survey data and GPS data) Field instrument measurements (includingwater levels,NAPL thickness, pH, EC etc.)

3 Laboratoryanalyticaldata (either laboratoryreports inin ESdat formator historicaldata) Laboratory request andChain ofCustodyinformation Soils descriptions and geotechnical soils data Borehole logsand groundwater well construction detailsIt is assumed the user has previously completedTutorial 2 Introducing TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|3 Geo-referenced basemaps in a variety of formatsTo maintain integrity of a database, data must be importedinto ESdatin aconsistent andcoherent this TUTORIAL will assist ESdat users in effectively Importing data sets thatmay notbe in the correct formatoris incomplete.

4 Importing , editing and Deleting data are key skillsfor efficient use of addition, maintaining a record of source documents (such as historical reports) can beuseful if reference to original data is required for TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|4 The ImportViewAn arrayofspecifically formatted Import Templates are availabletoallowimportationofvariousdata typesinto Templatesare accessed through theImportview. Open ESdat Select theSample Hydrogeology and Contamdatabase from the project list SelectImportfromthe View Type tool bar (the top tool bar), as shownbelowThe Import view(as shown in the figure below)providesa range ofImport Optionsforimporting dataand/or informationinto bluebuttonsat the left of theImport viewlabelled Blank Import Templatesprovideseightbuttonsto accesstemplates forthe major types of data imported into ESdat .

5 Locations(sample locationdetails, such as soil bores, test pits or sediment samples) Wells(groundwater well construction details) FieldChemistry(orother field parameters, such as temperature) HistoricalChemistry(as distinct from laboratory supplied analytical reports)Function TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|5 Groundwater levels(including NAPL measurements) Time-variant TOC(wheregroundwaterwellmeasurement reference points( TOCor Top Of Casing)have varied over timethroughdamagerepair, subsidenceetc.) Borehole/Geological logging-Standard( environmental)and Geotechnical logsThescrollable listat the bottom of the blue areasprovides a list ofallavailableimporttemplatedata tablesandreference tables(reference tables arelabelledwith a prefix:zRef_).

6 This list corresponds directly to the list of tables visible in Data Views when hoveringthe mouse over Data Tables (top right).The reference tables refer to existing information (data) that has been imported into ESdatto allow specific example, reference tablezRef_Environmental_Standardsinclude s third partypublishedor user createdassessmentstandards that have been imported into the (or criteria or guidelines) can be usedto create analytical data summary tablescomparinganalytical data against the assessment (as shown above)allows navigation toimportarange of specificfiletypes, including: Laboratoryreports(as supplied byananalytical laboratory in ESdat format) Logger data PopulatedImport Templates( templates from the blue area that have beenpopulated with dataand saved to a directory) DataInterchangefile (for transferring datafrom one database to another) Documents or document directory pathways stored in ESdat AGSformat files (geotechnical and geo-environmental file format)

7 Clicking on any of thesebuttonswill allownavigation to the directorylocationoftheparticular file typeto be imported into ESdat , with the exception TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|6 TheDocumentsbuttonwillopenaBlank Import Templatefor entering the details of one ormore of buttons allows users to: Navigate to and importenvironmental standardssaved in a directory location(either downloaded by the user) Run existing or create new Post Import Calculations Run existing or create newCombined Compounds(Users should refer to the ESdat Users-Manual for details on these two advanced concepts)Function TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|7 Import TemplatesClicking on any of the buttons in the blue areaof the Import View willopenaBlank ImportTemplate( a formatted excel spreadsheet).

8 Import templates will vary in appearance, depending onthedata type thathas beenselected. Open theLocationsBlank Import Template(by clicking the Locationbutton, asshown below, or from the scrollable list)Notethefirst few rows of theLocationsImport templateis shown in the figure below:The column headings are referred to asField Names. Return tothe Locations templateandnote twocellsarecoloured greenIn the Locations Import template, cells A3 and A4are both coloured KeysThe values inacolumnheaded bytwo green cellsareknown asPrimary the Locations Import template,greencells A3 and A4definethe PrimaryKeyField NameLocCode.(SQL users note-thePrimary KeyField Name isLocation_Code).

9 All values in this column ( from cell A5 and below) are Primary Key Keys values are always (Location_Codein SQL) is an abbreviation for Location example ofLocCodemight a borehole ID one record can exist for eachLocCodeper ESdat specific to BH01 is entered in the corresponding rowwithin theLocations ImportTemplate, filling the relevant cells to the immediate right of theLocCode, is not possible to import two records with the sameLocCode (for example, there can onlybe one BH01).A second(unique)LocCode may be BH02, a third may be BH03, and so is important, as it ensures the correct data (for exampleanalytical data)relates to a unique sample TutorialImporting, Editing and Deleting Data` @EarthScience Information SystemP a g e|8 Open theWellsConstructionandFieldParametersBl ank Import Templates byselecting the relevant buttons within the blue area Review theField NameswithintheWellsConstructionandFieldP arametersimporttemplatesNote the different Field Names for different data types.

10 Close the Wells and FieldBlankImport TemplatesCombination Primary Keys Open theWater/NAPLL evelsBlankImport TemplateIn theWater/NAPLL evelsexample(as shown in the figure below), several fields combine toproduce aPrimary Keyfor each record( each row).In thiscase,therecanbeonly one record for aspecificcombination of: Location(LocCode column) Well(WellCode column) Date/Time,& MeasurementMethodThis will form the Primary Key for the record (with the record being, for example, a gaugedwater level of one well on a single occasion, which is a unique measurement).Measurementmethod is included as it is possible that a dip measurement and loggermeasurement may occur at the same time in the same single greencellmeans the data is requiredin that column for each record ( each row),although it may be populated bya the Dry fieldthere are only two options available (true or false), with the default being isthe well is not dry (false).