Importing Data

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, Lab File Format, and User 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. .

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 lookup data, 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.

If the EDD is not in the correct format, you may see the error message below. 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 that matches your lab's file. While Version 8 will import an EDD in the DTS2012 format, you still have to select that format.

When the import file does not precisely match one of the supplied formats, you may be able to use one of the two User-Defined formats might do the job. These 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.

Setting up your EDD file in order to check it for problems

If your file is in one of the supported formats but you are still having problems, you can use the steps below to organize the EDD in Excel to help identify the problem(s).

Since most of these steps require some knowledge of Excel, we have prepared a 5:30 min YouTube video. troubleshooting_edd_setup.mp4, to assist you in performing the steps below. (Note that the video does not follow the exact steps sone. Also, when recording the video, each time the video was paused, Excel lost focus and we had to re-select the entire sheet.) If you are familiar with Excel, you likely can skip the video.

1. Open the DTS Import file that you are checking in Excel.
2. Make a note of the bottom-right or last cell in this spreadsheet. This would be the bottom row number and the far right column number.
3. Click inside the spreadsheet and using the keyboard press the CTRL and Home keys together to place the cursor in cell A1.
4. Now press CTRL and END keys together. This should take you to the cell identified in Step 2 above. If it does not, the spreadsheet needs to be fixed to remove these "extra empty" data cells before importing. These cells could be extra rows and/or extra columns.
5. Insert a blank row into row 1.
6. Open the import example file for the import format being used. This might be C:\Enviro\Edata8\DTSFormat2012.xls or C:\Enviro\Edata8\DTSFormat8.xlsx depending on your import format.
7. Highlight the top row with the field header names in the example file and Copy it.
8. Back in your import file, in the blank top row you created above, Paste the example DTS header you just copied.
9. This will give you two rows of headers, a correct set in Row 1 and the original ones in Row 2.
10. Click in the upper left-hand corner of the spreadsheet to select all records.
11. All records must be selected for the following steps through 14 to complete correctly.
12. Right click in any cell and select Format Cells then in the Alignment tab make sure Wrap text is not checked.
13. Right click in any one of the columns (A, B, C, etc.) and choose Unhide so all columns are displayed. (If all records are not still selected go to step 8.)
14. Right click in any one of the rows (1, 2, 3, etc.) to display any hidden rows. (If all records are not still selected go to step 8.)
15. Change the font and font size to something easy to read. We personally like Calibri 11 or 12.
16. Turn on Filter. (All columns should now show a Filter dropdown.)
17. Double click one of the dividing lines in the header between columns to resize each column so all contents are displayed (without text wrapping)
18. Compare EACH columns header in Row 1 to the original header in Row 2 and correct any errors.
19. Note, if you have selected the wrong example format header or, your import format is non-standard, you will need to take extra steps to resolve this.
20. Check to make sure each required field identified by RED font is populated.
21. Using Filtering and Sorting to compare the data in the file to make sure it matches the required format.
22. If all the header names are present and correct, but they are not in the correct order or you are missing some, you may have to use the User-Defined format.

Correct Headers - 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.

Formatting of Columns -All the data in each column must be the same data type. You can check this by highlighting a column and right-clicking in the header cell and choosing Format Cells. This will tell you what the data type is for that column. If the column should be Date, that should be the format shown. If not, you may need to change the format. You can sometimes see a formatting problem simply by looking at the data in the column. For example, if there are mixed number and text values, you will see this by the justificaiton of the content. See the example below.

To correct this error, highlight the entire AnalyticMethod column then right-click the AnalyticMethod column and select Format Cells. On the Number tab of the Format Cells form change the Category from “Number” to “Text”, and then click OK and make sure you save your file.

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 Are there characters or symbols that will cause problems if I use them in my data? 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 – You can use Excel's Filter function to see what data is in each column and also filter and/or sort it. 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.

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.

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.

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

During an import, the wizard checks to see if all the parameters are found in the Parameters table. If a match for each parameter was not found using the options shown below, Enviro Data then displays the parameter match screen to assist with assigning valid parameter names. This form 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 reports 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 import check is Accomplished by checking the spelling or CAS number (if the Use CAS Numbers option has been selected). Just be careful using the CAS number option as the CAS number may not be unique or standard for a specific parameter. You can also import by Other 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.

During an import, the wizard checks to see if all the parameters are found in the Parameters table.

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 a 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 & Supersedes radio buttons for a new import.

An explanation of how Duplicates and Supersedes are handled follows.

Enviro Data allows duplicated data at both the sample and analysis levels. The Duplicates and Supersedes 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.

most common field causing errors is QC Code however, you may encounter others like the missing Basis code shown below. You may also find multiple missing lookups and/or multiple pages* of problems, especially 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 is not in the Basis table. To find the problem code, scroll right in this form until you find the Basis (or your problem) field and determine what the flagged value is. 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 corrections have been made, try the import again.

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

See When importing data why am I getting the error "The default Analytic Flag [or some other field] is invalid"? (geotech.com)

When the EDD you are importing is missing a field or has a field name misspelled, this will cause an error. To correct it, check the EDD against the one provided by Geotech and correct any missing fields or misspelled field names. If you still have difficulty, you can try importing using the User Defined Columnar Excel/CSV import format.