Import from Excel causing data column loss - can't plot data

[BrettDavies]

Hi all,

When it rains, it pours... now a second issue has arisen - of course when there is a deadline to meet.

I am trying to import an Excel spreadsheet to plot field measurement (geology). I get:

1. random 'database' error messages, especially trying to add a .xlsx worksheet;
2. after clicking 'Display XY data' the dialog opens, but I only see the first few columns and not the data columns
3. sometimes the import has partially worked, but some columns are missing.
4. despite what  happens in 2., when I export the data to a layer I can see the full table, with all the data, but while using 'Selecting by Attributes' shows the data columns, always produces an SQL syntax error window when I've done nothing different from before. I figured it is simply not seeing the columns. When the data columns have been present, the Selection by Attribute works.

I have tried renaming, saving as .xls, partially recompiling the data in a new spreadsheet, but nothing is working. I had frozen panes and thought this may be a problem when saving the spreadsheet, but have found that it makes no difference. I really need this to work in a hurry, so i anyone can assist I'll be very grateful.

I have attached grabs of the dialogs and an extracts (.xlxs and .xls) from the spreadsheet. The extracts also repeat the issue. For some reason the column order in the Table has changed too, I just noticed! The first two data columns should be to the far right of the last column 'Lineation'.

Thanks in advance.

[JayantaPoddar]

You can install Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Ce... to resolve the underlying dbms srror.

The values in "Dip_S0" and many other fields are added as text (not numbers. Check datatype to confirm), So arithmetic operators won't work. To resolve this

i) Either you need to convert all the corresponding cells to number in Excel Sheet before adding XY data,

ii) Or you need to add new fields (Integer) in attribute table and import the values of the Text fields where all values look numeric.

[BrettDavies]

Hi Jayanta, 

The spreadsheet datatype was 'General' and this has always worked previously when importing this data for XY display. I don't see why this shouldn't still work, unless ArcMAP has changed the way it handles spreadsheet (Excel) datatypes. There have been absolutely no conscious changes to my workflow in this aspect of my work. 

Anyway, I went back to the spreadsheet, changed all the relevant cells to 'Number', saved it to a new name, then tried to display the XY data, but nothing changed. So that didn't work. When I looked at the table in ArcMAP the Field data type said 'Text' - it had apparently changed it (read/converted it?) from 'General' datatype in Excel as 'Text': that I don't understand.

I find it interesting that my first imports of Excel files on this project worked just fine, but seemingly gradually began to fail. The first loss was jus the two fields Dip S0 and Dip Dir S0, but I could see the others ok (Excel cell datatype for those was also 'General'). I actually recompiled the data in a new spreadsheet from scratch (~350 GPS stations with multiple data entry per station), displayed it, but still saw none of the fields. 

The good news is, I resorted to your second option and restructured the imported table (where the fields were actually still present) and changed the cell type to 'Integer'. This did work, thank goodness. This seems to me to add support to my view that ArcMAP 10.4.1 is handling this type of data differently somehow. I then checked the Datatype against fields for the layer and now, instead of Integer, it reads 'Long'...! Sorry, but I don't understand what's happening, or why.

So, your reply was helpful, but only part. Changing the data type in cell to Number had no effect and the problem persisted.

Thanks for your reply. Appreciated.

[JayantaPoddar]

LONG is also integer with higher range of values. So, it's correct. Carry on and let us know if you face any other issue.

[BrettDavies]

Thanks Jayanta,

We should learn a new thing everyday, and you’ve provided that one for today!

Thanks you for your assistance.

Brett

Dr Brett Davies BSc PhD FGS

Renaissance Geology

Post: PO Box 111 Mundaring

WA Australia 6073

Mob./Cell: +61 (0)419-817-566

Web: rengeol.com

Email: brett@rengeol.com

Skype: brett.M.Davies

[JoeBorgione]

There is a pattern here on the forum regarding the use of Excel: as a geographer I am trained to notice patterns and trends, whether it's land forms, population distribution, whatever.  I just can't help myself.

If you read these posts and I happen to respond, you'll see another pattern; my thoughts on Excel.  Use it for your expense account; it's not data base management system.  If anything, create a personal geodatabase,  use WIndows Office/Access to import your xls and use it there, and save yourself (yourselves) a ton of frustration....

[BillChappell]

The best avenue I found with Excel is to export it to a CSV file and bring that into ArcMap. Excel lets you use formulas, put text into X,Y fields, which problems plotting points. Another common problem is the Decimal Degrees being changed to an integer field and all the points plotting on top of each other. 

Joe is right, people treat it as a database, it's not. You just need to bring it in and convert it to one.

[BrettDavies]

Thanks Bill (and with a nod to Joe). I'll try that next time.

 My GPS stations are downloaded and saved as .csv files that I then edit to enter field data. It's simple and pragmatic and my clients in the resources industry like having field data in a spreadsheet, so for me it's just a handy way to store data. Can GPS data be downloaded directly into ArcMAP? Never needed to look for that functionality. I will reiterate that, prior to the issue arising last month while doing fieldwork in the (hot) Nubian Desert of northern Sudan, I haven't had any problems displaying the XY data via Excel. Mind you, I only upgraded to 10.4.1 not long before I flew out

As a geoscientist I understand the Excel is not a database and I have never thought of it, nor treated it as one. However, and regardless of attitudes to the use of Excel, I find it interesting that this issue has only arisen now when it was never before a problem - for me at least. I'm guessing something has changed somewhere.

Anyway, I appreciate the responses. I'm back home, jet lagged in Western Australia and it's approaching midnight, so bedtime. Thanks again.

Brett

[BillChappell]

Look into ArcPad for your GPS units, or maybe your GPS software can make Shapefiles. If needed the shapfile DBF's can later be brought into XLS.

[PaulDavidson1]

ArcPad is very useful.

But I believe that ArcPad is being deprecated in favor of Collector.

Esri Blog: using-gnss-receivers-with-collector-for-arcgis/

Using Collector for high accuracy gps

ArcPad official end of life, July 1, 2019

Fortunately for us, we can still get another couple of summers out of our Leica/ArcPad investment.

And probably a few after that even, just without support.  🙂

We have not tried hooking up to Collector yet but probably will next summer when we do most of our field work.