[flagged]
> Don't use broken tools.
Tell that to your accounting and finance department and let us know how the message is received.
> If you're forced to use Excel, then it's not really you in control, is it?
In which case the up-thread's advice to "just produce strict RFC 4180-compliant CSV data" is worthless. "Just."
We're stuck with whatever CSVs we get, so 'just' doing X is not an option.
> And *if you are in control of producing data*, just produce strict RFC 4180-compliant CSV data
The point of the comment was that you likely aren’t in control of producing the data, so the article’s recommendation of using an entirely different format is likely also invalid. I’m not sure what you are arguing against as you seem to actually agree with them.
Obviously that wouldn't work for CSVs with different structures, but can be effective in the workplace in certain scenarios.
However, if you ever have the misfortune of dealing with human generated files (particularly Excels) then you will suffer much pain and loss.
I once had to deal with a "CSV" which had not one, not two but 6(!) distinct date formats in the same file. Life as a data scientist kinda sucks sometimes :shrug:.
Exporting data into Excel: provide RFC 4180-compliant CSV data, or just generate minimal XLSX files.
Most Excel users generally don't export to CSV (or practice any data sanity conventions); they seem to believe XLSX is a perfectly fine data exchange format for automated use. (“Oh the import broke? I just added an empty row above the headers, because it looked sloppy.”) Those that do understand that automated data processing means you need to be stricter in what you do in the sheet you are exporting tend to understand how to export proper CSV's from Excel as well.
exactly right
I don't know anyone who is saving a CSV unless it is the final model output and another system (e.g. TM1) can only ingest the CSV.
If I accidentally save a spreadsheet as a CSV it is a bad day since I probably lost all my formatting, formulas, and additional tabs.
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.
Can you provide more information about this?
Like, I'd reinstall windows if this would actually work on messy excel/CSV data.
[1] https://www.mssqltips.com/sqlservertutorial/9248/import-and-...
So this solution is not going to work for Excel either.