When I use the Data Transformation Tool, some of my Excel Columns are blank?

QUESTION:  I’m having an issue where some of the data in the Excel file I’m using in the tool shows up normally but a couple of the columns are blank in the tool view but fully complete in Excel.  I tried changing the formatting in Excel and retrying a few different options/combinations but none have worked so far. Is there a certain Excel formatting that has to be used to get the file to load properly in the AIDV tool?

I’ve seen this happen if there are odd characters, a mix of text and numbers, or a Blue Moon.
This is what I usually do when this bites me.
  1. I verify that there are no ODD or Multi-line Header Names for the columns.
    • Keep your Header names simple.
    • It is useful to comply with our standards since the Tool will automatically detect them.
    • Use X, Y, Z, Top, Bore (or Boring or Well), Ground, Depth, Elevation, Lithology
    • You cannot use Non-Ascii characters. Even though EVS Versions after May 2018 support Unicode character sets for labeling, we don’t support them in our data files.
    • If any of the above was an issue, make these changes and try it now first.
    • Otherwise continue on to the remaining steps
  2. I verify there are no blank cells anywhere within the columns I need for all rows of data.
  3. I DELETE several rows below the last row of actual data (in case there are any non-visible characters in cells)
  4. I DELETE any unused columns
  5. I DELETE several columns to the right of the last column of actual data (in case there are any non-visible characters in cells)
  6. I select the entire block of cells (rows/columns) containing my data
  7. I paste it all into a new sheet AS VALUES
  8. I use that new sheet with the Data Transformation Tools
I know it may seem that there are some unnecessary steps here, but when I’ve had very problematic excel files, it has taken all of these steps to overcome the issues.