topic Re: Query not returning all records in Data Management Questions

Query not returning all records

TychoGranville — Wed, 08 Mar 2017 20:17:31 GMT

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

Re: Query not returning all records

JoshuaBixby — Thu, 09 Mar 2017 16:05:52 GMT

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

Re: Query not returning all records

AbdullahAnter — Thu, 09 Mar 2017 19:42:40 GMT

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

Re: Query not returning all records

TychoGranville — Thu, 09 Mar 2017 19:48:54 GMT

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

Re: Query not returning all records

AbdullahAnter — Thu, 09 Mar 2017 19:58:41 GMT

sorry, I miss understand,

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

Re: Query not returning all records

TychoGranville — Thu, 09 Mar 2017 20:24:18 GMT

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

Re: Query not returning all records

AbdullahAnter — Thu, 09 Mar 2017 20:31:58 GMT

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

Re: Query not returning all records

DanPatterson_Retired — Fri, 10 Mar 2017 04:08:41 GMT

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.

Re: Query not returning all records

TychoGranville — Fri, 10 Mar 2017 17:20:43 GMT

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.

Re: Query not returning all records

TychoGranville — Tue, 14 Mar 2017 20:03:03 GMT

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

Re: Query not returning all records

Lake_Worth_BeachAdmin — Wed, 15 Mar 2017 18:20:40 GMT

there is a data conversion.... Table -> Excel. Give that a try.

Re: Query not returning all records

JoeBorgione — Thu, 16 Mar 2017 13:16:57 GMT

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.

Sorry if I sound a bit short, but it's 2017; data and work flows are constantly in a state of flux. I gotta go with the Tech Support response: People need to adapt to this changing enviroment and accept the challenge of learning something new. I know it's tough, but it is what it is.

Years ago (As in the ArcInfo days) I got into a rather heated discussion with the director of a department: he posed the question "Can't you make it easier?" My response was "Can't your people think harder?" A few years before that while in college, hand held calculators were just hitting the scene. A group of us approached our Calculus Professor and asked if we could use them in class. He responded with a pearl of wisdom I chose never to forget: "Sure, you can use them. But you still need to learn the calculus. Don't become a slave to a machine". I think of him eveytime I pass someone on the freeway while they are texting....

Google "Who Moved My Cheese Free PDF" . There is a Google Docs copy of it floating around. This should be required reading for anyone in the database, GIS, IS or related fields; especially those who fear change...

Re: Query not returning all records

TychoGranville — Thu, 16 Mar 2017 16:40:48 GMT

I will have to disagree with you on that one.

I view GIS as being a tool for line staff to use. The guts of how it works should be invisible to staff. I want them to be concentrating on their primary work area (planning, tax assessment, etc.) and not have to spend time trying to figure out how these tools work. The information I provide should fit easily into their workflow, not mine. I don't know how to write staff reports, I don't expect them to know how to write queries against GeoDatabase tables.

(We do have staff who do analysis against our raw data, I just don't expect people to have to do that in order to do their jobs)