Excel pro tip: Locate and reset the last cell on a worksheet

I had the weird situation, that an Excel source at a SSIS job did produce almost 100k output rows whereas the read Excel sheet did only contain around 5k rows of data. After dissecting the results of enabled Data Viewers I found out that the XLSX documents seems to have some internal metadata which also keeps a pointer to the last data cell. Or at least what Excel thinks what this last cell might be. This pointer however, can be read and interpreted (for a number of valid reasons) which would lead to the behaviour I observed.

Last data cell in line 93146? Nope

Deleting all seemingly empty rows did not do the trick, so I consulted Google which lead me to the following knowledge article which finally cleared everything up (literally): https://support.office.com/en-us/article/Locate-and-reset-the-last-cell-on-a-worksheet-C9E468A8-0FC3-4F69-8038-B3C1D86E99E9


Pressing CTRL + END directly jumps the the last populated cell according to Excels internal pointer. In order to reset this, select all the rows or, even easier when applicable in your situtation, select the whole sheet, and finally clear the (visual) formattings. Done.

Oh, for the case your are also a curious one: I peeked into the XLSX file (which is an ordinary ZIP archive if you didnt know) and found the internal “pointer” I was suspecting. Inside the sheet XML files which are located under /xl/worksheets directory, there is a <dimensions ref="A1:D93146"/> right as first child element under the <worksheet/> root element that holds the misleading information. 😉

Beware of Dynamics 365 Web API: When attribute and entity have same names

With increasing maturity of the OData Web API exposed by Dynamics 365 CE/CRM its adoption does also steadily increase. From my perspective especially from JavaScript client codes. While the Web API doubtlessly has its sweetspots, it also comes with a lot of pitfalls and limitations (yet) we must get comfortable with and become aware of.

One of these pitfalls which I want to recall today, was already covered back in 2017 by Tip #970: When attribute and entity collide and goes like this:

While (technically) the CRM platform allows an attribute to have the same name as the entity it is defined on, the OData Web API is not capable of handling this and simple throws an “Could not find a property” HTTP 400 error.

Keep in mind when designing the entity model: Avoid naming collisions by never giving an attribute the same schema name as its parent entity.