How can I troubleshoot an 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.

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.

ImportErrorPopup

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.

field too small import error

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

edd  bad format in columns

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 https://staging.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.

allow null dates

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.

dupes Supersedes

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, This email address is being protected from spambots. You need JavaScript enabled to view it. can assist you in resolving the import issue(s).

#22Edited 9 months ago