A very common and very real problem. At my last company we addressed this by writing a CSV library that really did handle all the Excel weirdness.

https://forge.ocamlcore.org/projects/csv/

Actually some of that is not automatable, so this cannot by its nature be a complete solution, but with heuristics -- ugh -- it worked. Months and months of effort to get it to work, and we didn't even have the general public as customers, just a small set of B2B companies.