About a month ago, somebody posted a link to an interview with Brian Kernighan. About 6 minutes in, he talks about the difficulty of writing a decent CSV parser: https://www.youtube.com/watch?v=_QQ7k5sn2-o
CSVs Are Kinda Bad. DSVs Are Kinda Good
71–80 of 133 posts
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#72This keeps coming up as new people discover what CSVs are. An ancient TEXT data exchange format. The lowest vaguely common denominator. A style of format with flavors software long out of support contract are happy to export data in. The intent of the format is to be human readable and editable. Sure, Tab characters can be used instead of commas. (TSV files) Yes that's that "" to escape a quote rule. Oh and quoted va…
Right, the author skipped right over human-readable TSV files which play nicely with sed/awk/grep/sort pipelines, and are supported by all CSV parsers and spreadsheet software.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#73This keeps coming up as new people discover what CSVs are. An ancient TEXT data exchange format. The lowest vaguely common denominator. A style of format with flavors software long out of support contract are happy to export data in. The intent of the format is to be human readable and editable. Sure, Tab characters can be used instead of commas. (TSV files) Yes that's that "" to escape a quote rule. Oh and quoted va…
Right, the author skipped right over human-readable TSV files which play nicely with sed/awk/grep/sort pipelines, and are supported by all CSV parsers and spreadsheet software.
TSV is also my go-to when mucking around on the command line. Perfect for noodling with data before you have to put together an Excel file to show to management.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#74Question: I started with a deliberately convoluted PDF which after much effort I filtered, sorted, reorganized and transferred the 18000 useful lines to a csv. These lines are simple, with dates, indicator and corresponding numbers. The purpose is to statically analyze the numbers for anomalies or any signs of deviation from expected randomness. I do this all in python3 with various libraries. It seems to be working,…
18k lines is very small, CSVs are fine as storage option.
My rule of thumb is that anything that fits into Excel (approx 1M lines) is "small data" and can be analysed with Pandas in memory.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#75The 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…
[flagged]
Looking it up, using a custom delimited format in Excel is near impossible https://superuser.com/questions/733462/can-ms-excel-use-non-...
So this solution is not going to work for Excel either.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#76The 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…
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?
Easy, count running the report as an event.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#77This keeps coming up as new people discover what CSVs are. An ancient TEXT data exchange format. The lowest vaguely common denominator. A style of format with flavors software long out of support contract are happy to export data in. The intent of the format is to be human readable and editable. Sure, Tab characters can be used instead of commas. (TSV files) Yes that's that "" to escape a quote rule. Oh and quoted va…
I agree completely. Its simplicity is what gives it staying power.
When I was an undergrad, I had kind of an anal software engineering 101 professor who was treating the course like he was a scrum master. The deliverable was to make some dumb crud app, and a requirement was it used a "database." It was so stupid simple to write a csv to s3 or local disk that I just used that for the entire project. He tried to fail me for not following the requirements, and I had to go to the dean of CS and argue that by definition, a structured data format on a disk is absolutely a database, and I won. I got graded horribly after that though.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#78I like the idea but this is non-standard enough to be just as hard as making a custom format.
In my experience, the best way to handle this is:
1) Use TSV (tab-separated) instead of CSV (most things that export CSV also export TSV). Strip LF characters while reading and assume newlines are CR.
2) If you have a stubborn data source that insists on CSV, convert it to TSV in a pre-process. This could be a separate step or part of your reader as you're reading in the file. That means there's a single place to handle the escaping nonsense, and you can tailor that to each data source.
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#79Question: I started with a deliberately convoluted PDF which after much effort I filtered, sorted, reorganized and transferred the 18000 useful lines to a csv. These lines are simple, with dates, indicator and corresponding numbers. The purpose is to statically analyze the numbers for anomalies or any signs of deviation from expected randomness. I do this all in python3 with various libraries. It seems to be working,…
18k lines is very small, CSVs are fine as storage option. My rule of thumb is that anything that fits into Excel (approx 1M lines) is "small data" and can be analysed with Pandas in memory.
Hey, thanks for taking the time to reply. I won't be reaching 1M anytime soon, so good to know!
Re: CSVs Are Kinda Bad. DSVs Are Kinda Good
#80Earlier quoted context omitted.
>> […] Excel […] > 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.
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,…
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:.