8 ms·
One of the more interesting config formats I've come across was an application that used an Excel file. Once you get over the horror of such a terrible decision
by dagw 3y ago
One of the more interesting config formats I've come across was an application that used an Excel file. Once you get over the horror of such a terrible decision, it was actually a quite interesting choice that allowed a fair few advantages. First of all each config subcategory was on a separate sheet making easy to navigate and find what you where looking for. You could use formulas to relate different config options (If you wanted A to be 20% of B you just set that in a formula). You could use drop-down for fields where there were only a limited number of valid values. You could include as much comments and documentation as you wanted (including diagrams and images) as long you only wrote in unused cells. And finally, my favourite, when configuring colours, instead of typing in RGB or hex values you simply changed the colour of the cell to the colour you wanted.
Now I would obviously never ever recommend doing this, but it was certainly an interesting and eyeopening experience.
- btreecat 3y agoThat's just begging to be an actual database. Imagine if you said everything with SQLite instead of Excel, and all of a sudden your just talking about structured config in a database. Not new, not crazy, and generally a decent practice.
- michaelbuckbee 3y agoNow I just want a spreadsheet style front end to SQLite and I'm nerd sniped into trying to figure out how to do formulas (triggers I suppose).
- speed_spread 3y agoCongratulations, you're about to reinvent MS Access / DBase / FoxPro... 30 years later.
- michaelbuckbee 3y agoThat's an excellent point.
- speed_spread 3y agoI'm sorry for the initial sarcasm, it would actually be pretty cool to have those type of apps back. With everything going web-first nowadays, you could take inspiration from SQLite's own SCM, Fossil, that can run both as a CLI or a Web server.
- eternityforest 3y agoI've been thinking it would be cool to build something like this. What gets me stuck is version controllability and backups. I think ideally you'd want multiple backends, either SQLite, or flat files that are Git/SyncThing friendly. I was even thinking the file format could have a file UUID and record timestamps, so that if you put a different version of the same file in the same folder(Like with a SyncThing conflict file) it would give you a merged view, with newest-record-wins logic. Formulas I think would be the easy part. Just write it all in Python and use one of the many Excel compatible formulas implementations, and just make sure all changes went through the app. Maybe you could even have a REST API to build other things on top of it. The web frontend wouldn't be too hard, it could just be a Vue3 app with an HTML table element. Then you could have a cell type who's value was a query, which would embed a DB browser list widget in the cell, and that cell type could have the option to bind its selected row to another cell. Like VB+Excel+Access+My fork of Freeboard with inputs in one!
- masklinn 3y agoFor the most simplistic row-wise formulas, sqlite has generated columns. I’d assume the issue will be that an sql table is not free form, you can’t randomly decide to write in some other cell.
- dagw 3y agoThe big difference is that every Windows computer (in a professional environment) comes with a very nice GUI tool for easily editing Excel files. A tool that basically everybody know how to use. The same cannot be said for SQLite. SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit.
- johannes1234321 3y agoWhile I'm not sure I'd be happy about "end users" to edit the Excel files as config. Somehow they get the cells mixed up and you get utter mess. And then excel confuses some value for a date and stores some other mess ... For a somewhat trained audience however it can be quite interesting for some specific problem domain ...
- eternityforest 3y agoWhy doesn't Microsoft just add SQLite to Excel, so your formulas can query it?
- btreecat 3y agoBecause how else would they push MS Access?
- btreecat 3y ago>SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit. I think you are conflating the file with the workflow. A proper UI is the solution to making something not "terrible for end users to edit".
- harperlee 3y agoWell, that's a complete apples-to-oranges proposal with completely different requirements. With the original solution, you have an autocontained file that virtually any user knows how to edit, structure, expand, version, email, compare, discuss... The level of user knowledge that you need for a similar solution based on a standalone SQLite file that you can version is another different world, e.g. to relate two values you would need to perhaps create a view or a trigger. And even with the most knowledgeable user you would still lack functionality such as simply pasting an image as a means of documentation and be able to see it, or WYSIWYG colors.
- sofixa 3y ago> version, compare Gonna have to disagree with you here. Very few people know how to version, compare, let alone if there are multiple collaborators, Excel files. Is there even a decent way of doing that outside of Excel Online / Google Sheets?
- harperlee 3y agoWell I've seen a great deal of people comparing two Excels by quickly alt-tabbing, and doing Sheet1!A1=Sheet2!A1 comparisons in a third sheet, and dragging the formula. Yes, that's horrible. But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Same with version control: you just add _v17_20230909_final to the name. Yes, it's horrible. Yes, it's buggy. But yes, it also runs the world.
- btreecat 3y ago>But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Are you trying to suggest that either you can't do that with sqlite, or that people didn't require training to do this in Excel?
- harperlee 3y agoharperlee: open list of affordances that an average user comfortably identifies with an Excel file, not really with sqlite sofixa: version and comparison is not really supported in Excel harperlee: agree that software support is not there, but in practice average user knows how to do it and is quite comfortable doing it in Excel btreecat: sqlite also has filenames and you can learn how to use it harperlee: agree on the filename, Excel files and sqlite files are externally, opaquely versionable in the same way. the point about people learning is moot though, average user already knows Excel because they were forced to in the past, but does not have the time to learn new things.
- smitty1e 3y ago.xlsx and openpyxl For The Win! Cross-platform; copious spreadsheet applications; no-one needs training; scales.
- zelphirkalt 3y agoI see it more like: (1) A huge dependency in the project for reading Excel files. (2) Everyone needs training, as usually people in my profession have rarely if ever any need for Excel. (3) Visuals != contained text, so the config might be different from what you see on screen as the config value. (4) No proper version control, even a csv file would be better. (5) scales? lul, have fun trying to solve merge conflicts. Also don't come with any Excel git plugins. It will only make the bad decision worse.
- baq 3y agoxlsx is zipped xml. it might actually work. your profession must be very weird, though - knowing about git merge conflicts and not knowing how to use excel sounds like an empty venn diagram.
- zelphirkalt 3y agoI wrote "usually people in my profession have rarely if ever any need for Excel". But I will explain. Many software developers usually have no need for something like Excel. Either they use some free/libre alternative, because they know about it existing, or they use an actual programming language, or some might even use something like Emacs org-mode spreadsheets, or they use some library like Pandas for things, where it is reasonable to get out the tools. Software developers are also more likely to be aware of the technical debt incurred by storing anything inside Excel formats and will avoid it, if they are wise. As such many software developers rarely use Excel, if at all. I personally don't use it at all. All my simple spreadsheet needs are covered by Libreoffice Calc or Emacs org-mode. If I had to use Excel now, I would not know the names of functions (translated perhaps, because Excel does that silly stuff) or how to reference cells (Is it $ and then the number? And : as a separator between col and row?). So yeah, to properly use it, many of us would have to learn at least a little of it. Many if not most cases of Excel usage are actually due to people not knowing the alternatives, or perhaps knowing they exist, but not having the knowledge to use them (like with programming and quickly dishing out a few Pandas calls or Emacs org mode spreadsheets).
- lucumo 3y agoWere there any downsides? Because this sounds kind of awesome.
- dagw 3y agoOnce you got used to it, honestly not really, other than needing Excel (not a huge deal since it was a Windows only application). I've no idea what the config parsing code looked like, but with the right library I doubt it was worse than any other non-trivial config parsing code. Mainly it just felt very wrong to my Unix, everything must be a text file, brain.
- throwaway290 3y agoWell, xlsx is just a zipped dir of text (XML) files, so you can think of it as sort of following the .d pattern...
- kagevf 3y ago> the .d pattern What's that? I ddg'd it and I got a bunch of hits for regex \d ...
- throwaway290 3y agoconf.d, init.d
- kagevf 3y agoAh, got it, thanks!
- urinotherapist 3y agoAKA "config directory". See https://jmmv.dev/2020/08/config-files-vs-directories.html https://jmmv.dev/2020/08/config-files-vs-directories.html
- 3y ago
- fluidcruft 3y agoAt my co-op decades ago we used to use Excel "templates" as masters to generate/maintain text config files (and iirc a few C headers). You would save as text to make it usable. The grid layout, ability to highlight/color/border/style etc and use formulas and plot was very helpful.
- ics 3y agoUnderrated technique. I’ve used it similarly to generate scripts and it works great when used with care (error checking generally must happen at a different stage).
- kagevf 3y agoDid they version the spreadsheets? Or maybe converted them to text and versioned the text format?
- speed_spread 3y agoExcel sheets are an underappreciated tool for sharing technical info with analysts. They're easy to write and read from both dev and analyst side. They can be safely zipped, archived, emailed, saved on a USB drive by the user without any additional programming. Editing in Excel makes the expected schema clear. It's an excellent data interface.
- nonethewiser 3y agoMost of these sound like things a native programming language can do. Without being a damn excel file. So it’s basically an argument for making your config files JavaScript, Python, etc.
- mnstngr 3y agoHaving a visual way to configure this makes it much more accessible to non-programmers, with error checking available through the host (Excel, in this case), while also reducing the eng effort in building this. At Google, many internal tools use Sheets as their source of truth for config data, and it works really well.
- nonethewiser 3y ago> Having a visual way to configure this makes it much more accessible to non-programmers I agree completely but thats not the point he appears to be making. He never stated this was the use case and he reiterated that it was a bad idea which should never be done. Regardless, I’m saying those arent really unique advantages to excel. They just look unique compared to json, toml, yaml, etc.
- hot_gril 3y agoI can't think of any data entry method more easily understood by non-programmers than an Excel spreadsheet (or Google Sheets etc). Repetitive data in particular is a breeze since you can drag-expand and use formulas. We use GSheets at work for configs created by non-programmers, and it works great.
- nonethewiser 3y agoWhat is your point? You can see from my previous comment I already agree excel is user friendly for non programmers.
- hot_gril 3y ago
- macNchz 3y agoToo many encounters with Excel’s “smart” date parsing would make me very concerned about using it this way. https://support.microsoft.com/en-gb/office/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8 https://support.microsoft.com/en-gb/office/stop-automaticall...
- Rygian 3y agoThat page is such a gem. > "make it easier to enter dates. For example, 12/2 changes to 2-Dec" 12/2 is obviously the 12th of February in my locale. But I need to keep Excel in English as a company policy, so this is not only unhelpful, it's outright wrong. > Unfortunately there is no way to turn this off. How does Microsoft justify this choice?
- macNchz 3y agoI’ve never understood why they don’t provide the option to disable it, it’s not like Excel is a sleek, minimalist piece of software with strong opinions and limited configuration. I also love how the support article describes a behavior of their own software as “very frustrating”.
- WeAddValue 3y agoI use a Google Sheet as it can be accessed from anywhere. Even my non-techie business folks can make changes (they feel at home in spreadsheets). I even added a menu button to the gsheet to launch a re-build (it is a static site on Netlify; the build fetches it's config/data from the gsheet).
- deleted 3y ago[deleted]
- hot_gril 3y agoIt doesn't sound terrible. This is what we do at work for large configs that non-programmers touch, except it's Google Sheets instead of Excel. It works, and they'll often refuse to use anything else.