4 ms·
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
by markus_zhang 5y ago
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.
- rvba 5y agoMaximum 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.
- markus_zhang 5y agoOops 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.
- mkl 5y agoI 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-of-columns-and-rows https://www.askingbox.com/info/xls-and-xlsx-maximum-number-o... it has always been 256 for .xls and 16384 for .xlsx.
- Someone 5y agoPostreSQL 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 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.
- rrrrrrrrrrrryan 5y agoThe 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.
- kwyjobojoe 5y agoWe 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.
- markus_zhang 5y agoNow 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.
- kwyjobojoe 5y agoBut then we need something to interface with the db. Getting any programming language installed isn't going to happen in government.
- andrew_jef 5y agoNocodb is trying to solving the issues of implementing a spreadsheet-like frontend on top of a database backend https://www.nocodb.com/ https://www.nocodb.com/
- regularfry 5y agoBest 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.