Yamauchi went nuts and screw up. That's why.
Major reason why Nintendo refused to use CDs for N64 was because they would loose the control over manufacturing demand and the profits over this scheme. Namco complained about this control during the NES days and tried to bypass it (Tengen as well), shaking relations with Nintendo afterwards, that's why they heavily supported the PC-Engine and went all in with Sony after PSX came out.
This also was a major reason why Nintendo and Square relations shaked, as Nintendo refused to provide cartdriges with enough storage necessary for their games, reason why Secret of Mana had content cut at last instance. In contrast, Enix had larger storage for their games as Nintendo knew how important Dragon Quest was (and still is) for the japanese market. Despite Square games being hugely succesful and even worked together for Super Mario RPG, Nintendo always treated them as a stepchild and never thoughr FF (or anything else from them) would be something to be missed. Super Mario RPG was also another reason to shake Nintendo/Square relations. It was initally settled that Square would publish it, but Nintendo changed their mind and decided to publish themselves, frustrating Square in return. Final Fantasy VII was initially a 64DD project, an add-on that Nintendo developed especially for the RPG devs, mostly with Enix's Dragon Quest in mind. Nintendo was aware that the default N64 hardware and cartdriges didn't had the enough storage data to carry on the RPG genre, something huge in Japan, so they decided to use the 64DD to keep them at their side. Unlike the official story, Square didn't left because of the N64 lacking CDs, but rather because how badly mistreated they were by Nintendo. Sony decided to give them the red carpet treatment and couldn't refuse, so they decided to jump away from Nintendo and support the PSX as an AAA dev, something they would never have with Nintendo. This decision started a domino effect, causing pretty much every single big japanese dev, with few exceptions, to leave Nintendo and work for the competition. After Nintendo lost both Square and Enix, their 64DD project got a shot in the heart and Nintendo pretty much said "fuck this, I don't need it anymore", and treated it like shit when it came out.
Of course, excluding japanese devs from their "Dream Team" was also a huge factor for why they got alienated on them. It was a clear message that Nintendo was snubbing them.
Control over cartdrige manufacturing demand is a key reason why Nintendo never abandoned this type of media, was always reluctant to use CDs/DVDs as media and tried to use an "alternative" media, such as the GCN's mini-DVD. They have huge profits with this policy and still is a reason why it scare away some devs, despite how huge seller they are. You can see Wii and Switch selling like hot cakes and still struggling to get support.
Nintendo realized how toxic this decision was and how severely shaked their position on the japanese market and their relations with third-parties, so they decided to amend the damage on the GCN/GBA era. This is how Nintendo managed to bring Square back, get the Capcom 5 exclusive deal, work more closely with Namco... pretty much every single major japanese dev they lost to the N64 screw up.
On the other hand, Nintendo handled the N64 nicely in the western market. There wasn't the third-party mass exodus in this region and the system got solid support from western devs, some of then big exclusiviness, such as Star Wars, Turok, WCW vs NWO/WWF, Mission Impossible, Duke Nukem Zero Hour, Doom 64, etc. Pretty much every single big western third-party, with a very few exceptions, supported the N64. That's the reason why N64 was a significant success in the western market and sold almost the same as the SNES. Some tag the N64 as a failure. In Japan, yes, but no in the west, and it's great example from when Nintendo and western third-parties had good relations and how important it was to keep their pace on the western market. They consequently lost it after Nintendo decided to repair the damage from N64's japanese mass exodus and tried to bring them back, alienating western third-party support in return, as it was significantly lost during the GCN days and afterwards. Only now, with Switch, it seems they are starting to come back or treating Nintendo more seriously, again.
N64 took a heavy hit on Nintendo's management logistics and changed them completely. It was a key reason why Nintendo got skeptical toward third-party support and tried to depend mostly on their own efforts, alienating most of third-parties in return to support Nintendo systems. Nintendo decided to change this after how bad this backfired on them, culminating into two huge busts on every single region (GCN and Wii U), or even terminating a life cycle prematurely (Wii), resulting into an unprecedent three years in a row in the red situation. This aftermath made Nintendo reevaluate their strategy and logistics.
Every dumb decision Nintendo made from 1996 onward have it's roots and can be linked to the N64's japanese failure.