Query not returning all records

TychoGranville · ‎03-08-2017

I am working with ESRI tech support on this, I thought I would check with the community simultaneously. This is a known (and open) bug in desktop from 10.1 on. This just started happening to us (we first noticed it this morning), none of our data structures or procedures have changed in some time.

The FC's are in a File GeoDB. This happens on multiple machines using both 10.5 and 10.2 (we have one user with a 32 bit machine so they won't be able to upgrade for a while).

Basically, copy–paste a selected set from the table view to Excel does not select all the records.

For example:

Select more than 2048 features in FC (does not matter if it has joined or related tables or not) --> copy selected features in table view --> paste into Excel.
Result: 2048 records pasted into the spreadsheet (always returns 2048 if any number greater then that selected)

These permutations work –

Select > 2048 features in FC (does not matter if it has joined or related tables or not) --> copy selected features in table view --> sort (by any field) --> paste into Excel.
Result: all records pasted into spreadsheet

Select > 2048 features in FC (does not matter if it has joined or related tables or not) --> export from table view to DBF --> open in Excel.
Result: all records pasted into spreadsheet

Selecting and copy/pasting from a shapefile works correctly.

Not all of my staff are (necessarily) sophisticated enough to figure out/consistently use the workarounds, and we are not going back to shape files. Has anyone else experienced this, and did you have any luck fixing it?

Thanks,

Tycho

JoshuaBixby · ‎03-09-2017

Since it is an existing and open bug, do you have a bug/defect number you can share with us?

AbdullahAnter · ‎03-09-2017

How you get that number ? from attribute table or in TOC?

TychoGranville · ‎03-09-2017

The 2048? That's the number of records that show up when the data is pasted

into an Excel spreadsheet. Or did you mean something different?

Thanks,

Tycho

Tycho Granville, GISP | GIS Coordinator

INFORMATION SERVICES

tychog@co.wasco.or.us | www.co.wasco.or.us

541-506–2658 | Fax 541-506-2641

2705 E. 2nd St. | The Dalles, OR 97058

AbdullahAnter · ‎03-09-2017

sorry, I miss understand,

so why you copy paste? why you don't try to export table ?

TychoGranville · ‎03-09-2017

The data needs to go directly into Excel. ArcGIS will only export tables

into DBF format. Doing the extra steps of exporting the data then importing

it back into Excel is outside the normal workflow of my staff.

Tycho Granville, GISP | GIS Coordinator

INFORMATION SERVICES

tychog@co.wasco.or.us | www.co.wasco.or.us

541-506–2658 | Fax 541-506-2641

2705 E. 2nd St. | The Dalles, OR 97058

AbdullahAnter · ‎03-09-2017

Try to get scroll bar to the end of attribute table before doing copy. and keep the attribute table opened while you paste in excel ( don't close attribute table ).

DanPatterson_Retired · ‎03-09-2017

I am not sure I follow, since there is the Table to Excel tool albeit older,

Table To Excel is able to convert only to Microsoft Excel 5.0/95 Workbook (.xls) format.

but there is nothing fancy that most conversions won't work

If you need higher end xlsx files, then ArcGIS Pro's conda distribution allows you to install xlsxwriter, if you are so inclined to work with python and need to automate a workflow.

TychoGranville · ‎03-10-2017

My staff will not be able to figure out a new tool. They are used to going straight from copy selection in Arc to pasting directly into Excel. Right now they are all using Arc 10.5 and Office 2010 (I'm the only one with an earlier version of Office).

One thing I did forget to mention yesterday (too many pans of the fire) is that if you do a sort (on any field) before doing the copy paste the correct number of records show up in Excel. The response I got back from tech-support this morning is below (hasn't sent me the bug number yet).

-----------------------------------------------------------------------

I did some more testing on the data though and believe that the issue may be with Excel. I will outline the testing I did below.

1. Tested copying the records into Excel with no joins or relates from the shapefile- Success
2. Imported the shapefile into the geodatabase as a feature class and did the same test- Success
3. Exported the table of the 8358 records and joined it to both the shapefile and feature class- Success
4. Related the exported table to the shapefile as well as the feature class- Reproduced issue only with related feature class.
5. Copied the same clipboard of records to Notepad ++ and all 8358 records came across. Excel still showed ~2050.
6. Cleared related tables and tried relating again to feature class- Success, issue did not appear second time
7. Attempted multiple times to reproduce the issue and re-added datasets each time to new map without success

Results above were tested with ArcMap 10.4.1 and Microsoft Office 2016.

At this point I have been able to reproduce the issue once and it was specifically with a feature class that had a related table. However, this was not consistently reproducible and when pasting those same exact records into another text editor they all loaded fine. This may be attributed to having an older version of Excel, but the oldest version we can test with is 2010. That being said, my next step will be to test ArcMap 10.5 with Microsoft Office 2010 since it is the closest I can get to your machine.

------------------------------------------------------------------

I did run his step 5 (with both Arc 10.2/Excel 2007 and Arc 10.5/Excel 2010) on Feature Classes without related tables in 2 different file GeoDatabases on both Notepad ++ and Excel came up with the approximately 2050 records.

TychoGranville · ‎03-14-2017

Below is my final reply from ESRI tech support. Apparently I was mistaken about it being a known bug, but the issue has now been filed as such. It looks like the workarounds they suggested will have to do – hopefully there will not be too much gnashing of teeth when my staff have to change their workflow...

------------------------------------------------------

I did some further testing on a virtual machine running 10.5 and Excel 2010. When testing the copying and pasting of the shapefile and feature class alone it worked fine. However, when there was any joined table to either of the layers the records would not copy over at all. When there was a relate on the data, only the feature class allowed the records to copy over. To get all the records with the relate it did require first running a sort as you mentioned.

At this point I can go ahead and log a bug on the issue if you would like. There is currently no existing bug open related to this issue.

I understand that it can be challenging modify a workflow that everyone is already used to and I apologize for the inconvenience. The best workaround options at this point though would be for you to either run the table to excel tool or export the table as a CSV from the attribute table. I have included a link to the workflow for both below.

Export an attribute table to Microsoft Excel (Option A & C): http://support.esri.com/Technical-Article/000012471

------------------------------------------------

Thanks,

Tycho