WIP/ERR: raise consistent errors for non-existent files
- [x] closes #29125
- [x] closes #13872
- [ ] closes #17918  <-- needs tests and double-checking
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry

xref #29102 the json case is xfailed here.
