News for hackers. Stuff that matters.
Re:The Tyranny of Spreadsheets by markus_zhang on Thursday July 22, 2021 @05:45PM (#27924736)
In my last company we keep a spreadsheet that has tabs occupying almost the maximun number of columns allowed and in different headers. Updating that Excel file needs the full team to work for two full days each month. That was after I created a VBA script to scrape data from internal dashboards and put them directly in the spreadsheet.

From my experience these monstrosities are usually created by two reasons: Push from business side and a data team too slow to react. Thus the analysts have to create the first version that seems to be OK and smart but eventually evolved into a monstrosity.

Re:The Tyranny of Spreadsheets by kwyjobojoe on Thursday July 22, 2021 @08:01PM (#27925812)
We use spreadsheets to manage data that would 1000% be better suited for storage in a database (and extracted the data would be sooooo much simpler than my monthly half day job of updating management reports).

Why? Because we don't have access to any databases. I dread to think of the requests and committees I would need to find and traverse to get a database set up for my team, let alone a system that I can write and run code on. Yet we can guarantee that every internal and external user has access to Excel.

Unless you are working for a software firm, or are part of a dedicated analytics team with access to suitable systems, large companies and government pretty much have no chance of being able to roll your own database.

Don't suggest Access. Never suggest Access.

Re:The Tyranny of Spreadsheets by markus_zhang on Thursday July 22, 2021 @11:49PM (#27926993)
Now that you mentioned, we didn't have access to SQL Server dwh until 6 months before I left for good.

Can you try sqlite? It's usually enough for simple things I think.

Re:The Tyranny of Spreadsheets by kwyjobojoe on Friday July 23, 2021 @03:54AM (#27928299)
But then we need something to interface with the db. Getting any programming language installed isn't going to happen in government.
Re:The Tyranny of Spreadsheets by regularfry on Friday July 23, 2021 @08:14AM (#27929819)
Best of both worlds: libsqlite.dll is almost certainly on any corporate Windows rollout somewhere already, and you can load it into Excel as an extension for a pretty front-end that happens to have a programming language built in.
Re:The Tyranny of Spreadsheets by andrew_jef on Friday July 23, 2021 @05:08AM (#27928698)
Nocodb is trying to solving the issues of implementing a spreadsheet-like frontend on top of a database backend https://www.nocodb.com/
Re:The Tyranny of Spreadsheets by rvba on Thursday July 22, 2021 @06:34PM (#27925184)
Maximum number of columns is 16 384 (in modern Excel). PostreSQL has a limit of 16 000.

So Excel > PostrGRE? :)

I wonder what kind of data did you use and why it had to be stored in so many columns. This approach would probably kill a "real" database too.

Re:The Tyranny of Spreadsheets by rrrrrrrrrrrryan on Friday July 23, 2021 @03:20PM (#27934560)
The reason why you often encounter spreadsheets with so many columns is because spreadsheets really, really want data to be flat. Analysts often like to have all the possible datasets pre-joined into one monstrous sheet, then they can easily slice and dice it up however they want.

With a relational database this would be kind of insane, as it's much easier to normalize data into separate tables, then just join up them up when you need to.

Re:The Tyranny of Spreadsheets by Someone on Thursday July 22, 2021 @07:50PM (#27925744)
PostreSQL has way more data types than Excel. You can have arrays, json, xml, ranges, polygons, etc. in columns (https://www.postgresql.org/docs/current/datatype.html)

Because of that, chances are the natural format for a data item taking 16,384 columns in excel likely takes fewer than 16,000 columns in PostreSQL.

Re:The Tyranny of Spreadsheets by markus_zhang on Thursday July 22, 2021 @07:05PM (#27925398)
Oops I definitely got it wrong. Probably a bit more than a thousand columns now that I think about it. Still a beast. The thing is a database with every column a product id.
Re:The Tyranny of Spreadsheets by mkl on Friday July 23, 2021 @07:53AM (#27929659)
I was thinking maybe that meant an older version had a limit of 1024, but according to https://www.askingbox.com/info/xls-and-xlsx-maximum-number-o... it has always been 256 for .xls and 16384 for .xlsx.