Slider

Importing Data

Generally, it is recommended that you do not modify the reporting units from the lab as these can be easily converted for reports and exports. However, if you desire to change them during the import, this can be done from the second import form, IMPORT WIZARD – DATA CHECKING OPTIONS. Under the Analyses section check the Convert radio button in the Reporting Units section. If you wish to be notified of conversion errors, also check the Notify of Unsuccessful Conversion check box. Note that for a conversion to take place, a Matrix or Parameter Units must have been chosen for each parameter being imported

During an import, the wizard checks to see if all the parameters are in the Parameters table. If a match for the parameter is not found with one of the above options, Enviro Data then displays the parameter match screen to assist with assigning valid parameter names, if that option is selected.

Enviro Data allows you to specify alternate spellings, or aliases, for parameter names for use during import and output. These values are specified the same way as other lookup values, using the Parameter Aliases button on the MANAGE LOOKUPS form. The intention of this feature is to handle situations where the laboratory is required (for example, by regulations) to report the parameter using a different spelling from the one in the Parameters table. An example would be an alias of 1,2-Benzanthracene for Benz(a)anthracene. It is not intended to intercept misspellings, although it is sometimes used this way. Entries in the ParameterAliases table can either have a site associated with them, in which case the alias is used only for that specific site, or can have a site number of zero, in which case it is used for all sites.

This check is done by spelling (or CAS number if that options has been checked).Just be careful as in some cases, the CAS number may not be unique or standard. You can also import by Outher Parameter ID (such as STORET) if you are using this field.

Parameters section of the Import Wizard form

If some parameters could not be found in your database, you will be presented with a list of these. You are given the option to either match the "new" parameter to an existing one, add the parameter, or create an alias for an existing parameter.

It may be best at this point to take a screen shot of this list and then cancel out of the import. Using this list, you can research to determine if each is already in the Parameters table under a different name. If so, you can then create an alias for that parameter either during your import or using the PARAMETER ALIAS form accessed from Manage Lookups

Parameter Alias Form

If you find new parameter that is not in your database, it needs to be added either manually or during the import.

At times, an import file will not import, either producing an error, showing many duplicate / superseded results, or having some other problem. Sometimes these issues can be hard to track down. The most common issues are "ImportProblem" error/problem, bad dates, missing information, and occasionally a corrupted Excel file.

If you get to the EDIT IMPORT FILE form and see errors of problems in the ImportProblems form see the FAQ on How do I fix an "ImportProblem" error/problem during import.

The rest of this section will provide a few steps that might help identify and eliminate other types of problems.

There are many available import formats so first and foremost, make sure you have selected the appropriate format in the File Type and Format box on the IMPORT WIZARD form. While Version 8 will import an EDD in the DTS2012 format, but you have to select that format. If the EDD is not in the correct format, you may see the error message below.

ImportErrorPopup

When the import file does not precisely match the format chosen you may be able to use one of the two User-Defined formats might do the job. The two User-Defined imports are Columnar and Crosstab. The former can be an Excel or delimited file while the latter is an Excel file.

We recommend that an Excel EDD file only contain a single worksheet unless multiple worksheets are specifically supported for the chosen import format. If the file you are attempting to import has multiple worksheets and/or the first worksheet in that file is not in a recognized import format you can see the error below.

field too small import error

If your file is in one of the supported formats but still fails to import, there are several steps you can take to try and resolve the problem. Most of these require that the import file be in Excel format and require some knowledge of Excel.

1. Open the DTS Import file that you are checking in Excel.
2. Click in the upper left-hand corner to select all records. Keep all records selected through Step 6.
3. Change the font and font size to something easy to read.
4. Format Cells by turning off Wrap text.
5. Turn on Filtering
6. Double click one of the dividing lines between columns to resize them to display all contents (again without wrapping)
7. Right click in one of the Excel columns (A, B, C, etc.) and choose Unhide so all columns are displayed. Also do this in one of the rows (1, 2, 3, etc.) to display any hidden rows.
8. Open file C:\Enviro\Edata8\DTSFormat8.xls
9. Highlight the top row with the field names in this new file and copy it.
10. Insert this copied row into the top row of your EDD and compare this new header to the one that came in your import file.

If the header row has incorrect fields due to spelling or the fields not in the correct order, you really should contact your lab to have them supply the EDD in the correct format. However, if you need the file imported in a hurry, the user-defined import will allow you to map the incorrect fields to ignore the order and/or misspellings.

Dates – Date fields require special attention because Access can be problematic when importing dates from Excel. Dates in incorrect formats need to be corrected prior to import. This can be done with your import file in Excel. First click once above the SampleDate_D (or other date field) header to select the entire column. Next, right-clicking on one of the dates and choose Format Cells. In the Format Cells form you can then select Date in the Category window and the type of date in the Type window.

Filtering – To set up filtering, click in the upper left-hand portion of the spreadsheet (above the 1 for rows and to the left of A for columns) to select all cells in the worksheet. Next, on the Home ribbon, click on Sort & Filter and choose Filter. By applying filters to each column missing or incorrect data can be identified. Once a column is filtered, use the dropdown arrow in the column you wish to exam to view the contents. If Blanks are shown, uncheck Select All and then check blanks to see if data is missing, or if this is just an empty row of data.

Sorting – Once a Filter is set, you can sort the columns individually, both in ascending and descending order, by clicking on the drop down at the top-right of each column. By sorting, you can verify that the data range is accurate, there are no alphabetic characters in date or numeric field and that all (required) fields are populated correctly. Blanks should also come to the top when a column is sorted in ascending order.

Glitches in the File – At times, there can be a problem in the data that cannot be seen visually. These are usually caused by some data corruption in the file. Saving the file in a comma delimited CSV format and importing it in that format may correct these glitches.

Copying to Another File – As a last resort, you can copy the data into a new spreadsheet, taking care to only select the cell range that only contain your results.

User-Defined Import - Many times an import file may have missing fields in the header, or one or more field names is spelled wrong. When this happens, you can use the User-Defined import with either an Excel or CSV file.

For duplicate / superseded issues, we recommend checking to see if the FieldSampleID, the LabSampleID or AltSampleID fields are populated. If they are, choose the most appropriate one of those other fields in the first Import Wizard form for instead of using Auto.

dupes Supersedes

If you are unable to import your file after going through the above checks, Geotech Support can assist you in resolving the import issue(s).

The last two data levels in Enviro Data are Samples and Analyses. These are generally imported together using an Electronic Data Deliverable (EDD) file from your lab. This is convered in depth in the Getting Started and Documentation available from either the EDITOR or VIEWER main screen and in the EDITOR Tour. The steps shown below are general and provide an overview of the process.

1. Using one of the DTS format files, such as DTSFormat8.xls, as a template, create an Excel sheet with your samples and results information. SuccessfulImport8.xls is an example that you can consult when populating your own import files. These files are located in the Enviro\Edata8 folder in your directory. Notes: All lookup code columns with red captions must be populated. You may use “z” as a default code for lookups (SampleTypeCode, etc.) SampleTop, SampleBottom, DuplicateSample and Superseded can be left blank or default to zero. If you have digital data in a columnar format, but different from one of the Enviro Data DTS formats, you can use the User-Defined Excel option in the Import Wizard. Contact Geotech if you need help with that.

2. After constructing your import file, click on Import Data on the EDITOR MENU.

3. Select the File Type and Format that matches your import file format.

4. Click Select at the right of the File Path and Name text box. Navigate to the file you made and click Open.

5. Click Finish.

6. The HELP MATCH STATIONS form may open if there’s a discrepancy in spelling Station names. You can use the dropdown on the right to find the correct station, or click Add Station to add a new station, if needed. After matching stations click Finish.

7. Help screens for matching or adding new parameters and matching reporting units may appear if needed.

8. When all is correct the import will run to completion and provide a summary of what was imported. Additional report screen may be displayed if they were selected. .

Duplicates and Supersededs

If you have imported an EDD and received a large number of Duplicated or Superseded records, you probably should undo the import file and learn more about how Duplicates and Superseded results are handled in Enviro Data before trying to import them again.

Undoing an import is simple and can be done by going to the Import Wizard form and clicking on Undo A Previous Import. This will display an list of EDDs imported where you can select the problem file. Before clicking the Undo Selected Import button be very careful that you select the correct file and that you have a copy of the file being deleted so you can re-import it later. Typically, all you need to do is to select a different choice from the Duplicates & Supersededs radio buttons for a new import.

An explanation of how Duplicates and Supersededs are handled follows.

Enviro Data allows duplicated data at both the sample and analysis levels. The Duplicates and Supersededs section of the Import Wizard - Data Checking Options form provides several options for handling of duplicated data. If you do not have the Duplicate and Superseded fields correctly populated in your import file, you MUST select one of the following options, which uses additional fields to determine duplicate samples.

Duplicates and Supersede Options

Options for Duplicates

Auto - This option looks for unique samples based on StationName, SampleDate_D (without time), SampleMatrixCode, SampleTop, DuplicateSample, SampleBottom, FilteredSampleCode and QCSampleCode or LabSampleID (if QCSampleCode is ‘Unknown’). If all of these are the same for the next sample, then the DuplicateSample field is incremented.

The recommended import format contains a QCSampleCode field. The import logic utilizes this field for ordering and assigning the DuplicateSample number. Enviro Data groups samples by the unique fields, orders the records based on the DuplicateOrder field in the QCCodes table, then increments the DuplicateSample number by comparing the QCSampleCode of each sample. These codes can be entered by the lab or by the data administrator before import. If the QCSampleCode is not known or is left blank, it defaults to “Unknown”. The code then compares the LabSampleID numbers of each sample to set the DuplicateSample number. 

Field ID - This option looks for unique samples based on StationName, SampleDate_D (with time), SampleMatrixCode, SampleTop, SampleBottom, DuplicateSample, FilteredSampleCode, QCSampleCode and FieldSampleID. If QCSampleCode is ‘Unknown’ then it just looks at FieldSampleID. If all of these are the same, the DuplicateSample field is incremented.

Alt ID - This option looks for unique samples based on StationName, SampleDate_D (with time), SampleMatrixCode, SampleTop, SampleBottom, DuplicateSample, FilteredSampleCode, QCSampleCode and AltSampleID. If QCSampleCode is ‘Unknown’ then it just looks at AltSampleID. If all of these are the same, the DuplicateSample field is incremented.

For these import options, correctly assigning QCSampleCode is crucial to identifying original vs. QC data as the QCSampleCode field is used to correctly identify original data vs. QC data.

Clients who are dealing with large amounts of QC and multiple samples per day may want to consider removing the Duplicate = 0 and Superseded = 0 defaults on the SELECT DATA screen. If you need to remove this, be aware that this will be changed back to the default with any new program updates. Call Geotech for more information about changing this program feature.

If you are importing data with QC sample information, you must populate the DuplicateOrder field in the QCCodes lookup table before importing. The Import Wizard uses this field to set your duplicate values based on the duplicate order. For example, if the duplicate order for your QC codes were Original = 1, Duplicate = 2, and Split = 3, then for samples with those codes you would end up with duplicate sample numbers of 0, 1, 2 respectively.

EDD - The next option uses the duplicate and superseded values as they are contained in the import file. Use this option if whoever is providing you with the data sets the values correctly in the EDD.

Lab ID - The final option uses the laboratory identification number from the import file to determine duplicates and superseded values.

Set Initial Dup/Sup to Zero - This option sets both the Duplicate and Superseded fields to zero prior to import.

Options for Superseded Analyses

Superseded values for are set using the unique key of SampleNumber, ParameterNumber, Superseded, AnalyticMethod, LeachMethodCode, ReportUnitsCode, Basis, FilteredAnalCode, QCAnalysisCode and LabSampleID. Unless the EDD option is checked, when a new analysis is imported matching this unique key is imported, the Superseded field for the original analyses is incremented.

All lookup values in an EDD being import MUST have a match in the appropriate lookup table. The most common one missing is a QC Code but you may encounter others like the missing Basis code shown below. You may also find multiple missing lookups and/or multiple pages* of problems, espeically when importing data from a new lab or project. 

After completing the initial steps to import an EDD file, you may see the EDIT IMPORT FILE form with errors or problems listed. If there are errors, they will be in the ImportProblem field on the form below .

Edit Import File form showing errors in the ImportProblem field.

In the case above, there is a value for "Basis" in the EDD file being imported that was not in the Basis table. To find the problem code, scroll over in this form until you find the Basis (or your problem) field and determine what the flagged value is. 

If the issue(s) can be corrected with a simple change, replace the problem record using this form and click Finish. (You can also use Search and Replace to change multiple records at the same time.) If editing with this form is unsucessful, you will need to stop the import, correct the EDD file, and try the import again.

If the problem is a missing value in one of the lookup tables, you will have to stop the import and add the missing value. You can add (or edit) the missing/incorrect lookup values from the EDITOR --> Manage Lookups and click on the button for the table with the problem. (You may have to click the Show More. in the MANAGE LOOKUPS form to find the specific lookup table button you need.)  

Once all correction have been made, try the import again. 

* WARNING: Check the Access Record Counter in the lower left of the EDIT IMPORT FILE form to check if there are additional error pages.