Definitely been there, though my favorite was when someone gave us a "ragged right" CSV. So if you had 10 total columns, but some rows only used 8 or so of them, they just stopped - no trailing commas to indicate empty fields, just a CRLF for the next line. SSIS _hates_ these. To make it more fun, Excel at the time would generate ragged-right CSV files if you saved as CSV. That gave us all sorts of fits because Excel's data type handling is less-than-stellar and we exported to a CSV to avoid some of the issues only to find that those had other issues.
For imports using PowerShell, the Import-CSV and Import-DbaCsv are nice functions. There's also the ImportExcel module, which does some really neat stuff w/ data and doesn't require a full install of Excel on the machine to do its magic. I've used that quite a bit to generate Excel files, read them, standardize worksheet names for ETL (because we couldn't depend on that sheet containing data to have a consistent name), and do some basic formatting.
I was trying to fix someone's import/export script for CSV files and we hit some crazy inconsistency with quotes. We had a script that was designed to pull in the file, fix the quotes, then save the data to a new file. The BULK INSERT command could then read that file. Crazy workaround, but it works and hard to justify touching a working process to try to make it better. 🙂