I have added a 97-2003 Excel document to ArcMap to compare data. The file has approximately 3200 records. When I initially open the table in Arc it shows all 3200 records, but as soon as I attempt to sort on a field or even select one of the records the table suddenly only has 2000 records. Some have suggested to go to bottom of the table and select the arrows to scroll to the last record. However, when I do this, the table selection goes to record 2000; the table really does only have 2000 records. If I close out and view the attributes again all 3200 reappear. I did seem to remedy the situation by creating an empty personal GDB and importing the table but I feel like I shouldn't actually HAVE to do this to display 3200 records. Has anyone else encountered this issue?
There are a couple of issues at play here. The first is that ArcMap has traditionally used 2000 records as their standard cache amount from a table. When you load in a table, and this used to apply to shapefiles as well, it would do a first read of the table. If there were more than 2000 records, it would load the first 2k, and show
0 of *2000 Selected at the bottom. If you scrolled down to the bottom, it would load in consecutively more, in groups of 2000, changing the readout to
0 of *4000 Selected, and so on, until you reached the end of your table. If you did a sort, that forced ArcMap to do a full read of the table, giving you a count of all the records in the table.
Here is an example of a query table showing the record count:
Now with the next set of records loaded:
All records shown after sort:
So, that covers where the 2000 count number came from. It is only the first part though, and is not the reason for the total table not loading on. That brings us to the 2nd part of the issue, which is in the lack of an
ObjectID field for tables from sources like
CSV. According to the Esri help: Understanding how to use Microsoft Excel files in ArcGIS
Excel tables behave like other tables that don't have an ObjectID field. This means you will be unable to edit, perform relates, or make selections on the map.
Here is the relevant link about ObjectIDs: Fundamentals of ObjectID fields
What you are seeing as ArcMap not reading the Excel file fully is I think a function of it not having an
ObjectID field. I tried the same thing you did, only with a
.csv file, and have the same result. The image below shows the count of records as 2048.
If you view the statistics for a field, however, it reflects the actual number of records in the table, which is 5964.
My guess is that Esri only maintains an active index and thus functional knowledge of the records in a table, through the
ObjectID field for tables, or FeatureID or other Unique ID specified for spatial tables. Without this, ArcGIS knows the count of a table when a full read is done, as when statistics are calculated, etc., but does not retain that in memory. More importantly, it is not able to uniquely identify any particular row. If you don't know the first 2000 records you displayed, it is then difficult to know if you are showing the next 2000 records, or some of the first set again.
The consequence of this seems to be the inability to read more than the first ~2000 rows of a table in
Excel and other formats. This may be by design based on the above paragraph.
The reason a table works when imported to a
FGDB is that the an
ObjectID field is added to the table and calculated, so full functionality is available.
Where does this leave you? As the other answer said, and others commented, import the table to a geodatabase or convert to a
dbase file. This will ensure an
ObjectID is created and ArcMap will be able to deal with it.
On the other hand, you could use open source GIS software like QGIS, which doesn't seem to have any issues reading these files.
Hopefully this gives you some insight into what is happening in ArcGIS when reading these file formats. I tried to keep the speculating to a minimum, but there is some, as documentation of this particular is scarce to nonexistent.
You could try simply exporting it.
Any time I have this issue (whether adding a table or joining a table to a feature class), an export will solve the issue and your table will show all values. That's why when you imported the table into your geodatabase it worked. I'm not sure why but for some reason whenever I add a table to ArcMap it does what you're experiencing.