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.

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.

For a somewhat trained audience however it can be quite interesting for some specific problem domain ...

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".

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.

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?

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.

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?

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.

SQLite is also an auto contained file. There are similar tabular GUI tools that could let you interact with sqlite using a similar workflow. Users knowing that thing is not inherent, they had to be trained on it, and they can be retrained as they will be for other workflows and business tools.

Remember, you still need an external app (Excel) to open it's files, the files themselves are just data, exactly like sqlite. So you could just make an excel plugin to interface with SQLite.

SQLite is as version-able as an excel file, as in not very with standard tools.

Why are you pasting images into excel? Doesn't matter, sqlite handles that fine actually. https://www.sqlite.org/fasterthanfs.html

The main argument against sqlite is that you would need to build an interface or figure out how to train folks on existing tools. That's not a huge argument against it in my experience, it's a strong social/political one in many orgs but rarely a technical issue.

The design of a solution needs to look into way more requirements than just the technical ones, time and money being 2 big ones. I think most of the HN readers would agree that you could end up building an interface with most of the Excel functionality, even more perhaps, on top of sqlite, and have your particular group of users trained on it.

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!

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.

What's that? I ddg'd it and I got a bunch of hits for regex \d ...

See https://jmmv.dev/2020/08/config-files-vs-directories.html

Early 1990s, college internship. The company did presentations for clients, like many do. They had an unusual way of presenting data that required using actual protractors to draw circles and curves, with pencil, on otherwise computer-generated charts. They read numbers from Excel spreadsheets and plotted them on paper.

I was shocked, to say the least. I proposed writing a program that read Excel spreadsheets and emitted the graphics. They loved the idea, especially from a summer intern.

So I wrote a letter to Microsoft asking for documentation of the Excel file format. A week later I got a thick envelope with a photocopied manual completely describing the format. I remember the word BIFF throughout. I wrote the program, it worked great, and I even negotiated a hefty lump-sum payment to sell it to them at the end of the summer.

It left me with a very positive impression of Microsoft as a developer-friendly company. Makes sense; developers are their platform's customers, and they're good at serving their customers.

Basically every engineer who joined the team thought it was a unforgivable blemish on the system, yet it survived a few years with no major issues, long enough for the team to build an internal backoffice and port the whole sheet structure into a proper CRUD API.

Cross-platform; copious spreadsheet applications; no-one needs training; scales.

(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.

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).

Excel is like Python: the second best tool for a lot of problems. There are problems which take five minutes to solve in the shell or in a text editor (maybe less now when LLMs can straight produce certain solutions with a simple prompt) and they take 10 seconds in a spreadsheet including copy and paste. I really recommend spending some time with Excel just as I recommend reading the table of contents of your primary DBMS’ manual.

I’d actually argue that being able to solve a problem quick and dirty in excel and in then in a more proper way in pandas is a good thing.

I am perennially lost in all but the very simplest spreadsheets.

At Google, many internal tools use Sheets as their source of truth for config data, and it works really well.

I agree completely but thats not the point he appears to be making 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.

https://support.microsoft.com/en-gb/office/stop-automaticall...

> "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?

I also love how the support article describes a behavior of their own software as “very frustrating”.