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. This check is done by spelling (or CAS number if that options has been checked). If some are missing, you will be presented with a list of missing parameters. 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 paramter.

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. If you find new parameter on the list, then you will know that it needs to be added during the import. 

Parameter Alias Form

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 bad dates, missing information, and occasionally a corrupted Excel file. This section will provide a few steps that might help identify and eliminate the 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, you have to select that format.

Sometimes you cannot get a file to import because it does not precisely match the correct format for the format chosen. In this case, 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 worksheet are specifically supported for the chosen import format.

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.

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.

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).

Occasionally, a non-standard import file will result in an error message to be displayed. Sometimes these messages generated by Access are difficult to interpret.Several are discussed below.

The most common is an the error message below that indicates that the EDD file you are trying to import is not in the import format you specified.

Popup shown when there are problems with the EDD being imported.

This could result from no data in the EDD file, missing fields, mislabeled fields or simply the wrong import format was chosen. It could also be the result of dates in the wrong format.

In the DTS2010 import below, Row 1 contains the correct headers while Row 2 contains incorrect headers for this import. Cell D1 should have contained SampleTypeCode but because this was left out, the rest of the headers are in the wrong positions. Additionally, the field "Duplicate" is labeled as "DuplicateSamples". A number of other fields in this format were also left out. 

Bad Import Format

(Note: the two headers are only shown for comparison. Only one is allowed.)

Another error that has been seen is shown below.

field too small import error

This error can be caused by having a multiple worksheets in the EDD Excel file you are attempting to import and the first worksheet in that file is not in a recognized import format. This generally occurs when using the User-Defined imports and not with the DTS import formats. The solution is to copy your EDD into a single worksheet Excel file.

We recommend that an Excel EDD file only contain a single worksheet unless multiple worksheet are specifically supported for the chosen import format.

For more help see How can I troubleshoot an Import?

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 section tells you how to do this.

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. Another help screen for matching or adding new parameters may appear.

8. And one for matching reporting units, and perhaps others. When all is correct the import will run to completion.

Duplicates and Supersededs

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.