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.
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.
If you find new parameter that is not in your database, it needs to be added either manually or during the import.
On occasion, 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 "Import Problem" 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.
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.
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, 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 Columnar Excel/CSV import will allow you to map the incorrect fields to ignore the order and/or misspellings.
Special Characters -- Some characters or symbols can cause problems when used in your data. The issue is that Access may treat them a reserved program code. The primary character that causes problems is the single quote symbol that can also represent a foot symbol, but there are others. See the FAQ https://geotech.com/resources/faq/using-geotech-software/general-questions/are-there-reserved-symbols-in-ms-access-that-will-cause-problems for more information.
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.
Null (Blank) Dates -- All normal samples should contain the sample date in the SampleDate_D field. Many times however the lab will not include a sample date for lab QC samples. There are two ways to work around this. If the sample in questions is truly a lab QC sample just check the Allow Null Dates (shown below) in the Samples section of the Import Wizard - Data Checking Options form, The other way is to copy one of the lab dates (LabRecvDate_D or AnalDate_D) into the SampleDate_D field.
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.
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).
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.
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 .
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.
When importing data using an EDD, Enviro Data requires certain fields to be populated. If the EDD you are trying to import has one or more of these required fields that are empty (null), it will attempt to fill these with a preselected "default" value from the ImportDefaults table. If the lookup values in this table have not been selected prior to the second import form, Import Wizard – Data Checking Options form, you can get the error message shown below.
The Set Defaults button (highlighted below) is found on the Import Wizard – Data Checking Options form. You can reach this form during an import by clicking Next (instead of Finish) after you have selected a file and format for your import.
When you click the Set Defaults button it opens the Edit Defaults form shown below. All the defaults dropdowns must be populated with a value from your Lookups tables by clicking the dropdown for each field and selecting the value you wish to use for the default. Once the defaults have been populated, they will be saved until they are changed.
Note that you cannot exit this form until all defaults are set.