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.