The article talks about reading and parsing CSV data of unknown variants, but then skips to the solution being using a different format altogether. But you can only switch to a different format if you are producing data, not if you are reading it!
And if you are in control of producing data, just produce strict RFC 4180-compliant CSV data and everybody will be able to read it just fine. There is no need to make your reader’s lives difficult by using yet another non-standard data format.
I just had a look at RFC 4180. This is the grammar they suggest:
> file = [header CRLF] record *(CRLF record) [CRLF]
I find it kind of wild that you have to have at least one record. Suppose I have a program that lists the events that occurred on a given day. How do I represent the fact that the program ran successfully but that there weren't any events on that day?
Event - Loging Started
Aditionally, Loging Ended (for the day) allows you to check for intermediate crashes and startup errors, kinda like a pulse / health check
Not necessarily how I'd do it intuitively, but doesn't seem that crazy
Sqlite isn't a standard per-se, but outputting an Sqlite db file, if you're writing the export code, is easy enough, and enough of a standard that I dare say you'd be doing fine to output .db files marked as sqpite.
> 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.
From a pragmatic viewpoint, the CSVs that I get from finance (usually saved as .xlsx) have the same issues for parsing the data as a CSV. But since the issues are consistent, I can automate conversion from .xlsx to CSV, then process the CSV using awk to eliminate errors in further parsing the CSV (for import, analysis, etc.). Sure, I'm essentially parsing the CSV twice but, because the parsing issues are consistent, I can automate to make the process efficient.
Obviously that wouldn't work for CSVs with different structures, but can be effective in the workplace in certain scenarios.
As long as a human didn't generate the file, all things can be automated.
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:.
Before 2010 and UTF-8 everywhere , I regularly had the misfortune of dealing with multi encoding CSVs. Someone got CSVs from multiple sources and catted them together. One source uses ISO 8859-1, another -15, another UTF-8, sometimes a greek or russian or even ebcdic was in there. Fun trying to guess where one stopped and the other begun . Of course, none of them were consistent crlf or escape wise.
This is some next level response where Excel is called a “broken tool”. You may not agree with their choices or design or anything else. But calling the entirety of the product isn’t making a strong case towards the prior point.
Importing data generated from Excel: don't, or force RFC 4180-compliant CSV data.
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.
>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
Do you have some recommendations or references about getting data properly out of Excel. I usually avoid using Excel alltogether if possible. But, obviously that is not always an option.
As someone who has excel open all-day every-day: I don't think excel-employees are "producing" data. We are ingesting CSVs/XLSXs, performing modeling/analysis, and then saving as an XLSX.
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.
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 have to use Excel, you have to produce whatever data Excel produces. It all depends on what (explicit or implicit) contracts you have in place with whoever is to consume the data which you produce.
And if you are in control of producing data, just produce strict RFC 4180-compliant CSV data and everybody will be able to read it just fine. There is no need to make your reader’s lives difficult by using yet another non-standard data format.
See also: <https://news.ycombinator.com/item?id=39679753>