I was assuming the whole transaction history was stored in the wallet.dat file, since when I manually swap my personal and business wallet.dat files and open the client they show their correct transaction histories.
I'm pretty sure the wallet rescans the entire blockchain for transactions that match the addresses that are contained in the wallet.dat when you swap in a wallet.
He's missing something though.
OP, you need to fix your client before any of this matters. It will rescan, it will show history. Your wallet file is fine, it's what you're running it with that is messed up. Save your wallet somewhere safe, reinstall bitcoin, and import the original. No reason to move coins, the problem will just repeat itself.
After you overcome this issue, you will see all of your transactions. Making that a spreadsheet, though, is not directly possible through the client. Figure out the first part, then make a new thread about how to extract tx logs from the blockchain.