13 ms·

Simple file downloads and CSV files are fantastic. I wish more folks would publish data in simple formats like this. Every time I have to fill a "shopping cart"

• stormfather 3y ago
  I've never stopped to question the absurdity of the gov data shopping cart thing. WHY!?!? Is there some justification for that?
  • icyfox 3y ago
    I can almost assure you it was an explicit requirement in an RFP that was copied from some master template. So not a good justification - but a justification in the eyes of the bureaucracy. The book Recoding America has a lot of anecdotes to this effect; most of these situations reduce to a Congressional mandate that got misinterpreted along the way. My favorite was for an update to Social Security. The department in charge of the implementation swore that Congress was forcing them to build a facebook for doctors (literally where doctors could friend other doctors and message them). Congress had no such intention; it was actually 3rd party lobbying that wanted the requirement so they could build their own solution outside of government. Really crazy stuff.
    • reverius42 3y ago
      > Congress had no such intention; it was actually 3rd party lobbying Right, but 3rd party lobbying can't force anyone to do anything, whereas Congress can (and did) give this mandate the force of law. The fact that lobbyists got Congress to do something that they had "no such intention" to do is its own problem, but let's not lose sight of who is responsible for laws.
      • icyfox 3y ago
        That's the interesting part of this story; Congress didn't think this requirement existed, neither did the lobbyists. But the language that congress adopted (with the consultation of this lobbying group) made the agencies _think_ it was what congress wanted. So the agency was left holding the bag for something no one actually wanted in the first place. Like a big game of telephone. I agree with your broader point however. Congress needs to do a better job of owning outcomes.
  • d1sxeyes 3y ago
    Probably because it’s the out-of-the-box functionality on ServiceNow or whatever tool they’re using.
  • phatskat 3y ago
    Bureaucracy = $$$
    • nerevarthelame 3y ago
      Who is making money with shopping cart-style downloads?
      • deleted 3y ago
        [deleted]
      • redavni 3y ago
        The web development companies that are subcontracted by the government agencies to repurpose their silly shopping cart software. I will decline to share my personal anecdote's about these companies because I am like 10+ years out of date, but I can tell you that most of these companies seemed to have certain very specific non-technical things in common.
  • jstarfish 3y ago
    Some records are only released for a fee, so I always assumed implementing a standard interface to handle commercial transactions and making select items free was easier than maintaining separate commercial/noncommercial sites.
    • dTal 3y ago
      Which in itself is fairly antidemocratic.
  • btown 3y ago
    Probably made by the same people who made https://pacer.uscourts.gov/pacer-pricing-how-fees-work https://pacer.uscourts.gov/pacer-pricing-how-fees-work ... (As a side note, I can understand why in years past it would cost multiple cents per page to physically photocopy a federal document - but it is absolutely absurd that already-digitized documents, documents which are fundamentally part of the precedent that decides whether our behavior does or doesn't cause civil or criminal liability, are behind a paywall for a digital download!)
    • dmvdoug 3y ago
      Pacer physically hurts to use. They should have to pay us for having to use it.
  • NelsonMinar 3y ago
    I assume it's a vestige from the old days when you ordered actual tapes or printouts from government agencies. The notion of an order that had to be prepared is baked into how they think about the service and product. All sorts of strange things happen with accessing US government data. But most agencies have a lot of excellent data available for free and motivated data scientists who want to make it available to you.
  • RicoElectrico 3y ago
    My guess would be to gather information about who is using the data - and present it to the stakeholders. Sometimes they send questionnaires to data consumers.
  • ics 3y ago
    I for one was tickled the first time I paid an NYC parking ticket online and had to add it to my cart, as if they might upsell me on some other more serious violation. Act now before it's too late!
    • dkarl 3y ago
      When you're paying for things, and can pay for several things at the same time, it makes sense. I helped my mother pay her property taxes this year, and for two properties we had to make four payments. Without the shopping cart (or a functional equivalent) I would have had to enter my payment information four times instead of once.
    • yard2010 3y ago
      And if you don't like the service you have free returns for 14 days no questions asked
  • turquoisevar 3y ago
    I’d go further and say that government websites and services in general are weird and often feel outdated, especially the more local you get. Some of them look like they’re straight from the early 2000s. Another thing is that privacy is dead and almost everything is deemed public information. Your address, mugshot, voter rolls, you name it, it’s all deemed public information. But once you actually want to access information that is useful to society as whole, more often than not it’s behind a paywall. Despite this, they still call it “public” information because theoretically anyone can pay for it and get access. It’s one of the first things I noticed when I moved to the US. Another thing that I’ve noticed is that, if possible, there always needs to be a middle man inserted, a private corporation that can make a profit out of it. You want to identify yourself to the government? Well you’re gonna need an ID.me account or complete a quiz provided to you by LexisNexis or one of the credit reporting agencies. Why? How is it that the government of all entities, isn’t capable of verifying me themselves? Zooming out even further you’ll start to recognize even more ancient processes you interact with in daily life. The whole banking situation and the backbone that’s running it is a great example. The concept of pending transactions, checks and expensive and slow transfers is baffling to me. It’s so weird, like ooh, aah this country is the pinnacle of technological innovation, yet in daily life there’s so much that hinges on ancient and suboptimal processes that you don’t see in, say, Western Europe. My best guess is that’s this is because of a mix of lack of funding and politicians that wanting to placate profit seeking corporations. Ironically, and I have no hard evidence for this because I’m too lazy to look into it, I suspect that on the long term it costs more to outsource it than it does to do it themselves. /rant
    • domh 3y ago
      I feel like the USA was probably ahead of the curve on things like the banking system in the 90s but removing all of that cruft and replacing with a v2 is not an easy thing to do. Especially when the population are generally skeptical of government and outsourcing to a private corporation is seen as the cheaper option. Short term it's potentially cheaper/quicker to outsource but long term maintenance contracts and lobbying to keep things as they are is much more expensive. In the USA you find these whole industries that exist due to the inadequacies of the old systems. E.g. venmo doesnt need to exist in Western Europe (and probably rest of world?) because person-to-person bank transactions are free and easy.
• digging 3y ago
  Can you explain what the shopping cart is? I mean... do they make you select one or more files, then navigate to another page to download your selected files?
  • RosanaAnaDana 3y ago
    Quite literally yes. There are often multiple click throughs. Every Department, agency, sub-agency, all the way down to federal dog catcher has decided the most important thing is to invent a new way of getting data from them.
    • imchillyb 3y ago
      Solutions vary as the budgets and talent assigned the projects do. Federal, State, local and hyper local solutions cannot be the same unless the financier is also the same.
  • crizzlenizzle 3y ago
    Sounds like Germany’s company register[1]. You can search for companies, select the documents you’d like to see (like shareholder lists), then you go through a checkout process and pay 0 EUR (used to be like a few euros years ago), and then you can finally download your file. Still a super tedious process, but at least for free nowadays. [1] https://www.unternehmensregister.de/ureg/ https://www.unternehmensregister.de/ureg/
  • pininja 3y ago
    Pretty much. USGS’s EarthExplorer requires a login just to download free files https://earthexplorer.usgs.gov/ https://earthexplorer.usgs.gov/ There are other ways to access the data on here, but they’re fragmented. It’s nicely organized here so it’s a bummer they make it hard to programmatically retrieve files once you find what you’re looking for.
• username_my1 3y ago
  it blows my mind that you can use sqlite with csv as input and then query it, it sounds so logical and useful yet I never came by it. we have lots of reporting in CSV, can't wait to start using it to run queries quickly
  • WorldMaker 3y ago
    You might find a lot of interesting tools in the Datasette ecosystem. Data dashboarding for SQLite with all sorts of import and export and visualization plugins. https://datasette.io/ https://datasette.io/
  • j0hnyl 3y ago
    Have you used duckdb? It's great for that.
  • shortrounddev2 3y ago
    You should checkout powershell; it supports converting CSV into in-memory structured data and then you can run regular powershell queries on that data: $> csvData = @" Name,Department,Salary John Doe,IT,60000 Jane Smith,Finance,75000 Alice Johnson,HR,65000 Bob Anderson,IT,71000 "@; $> csvData | ConvertFrom-Csv | Select Name, Salary | Sort Salary -Descending Name Salary ---- ------ Jane Smith 75000 Bob Anderson 71000 Alice Johnson 65000 John Doe 60000 You can also then convert the results back into CSV by piping into ConvertTo-Csv $> csvData | ConvertFrom-Csv | Select Name, Salary | Sort Salary -Descending | ConvertTo-Csv "Name","Salary" "Jane Smith","75000" "Bob Anderson","71000" "Alice Johnson","65000" "John Doe","60000"
    • llimllib 3y ago
      nushell does too: /tmp/> "Name,Department,Salary ::: John Doe,IT,60000 ::: Jane Smith,Finance,75000 ::: Alice Johnson,HR,65000 ::: Bob Anderson,IT,71000" | ::: from csv | ::: select Name Salary | ::: sort-by -r Salary ╭───┬───────────────┬────────╮ │ # │ Name │ Salary │ ├───┼───────────────┼────────┤ │ 0 │ Jane Smith │ 75000 │ │ 1 │ Bob Anderson │ 71000 │ │ 2 │ Alice Johnson │ 65000 │ │ 3 │ John Doe │ 60000 │ ╰───┴───────────────┴────────╯
• mhh__ 3y ago
  Someone posted me a pdf in the last year! Online even! (But posted!)
• rewmie 3y ago
  > Simple file downloads and CSV files are fantastic. I wish more folks would publish data in simple formats like this. The document format doesn't seem to have much to do with the problem. I mean, if the CSV is replaced by a zipped JSON doc then the benefits are the same. > Every time I have to fill a "shopping cart" for a US government data download I die a little. Now that seems to be the real problem: too many hurdles in the way of a simple download of a statically-served file.
  • dividedbyzero 3y ago
    > if the CSV is replaced by a zipped JSON doc then the benefits are the same. Being able to use things like jq and gron might make simple use cases extremely straightforward. I'm not aware of anything similarly nimble for CSV.
    • two_handfuls 3y ago
      For CSV? DuckDB, Datasette, awk, pawk, and tons of others.
      • rewmie 3y ago
        > For CSV? DuckDB, Datasette, awk, pawk, and tons of others. Those tools are either line- or table-oriented, and thus don't handle CSVs as a structured format. For instance, is there any easy way to use AWK to handle a doc where one line has N columns but some lines have N+1, with a random column entered in an undetermined point?
        • two_handfuls 3y ago
          The CSV Spec (1) specifies that “Each line should contain the same number of fields throughout the file”, so what you describe would be a noncompliant file. Nevertheless, I would expect both awk and pawk to be able to work with it just fine. Both pawk and the recent version of awk can parse the CSV format correctly (not just splitting on commas, but also handling the quoting rules for example). 1: https://www.rfc-editor.org/rfc/rfc4180 https://www.rfc-editor.org/rfc/rfc4180
• pbreit 3y ago
  Pretty much all data eventually ends up in 2 dimensions (rows & columns) so all these complicated data models are just mostly complicating things.
• msla 3y ago
  CSV would be great if there were one CSV, and if you absolutely guarantee that nobody has "improved" the CSV by editing it with a spreadsheet program (including opening it with a spreadsheet program) or their own deft little fingers in a text editor. For example: "Look, this contains \"quotes\"!",012345 Or: "Look, this contains ""quotes""!",012345 Or, for some degenerate examples: "Look, this contains "quotes"!",012345 Or: Look, this contains "quotes"!,012345 Or the spoor of a spreadsheet: "Look, this contains ""quotes""!",12345 Theoretically, JSON isn't immune to being hand-hacked into a semi-coherent mess. In practice, people don't seem to do that to JSON files, at least not that I've seen. Ditto number problems, in that in JSON, serial numbers and such tend to be strings instead of integers a "helpful" application can lop a few zeroes off of.
  • AshamedCaptain 3y ago
    > CSV would be great if there were one CSV, and if you absolutely guarantee that nobody has "improved" the CSV by editing it with a spreadsheet program (including opening it with a spreadsheet program) Practically no formats actually pass those rules. Even plain text is bound to be "improved" by text editors frequently (uniformation of line endings, removal of data not in a known encoding, UTF BOM, UTF normalization, etc.) Just don't do that.
    • taway1237 3y ago
      Just don't do what, edit your data? The problem is all JSON implementations agree what a properly encoded JSON file look like, and it's not true for CSV. Arbitrary CSV is not, in general, round-trippable.
      • gkbrk 3y ago
        > all JSON implementations agree what a properly encoded JSON file look like You clearly haven't had to deal with tools that cannot read and write a JSON file without messing up and truncating large numbers.
        • 3y ago
• x86x87 3y ago
  Lol. Tell me you never had to parse CSV files without telling me. CSV files can be a nightmare to work with depending where they come from and various liberties that were taken when generating the file or reading the file. Use a goddam battle tested library people and don't reinvent the wheel. /oldman rant over
  • nuc1e0n 3y ago
    It's not as bad as all that. There's some gochas sure but you can cover them all with about 200 lines of code. However, I would recommend using a tested library to do the parsing, sqlite for example, rather than rolling your own. Unless you have to of course.
  • wavemode 3y ago
    Yes, you eventually realize the hard way that "CSV" is actually a blanket of various similar formats, with different rules and conventions. The way one program outputs CSV's may be completely different from another.
  • thefurdrake 3y ago
    Hey, CSV is hard, guys. I've found template injection in a CSV upload before because they didn't anticipate a doublequote being syntactically relevant or something. It was my job to find these things and I still felt betrayed by a file format I didn't realize wasn't just comma separated values only.
    • x86x87 3y ago
      Someone can find all these issues on 200 lines of code :) see sibling comment
      • nuc1e0n 3y ago
        This isn't my code, but gives you an idea of the level of complexity involved. But don't reimplement what you don't need to. https://github.com/mafintosh/csv-parser/blob/master/index.js https://github.com/mafintosh/csv-parser/blob/master/index.js
• chankstein38 3y ago
  I don't know if this is the cart you're talking about but the USGS does this for LiDAR data and yeah I'm with you I die a little every time I use that site. I love that the data is available but why in the world do we have to cart everything? Just give me a one click download
• wefarrell 3y ago
  One nice thing about CSV files being zipped and served via the web is they can be streamed directly into the database incredibly fast without having to persist them anywhere (aside from the db). You can load the zip file as a stream, read the CSV line by line, transform it, and then load it to the db using COPY FROM stdin (assuming Postgres).
  • heavenlyblue 3y ago
    That doesn't sound like an amazingly safe idea
    • dambi0 3y ago
      What specific risks do you foresee with this approach?
      • diroussel 3y ago
        Seem totally fine to me. As long as you can rollback if the download is truncated or the crc checksum doesn’t match.
        • chii 3y ago
          > or the crc checksum doesn’t match. which wouldn't exist if the api is simply just a single CSV file? at least with a zip, the CRC exists (an incomplete zip file is detectable, an incomplete, but syntactically correct CSV file is not)
      • NL807 3y ago
        DROP DATABASE blah;
        • aidos 3y ago
          That’s not how COPY FROM works in postgres. You give it a csv and a table matching the structure and it hammers the data into the table faster than anything else can.
• jhwhite 3y ago
  clinicaltrials.gov let's you save a search term then download the results as a zip. But there's an xml file for each search result for the trial. One of the first things I played around with was using Python to get that file, unzip it, then iterate through the xml files grabbing the info I wanted and putting it into ElasticSearch to make it searchable then putting an angular front end on it. I used to have it published somewhere but I think I let it all die. :(
• thibaut_barrere 3y ago
  Agreed! People are surprised but CSV files (while I would not use them for everything) work great in low-tech environment, for instance, or when files must be produced by many different actors with different technical levels. They also work nicely as an interop between different stacks (Cobol <-> Ruby). One concrete example is the French standard to describe Electrical Vehicles Charge Points, which is made of 2 parts (static = position & overall description, dynamic = current state, occupancy etc). Both "files" are just CSV files: https://github.com/etalab/schema-irve https://github.com/etalab/schema-irve Both sub-formats are specified via TableSchema (https://frictionlessdata.io/schemas/table-schema.json https://frictionlessdata.io/schemas/table-schema.json). Files are produced directly by electrical charge points operators, which can have widely different sizes & technicality, so CSV works nicely in that case.
  • rjh29 3y ago
    I worked for a company that used TSV for huge datasets. It was extremely efficient, far more so than any database.
    • xp84 3y ago
      TSV is criminally underrated compared to CSV.
    • Chiba-City 3y ago
      [dead]
• jd3 3y ago
  > Every time I have to fill a "shopping cart" for a US government data download I die a little. I've worked in gov/civic tech for 5+ years and, as you're probably aware, there is now a highly lucrative business in centralizing and consequently selling easy access to this data to lobbyists/fortune 500s/nonprofits. USDS/18f are sort of addressing this, but haven't really made a dent in the market afaik since they're focusing on modernizing specific agencies rather than centralizing everything.
• FranOntanaya 3y ago
  The main thing for me is that the data isn't broken down in a way that ends you having to make a thousand requests and collate them. If the client is expected to be able to pull the whole set of data, provide the whole set. There's other ways to rate limit.