I



AquaChem 3.7

Software by: Waterloo Hydrologic Inc.

Table of Contents

Data input and import

Database Management

Data input and import

Creating a file for Importing to AquaChem

Now that we have defined the database field names and structure, we are ready to import a data file from another application. Laboratories often provide data in electronic format such as a database, spreadsheet or text file. A file can easily be created to import into AquaChem from other database programs and from spreadsheets. The file we will be importing is a tab-delimited text file.

The first line of the file contains the Field Names and each row is a database record. The tab delimiter is a useful feature since it allows for blanks in field names and blank data.

Editing the Text File Prior to Importing to AquaChem

• Run Excel and open the achempriceriver.txt file.

The Text Import Wizard should guide you through the file opening process.

• Select the “Delimited” radio button as shown below.

[pic]

• Click the [pic] button.

The Import Wizard will display your data file in columns as shown below.

[pic]

• Click on the check box to select Tab as the delimiter.

• Click the [pic] button.

The Import Wizard now allows you to format each column.

• Click on the column with the Date at the top and select the Date radio button as shown below. (Right side of data preview screen)

[pic]

• Click the [pic] button.

Your data file should now open in Excel. Notice that the first two rows contain headers. We will modify the headers to match those in AquaChem to make the importation easy.

• Type Project in cell A1.

• Type Site in cell C1.

• Type Location in cell D1.

• Type Date in cell E1.

• Delete the column with “Site Type” as the header.

• Delete the column with “Time” as the header.

• To the far right, delete the headers Ca-T, Fe-T, Mg-T, Mn-T, K-T and Na-T and the units below. The data from these columns were concatenated into the appropriate parameter column.

• Click on the 2 in the far left of row 2 to select the entire row and delete this row.

• Save the file keeping the file type as Tab-delimited.

Importing Data to AquaChem from Text files

Now that the data have been saved from the spreadsheet, the file can be imported into AquaChem. Switch to AquaChem and do the following steps.

• Click File

• Click Import ASCII

• Select the file you created. (achempriceriver.txt)

You will be presented with the following dialogue box.

[pic]

This is used to link the database structure in the tab delimited text file to the database structure within AquaChem. The columns presented in the dialogue box are as follows:

Col: column in the text file.

Text File: the database field names in the text file.

AquaChem: the database field names in AquaChem (blank until specified).

Unit: the unit of concentration used for that parameter if different than the specified default, which is displayed in the lower part of the dialogue under “Unit of imported concentrations”.

TIP: If the field names in the text file are identical to the field names in the AquaChem file, they will appear automatically in the AquaChem column. Therefore if the lab provides the file with the field names consistent with your AquaChem database or vice versa, you can save time.

The width of each column can be adjusted with the mouse similar to a spreadsheet. The scroll bars can be used to display those parts of the database list that go beyond the display limits.

Since we edited the text file prior to importing it into AquaChem, most of the field names are the same and little no correlation is required.

• Scroll down through the Assign Parameters window and find a blank in the AquaChem column. These exist for NH4 and NO3.

• Slowly triple click in the AquaChem column next to NH3. A drop down menu appears containing a complete list of parameters. Scroll down this list and highlight NH3. This places NH3 into the AquaChem column if it wasn’t already there.

• Click on another field to retain the parameter in the AquaChem column.

• Move to he next blank field and repeat this process until all unassigned parameters have been assigned.

[pic]

TIP: Clicking on a scroll bar without clicking on a blank field will prevent the selection from being saved. Make sure a blank field is selected with the mouse after selecting from the dropdown list.

Units: The unit of concentration for each parameter name in the imported text file is mg/l unless ug/l is displayed in the list of parameters. Therefore set the default concentration unit to mg/l and individually specify those parameters that require ug/l.

After you have completed all of the links you are ready to import the data.

• Check that the Append radio button is selected in the Options.

• Click [OK].

As the records are imported, you will see a record count in the lower left corner and the progress of importation will be tracked with a progress bar. When the import is complete, your screen should appear similar to the following. The sites and mines will be different depending on what file you imported.

[pic]

This completes the importing process. To check that they imported properly:

• Double click on a few of the records in the Active Records Window.

Importing Data into an Existing Database

Sometimes you will want to import data into an existing database. If you were importing new records, the Append option would be specified.

If you wanted to import additional or revised data for existing records, the Overwrite option would be specified. This situation could occur under the following circumstances:

➢ The results of one or more parameters were in error and had to be re-analyzed.

➢ Results for different parameters were received at different times.

Entering Data Manually

The manual method of entering records Is not at all efficient for large volumes of data. It can be useful from time to time for selective data entry. To enter a record manually:

• Click Records, followed by New.

• Be sure to enter the data required to uniquely identify the sample, (Project, Site, Date). The DBIndex will uniquely identify the sample but other information will be required as well.

• Enter whatever data you have in the appropriate boxes provided.

• Click [Close].

• Read the message that appears

• Click [Yes].

Method Detection Limits

Method Detection Limits are important for assessing the significance of sampling results

Samples may be analyzed by methods whose detection limits are higher than the water quality criteria.

Analytical results may be very close to the detection limit and should be treated with skepticism.

Considering the detection limit is a necessary step in analyzing data.

This step in data analysis can be accommodated in the AquaChem database by adding fields for detection limits.

This data should be requested in the electronic file from the lab.

Adding Fields for Detection Limits

• Open Database

• Click File, Preferences, Data Structure tab on the AquaChem menu.

• Create a new detection limit parameter for each lab parameter.

• Hint: use “+” button in the appropriate Cations or Anions Parameter Group.

• A new parameter appears as: #NEW which requires editing.

• Name the parameter using the AquaChem element or symbol name followed by a dash “-” and “det” for detection.

• IE: Calcium would be Ca-det.

• Enter the Display label. IE: Ca-det

• Enter the Internal name. (same)

• Enter the atomic or formula weight. The Calculate button will not work since this parameter is non-standard.

• Enter the appropriate charge as a positive number.

• Click Apply when finished.

• Click Test

A Test Input Screen appears

• Notice the new Ca-det is listed directly below Ca.

•

• Click Close,

• You will be returned to the Preferences dialogue box.

Save your new parameter

• Notice the new Ca-det parameter is listed directly below Ca.

•

• Click Save.

• Read the AquaChem message that appears.

•

• Click Yes to save.

Create & Save new Detection Limits for each lab parameter.

• Create a new parameter for each of the remaining lab parameter’s detection limit. (Cations & Anions)

•

• Reorder parameters to pair parameters with respective detection limits.

•

• Use the “+” button to begin adding each new detection limit.

•

• Save and Close when complete

Importing Detection Limits File (Not covered in exercise)

• A Tab delimited text file can be used to import detection limits into existing database.

• Click File, Import ASCII in AquaChem menu.

• Browse to the project directory & Select file containing parameter detection limits.

• Correlate detection limit field in text file with AquaChem parameter.

• Check the Overwrite existing samples radio button.

• Click OK to import the file.

• Once the file has been imported, double click on a record to verify that the detection limits were imported.

• This opens a data input form for the sample that was clicked.

• Observe the detection limit fields.

• Click Close to return to the Active Records window

Importing Detection Limits

Ideally, Field parameters were given to the lab so they can be entered into the database file received from the lab.

• Field data can also be entered by creating database parameters in AquaChem as outlined for Method Detection Limits.

• Next create a Tab Delimited file with the database index number (DBIndex) in the first column. Include the unique sample identifiers (Site & Date). Add field data in subsequent columns.

• Correlate the text file fields with AquaChem’s database fields.

• Use the Overwrite existing samples option and click OK to Import the data the same way the MDL file was imported.

Database Management

Viewing the Data in Spreadsheet format

When reviewing raw data in a database, it is usually most effective to view the data in a spreadsheet format. AquaChem is set up to select the parameters you want to see in this spreadsheet view. To view as a spreadsheet:

• Click Records

• Click View as Spreadsheet

You will be presented with a window that allows you to customize the parameters you view and to save and load pre-set configurations for spreadsheet views.

[pic]

Parameters can be removed or added using this window in the same manner that you set up the original database structure. Once you have defined your spreadsheet view, it can be saved for future use by clicking Save, using a filename you provide.

Editing Database Records

Small changes are sometimes required for database records. These changes can be made in Spreadsheet view as shown in the previous section. However, edits can also be made on a data input form. To edit a record form:

• Double click on a record in the Active Record Window. The Record Form will appear as shown below.

[pic]

The measured values are presented automatically. The advantage of this view is that you can conveniently see most of the data fields, which are sometimes difficult to see in a spreadsheet view. Any value can be changed by highlighting the value and entering a new one.

• To scroll through the records one at a time, click the > icon in the lower right of the dialogue box.

• The < icon reverses the direction of scrolling.

• To go to the last record, click the >> icon.

• To return to the first record, click the 10 GPM & Cond > 3000 umhos/cm).

• Click Records, View as Spreadsheet on main menu.

• Remove everything except Site, Date, Flow and Cond.

• Click Save to keep the configuration.

• Click OK to view spreadsheet

This completes the exercise on database importing and management

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download