If you’re deep in the Excel world, chances are extremely high that you also have access to SSMS, which has a really, really good data import tool that makes short work of nasty CSV files. The output of this tool doesn’t even have to be SQL Server, it will use any ODBC driver you’ve got installed; you can send the data to Excel or even a new, properly formatted CSV.
And if you want a repeatable package, there is always SSIS.
Look, I would never recommend anyone jump into the Microsoft ecosystem. But when in Rome, do as the Romans do.
There is a screenshot here [1]. The trick is to choose the generic "import data" task rather than "import flat file". Then you have a wizard that lets you play around with the specifications, change things up, look at the errors, go back to tweak things, go forwards to try again, etc. The only improvement I can think of is if you could save what you did as a standalone SSIS package (or whatever).
If you’re deep in the Excel world, chances are extremely high that you also have access to SSMS, which has a really, really good data import tool that makes short work of nasty CSV files. The output of this tool doesn’t even have to be SQL Server, it will use any ODBC driver you’ve got installed; you can send the data to Excel or even a new, properly formatted CSV.
And if you want a repeatable package, there is always SSIS.
Look, I would never recommend anyone jump into the Microsoft ecosystem. But when in Rome, do as the Romans do.