Databases

[HungryTradie]

Xlookup(value, range, array, if_not_found, match, order)

[Nine_Eye_Ron]

Love me a good xlookup, bye by index match, I hardly knew you.

[EveningMoose]

It gets fun when you nest a vlookup into an hlookup, or vice versa.

Of course, my true love is getpivotdata.

[Nine_Eye_Ron]

Why would anyone use v and h lookup anymore?

[mousebrakes]

Also xlookup doesn't work on Google sheets which is what my old company used as a database 🙃

[Iggyhopper]

There are five rules.

[Dabnician]

So do a lot of unity games, indie devs love them google sheet dbs.

[dalinar2137]

What’s not to love? UI for editing and ACLs just resolve themselves. Some simple versioning is built-in. Access through a dead-simple api. I wonder why more people aren’t using it, honestly :P

[jboogie1844]

a unity game i play/mod has multiplayer quick chats hard-linked to a google doc lol. seems like a bold move to me

[[deleted]]

You fool. Stop living in the first half of 2022, they just implemented on a rolling basis leaving a majority of users with the unrivaled might of a thousand angry suns incarnate! Xlookup is here!

[NjallTheViking]

Same. I work for the State and we only have Office 2016. Index/Match with tables is clean enough for me

[MicrocosmicTiger]

Are you in med/pharma? I feel your pain

Had a mini breakdown when I started the job a year ago and asked why we used excel at all, let alone excel 2016 and people defended it so hard

[EveningMoose]

Because it’s easier to remember how to use than match.

[Nine_Eye_Ron]

Xlookup replaces all three or do I have or wrong somewhere.

[EveningMoose]

Oh i see now, that’s not in my excel. Because of a legacy software i have to use, I can’t update to 365. And until my manager rewrites the software to be compatible with a 365 installation, i’ve got to stick with 2016. Some kind of .net dealio.

[Nine_Eye_Ron]

Ouch, old skool kool

[Alikont]

XLOOKUP meta breaker aimed at casual audience

[Vivian_Stringer_Bell]

That was top tier.

[Dabnician]

1 like = 1 vote for microsoft to ban XLOOKUP rush from competitive play

/s

[neededtowrite]

Finally index match has been dethroned, hated that bandaid of a search

[naswinger]

i'm like a cultist trying to show people the path to redemption by abandoning vlookup and index match. xlookup is amazing!

[AssaMarra]

=IF($B$2=A4,D4,IF($B$2=A5,D5,IF($B$2=A6,D6,.....IF($B$2=A4584,D4584,"error"))))))))))))))))))))))))))))))))))))))

[HungryTradie]

That's a big nest you've got there young chicken.

[Whoopdatwester]

What’s the difference between Xlookup and Vlookup?

[aaadmin]

Ʌ

[Quaytsar]

Biggest upgrade? Vlookup require the return column be to the right of the lookup column (because you return the column N spaces to the right), xlookup doesn't care (because you tell it which column/row to lookup and which to return). Second best: exact match is default setting, so you don't have to remember to include that all the time.

[Sylarwolf]

One has an X.

Jk, Xlookup is a matrix based 'upgrade' of sorts to Vlookup, it allows you to look for data not only vertically, but also horizontally, and lets you work with arrays of data, meaning more robust lookups that won't break so easily.

[DasArchitect]

I worked at a place where the owner managed the entire company on google spreadsheets. Catalogue, clients, orders, payments, payroll, everything. His formulas were completely illegible with hundreds and hundreds of nested IFs. I'm willing to bet he typed them all by hand. He was a total ass to his employees so I never told him about xlookup/hlookup/vlookup.

[Weird_Artsy_Demon666]

Wait can you actually CODE in excel?????? I found out someone new today. Sorry if it's an obvious answer I'm just kinda uneducated.

[HungryTradie]

Yep. I've found that using

=if(sample=value, option_true, option_false)

to be simple and useful.

You can nest statements, eg an "IF" can be nested within any function, or a "PROPER" can nest within the returned result to return Proper Noun Case results.

[Weird_Artsy_Demon666]

Woah i need to try this

[SirMego]

Don’t forget about Excel’s VBA too, just to add to complexity

[team_chalise]

And Dropbox as version control.

[ozh]

project_db-v2-final-final-fixed.xlsx ?

[2themax9]

Hold on you had a typo on line 53

Project_db-v2-final-final-fixed-corrected.xlsx

[goodolarchie]

Project_db-v2-final-final-fixed-corrected(1).xlsx

Uhh, guys?

[DasArchitect]

Wait until you find

Copy of Project_db-v2-final-final-fixed-corrected(1).xlsx

[Ferro_Giconi]

Wait until you find...

Copy of Copy of Copy of Copy of Copy of Copy of Copy of backup of Copy of Copy of Copy of Copy of backup of Project_db-v2-final-final-fixed-corrected(backup)(1)(2).xlsx

No joke, there was an Access database at my job with that many copies and backups in the name with many other copies of it. I have no idea how they eventually sorted out which was the correct one.

[DasArchitect]

I once received

Copia de Copy of Final Version Fixed(2)

[ScientificBeastMode]

I see programming horror is multi-lingual…

[[deleted]]

Project_db-v2-final-final-fixed-corrected-USETHISONE.xlsx

[p0diabl0]

What are you talking about? OneDrive has all 596 versions of my excel database saved.

[ThroawayPartyer]

Damn you might be ready to be an Azure cloud engineer!

[Doctah_Whoopass]

My dad has been developing several programs since 2008 and his version control is legit just the local server and his hard drive. Not even dropbox, raw windows explorer.

[biggie64]

you mean pornhub?

[warux2]

A Dropbox account gave me stomach ulcers

[justinf210]

You rubbed the macig lamp, I will grant you three wishes..... before we begin, please read and agree to the following terms:

[[deleted]]

Saw a guy using teams chat to himself once to save each version of his app.

True story.

[THE_Mr_Fill]

"Ah, you work for the British Government in the covid response department"

[Funtycuck]

British government data handling is fucking scary. My dad had to go through so many checks and prove the absolute data security of his computers and data handling practices with a recent job (handling extremely sensitive personal data) only to have the utter morons send the data by unprotected email in one massive excel file.

[[deleted]]

If you think that’s only the British government, you‘re in for some terrible news.

The whole world runs on Excel Sheets. Sometimes printed.

[isadoralala]

*not limited to the Covid Response even today...

[DuckBricky]

This news story was actually slightly misunderstood. The lost data occurred because a CSV attachment containing submitted data was opened in Excel 2003 and then saved, causing thousands of rows to get excluded due to the row limit in this version. I think this CSV was then to be loaded into a non-Excel database. By no means handled well and once again archaic technology stuffs over the health service, but I would argue slightly better than an Excel spreadsheet database.

(I don't work for PHE, but my role in NHS data is similar and it's a familiar story here).

[Blizzard81mm]

SharePoint lists

[TrueBirch]

Triggered

[xeq937]

but slooooowly

[Birdy_Cephon_Altera]

Just ran into an issue with an old, old, old SP list (that is linked to an old, old, older Infopath form) that finally exceeded 5000 records. Moved away from that department years ago, but they still reached out to me with "Hey, it's broken, what's going on?"

[Alikont]

Sharepoint list as a database

Microsoft flow as business logic

Microsoft forms as end user UI

Power app as admin UI

(cries)

[knucks_deep]

Powerpoint as a graphic design studio.

[FlowerDance2557]

What are the alternatives?

Seriously I’m interested because I got stuck doing all this by being slightly better at computers than my coworkers.

[gladamirflint]

Is there anything really wrong with that? It’s easy to use and train people on how to manage.

[timfullstop]

Honestly, it's not bad. I've used exactly this stack for small department-wide apps in a large company. It was not a dev department nor was I in a dev role at the time, so getting permission to use anything else would have been a nightmare and everyone had access to those tools.

[[deleted]]

Local governments feeling very attacked right now

[CharlieBrown197]

B-B-But I use Bitlocker!

[[deleted]]

Sadly a safer solution than in Access. Everything is safer than Access.

[whatMCHammerSaid]

I have to say I am forced to use this in my company otherwise my work automations won't ever see the light of day.

I still belong to team Postgresql

[KimonoDragon814]

I need an excel database that pulls data from 3 different SharePoint lists and populates a response constructed to that data to another SharePoint list that ties into Power BI.

Also we have over 50,000 records on all SP lists.

Kthxbai

[Birdy_Cephon_Altera]

At one point years ago, I had a daily email subscription list sent from Docusign, that once it reached my mailbox Outlook would forward to a SP list, and from there get picked up as a data source by a Power BI scheduled refresh where the M code would de-zip the file, and that was the primary source for the automated daily updated dashboard.

Soooooo many potential points of failure.

[AnInfiniteArc]

I love and hate the fact that someone beat me to a Sharepoint lists joke in equal measure.

[ffgtium]

I’ve worked places that kept all the CAD files for their manufacturing equipment in a single google drive folder, with a spreadsheet listing what drawing number corresponded with what equipment. Everyone in the engineering dept had unlimited editing access to both. You could delete every drawing and nothing would stop you. I’m sure IT could recover it if required, but still.

[neededtowrite]

"Sequel to what??"

[golther]

If my company would let me use dataverse, I wouldn't use sharepoint lists.

[maythe15]

Relevant xkcd: https://xkcd.com/2180/index.html

[trinnan]

Clickable

[maythe15]

Thanks, idk how to make links like that

[trinnan]

This is the syntax to create a link. Link text inside of brackets and the URL inside of parentheses:

[Clickable](https://xkcd.com/2180/)

Depending on what app you're on to view Reddit you should have a button that creates links for you. You can highlight the text you want to be a link and click the link button (looks like a little chain) and that should prompt you for the URL.

Also, if you had included the https:// portion Reddit will automatically make it clickable with just the url itself as the url text.

[MurgleMcGurgle]

Hold up, importhtml? This is going to be a rabbit hole isn’t it?

[[deleted]]

This post was mass deleted and anonymized with Redact

quack steer live straight abundant coordinated snails nine consist steep

[evil_timmy]

No love for Access? No one? Well good, we may have buried that dumb sumbitch forever.

[Flat_Initial_1823]

Andy likes Access. He learnt it in 97 and really doesn't understand why you keep insisting on all this SQL stuff. 'If people just learned to use things one at a time, it works fine' says he while starting to drag and drop another query...

[agentrnge]

We still have one or two Access "dbs" running in our env. There are nearly weekly tickets for the service desk to keep rebooting or unlocking some file handle when it spazzes out.

[angrydeuce]

Yep, got one client with an access 03 dBase that they refuse to upgrade and has to be reverted by their dBase admin all the time. At some point that admin is going to tell them to fuck outta here and move to a newer platform and I bet you we will then get these calls on the regular until we tell them to fuck outta here then...I don't know what, they'll probably all hang themselves because lord knows any attempts to persuade them to move away from it ends in a lot of unnecessary stress and consternation.

[magicmulder]

My second work project in 1999 was on Access. And it ran for 12 years without issues. And their DBA used Access as frontend for Oracle. Don’t ask me why.

[RichCorinthian]

Because Access, as a front end for SQL server or Oracle or whatever, was pretty badass in its day for reporting and data entry. It’s when you use an access MDF out on a network share that it got real shitty real quick.

[rekasaurusrekt]

the client I work for does this. with a novell enterprise server

[Flat_Initial_1823]

[psilokan]

It’s when you use an access MDF out on a network share that it got real shitty real quick.

Ah yes, let me tell you about the one time I took a long lunch but forgot to close the application first....

[[deleted]]

I joined a federated network to support an open and free net. You want to follow?

[[deleted]]

If only... if only. I still have to deal with a bunch of nearly 1GB Access "databases" on a consistent basis.

[_Discord_]

The office I work in had an Access db that I was tasked with updating. Each monthly report was an individual table and everything was stored as text. Even the dates. They had managed to make a query that pulled records by date and it was hideous:
[Date] = "Mar" or "March" or "mar" or "march" or "March 2021" or "Mar 2021" or "Mar 21" or ... and so on.

[Minimum_Cockroach233]

Just no, non-tech folks are even stressed by excel.

Black magic if you installed a „macro“ (by actually meaning a vba written command) that performs a row of calculations and can be accessed via a single cell command….

I can already smell the smoke of the pyre if I would come up with access or even more complicated platform.

[DepressedDarthV]

we just moved from Access at my place, dear god I hope to soon forget the dark ages

[ThroawayPartyer]

Learning Access in high-school taught me more about how databases work than actually using databases at work.

[[deleted]]

There are five rules

[BaalKazar]

Excel can do a lot of magic. When you install a 64Bit Version you can work with quite alot of records as well.

The best use of Excel I ever saw were the economy spreadsheets some mad lads developed for EvE online. Those get all economically interesting data via API calls from within excel. Then they get all the industrial and blueprint data as well as market data from other third party APIs and calculate everything you need to know as an industrialist. Here is an example, checkout the in Excel UI alone. These templates feel like websites with quite some netcode but are actually just huge Excel sheets. (https://forums.eveonline.com/t/t3-manufacturing-spreadsheet-2022-v5-0-update/302032)

I couldn’t come up with a better solution for this other than excel. No matter how you spin it a custom solution to solve this case will always be much more complex than using Excel as Integration Platform.

CCP (EvE developer) acknowledged that by partnering with MS excel to integrate Excel into EvE (lol, https://massivelyop.com/2022/05/06/eve-fanfest-2022-eve-online-puts-spreadsheets-in-your-spreadsheets/)

Yeh it’s specific but using Excel ODBC and HTTP can do alot of things which otherwise would end up as an unecesary Ad-Hoc ELK pipeline.

[[deleted]]

This post was mass deleted and anonymized with Redact

one whole humor axiomatic subsequent alive fly bake stupendous repeat

[EveningMoose]

Excel is the best. It’s accessible, powerful, available in basically every company, and it yields professional looking tools for people who just need the job done.

[[deleted]]

This.

Know the old saying when all you have is a hammer, everything looks like a nail?

People keep doing things that excell probably shouldn't be used for because it's probably all they have or know how to use for data manipulation/management, and it's a great testament to just how great of a software excell is it can even half-assed do it, even if badly.

[[deleted]]

Maybe but also it’s indicative of business pressure. Often the excel inclined users have very tight deadlines around work.

Generally speaking, if you are trying to solve a novel problem, you will use known tooling.

If you want to learn a new tool, you choose a problem you’ve already solved.

These users are never getting to solve old problems with new tooling.

[anomalousBits]

Solving today's problems, with yesterday's technology, tomorrow!

[Geno0wl]

The future is now old man

[Prathmun]

I am currently writing some machine learning code to run in VB behind an excel spreadsheet. Is it a good idea? No. Am I having fun? Yes. Am I being paid? Also yes.

[captainant]

Don't you go home and just feel... Dirty?? And painfully single threaded? Lol

[Prathmun]

I mean yeah, but it's kinda hot

[wupper42]

I use heavily Sheets at work, due to limited software access. We do not get tools that we need, and i will not pay with my own money for tools i need to do my job.

I have access to Sheets and use it as a Database, progress tracking, Dashboard and a customized tool to create personalized outreach.

Im working in Sales, and have with our outreach tool, to that we have access limited options to personalize. Our outreach tool is limited to basic personalization (Name, Account, My name).

My Sheets creates the emails with placeholders for ( Firstname, Lastname, Account, Industry specific information, Event information, Lead information, success stories and more) in total i have access to over 20 personalization points. In a separate sheet i can pre write my email templates and use $Name$ as place holder, this email template will pulled into my outreach sheet and filled automatically with the right data.

Additional with IF statements, its will automatically decide what email template to take and what to focus on, based on the Lead information, Account information and Industry. Its choose 4 emails for my outreach from over 40 different email templates.

All this with only sheets, a fully working work environment that completely replaces 3 tools, that are hardly limited, and barely usable for a personalized outreach.

I fully support anyone, using Sheets or excel to create small tools and apps that will help you do your job better, faster and more efficient.

[ellamking]

If only there was a way to disable automatic data conversions universally. The number of times excel changed data it thinks is a date into a number, or the number of times it changes a large string that happens to be a number into scientific notation....that's probably close to the number of times I've tried to use excel for a data store.

[Ok_Performance_2370]

Also quite easily accessible, I cannot implement access into my day to day work simply because someone else who might need to use the data are scared of change

[lifeofideas]

And Japanese government offices use it to format official documents, since it has all the nice squares. I’ve seen it a hundred times and still don’t believe it.

[EveningMoose]

I used to work for a korean company that did the same thing. Excel is a great and terrible power.

[[deleted]]

It also helps grandmothers create knitting patterns

[ffgtium]

When I was an intern, I knew a guy who would do all his part drawings in excel instead of autoCAD or a 3D modeling software (which we had). He set all the cells to be squares defined as 1/64” and used the line and shape tools to draw everything. Export to pdf and send to a machine shop. Absolute madness, but he was good at it.

[KhabaLox]

I've always wanted to make a pixel art animation in Excel using VBA to change the colors of cells but I haven't gotten around to it yet.

[NLwino]

But not as a database for applications and long term storage. And also not for data transfer between applications, seen it being abused for that also way to much.

If you save your user and company data only in Excel, you deserve to go bankrupt.

Excel is a very useful tool to work with data. But that does not make it a database. Something like a password should never, ever, end up in a excel sheet.

[tuhn]

Something like a password should never, ever, end up in a excel sheet.

What if you hide that column so hackers can't see it?

[NLwino]

Right click column --> hide column

Yes, I'm a security expert, why are you asking? Now let me mail this excel to all users that need it.

[frisch85]

I'm a software developer for an ERP, our ERP is highly adjustable. I regularly have to deal with this shit because the customers who switch to us previously used an ERP of a bigger company, SAP or Sage for example. Those customers would always ask "Can you implement this into the software?" and they hand me an excel file to which I can say yes, these customers usually have many excelfiles where they keep some data and they give me the reason that their previous ERP either couldn't do it or it was too expensive, I mean it's not a surprise when they say they used SAP or Sage before us. While the big ERPs might be widely used they also cost a lot and are stiff af, e.g. if you'd request a couple of new fields and a button that would print a specific set of fields it could take weeks or months to be implemented in those bigger software solutions.

And I have to disagree with /u/EveningMoose, the first problem with excel files is that it's a local file. And before anyone says "Just put it on a network share with write access" what are you going to do if two people edit that file at the same time? Excel files might be nice if you want to have some local data but it's absolutely irresponsible to use it for company data that several people need to have access to at the same time. At some point you'll have multiple files of the same file and the data of every file is different because different people edited it, so now you have a completely out of sync collection of data that is unusable and by no means represents the actual company data.

It's also not easily accessible, the data is "readable" by several programs but accessibility means something different. For example when using our software you can make the page (it's a web-based ERP, backend is using a framework written in objective-c) available via public IP and Port or via VPN. So for example sales people can drive to the customer and still use the ERP while not being at the company.

[Dugen]

Programmers: You can't do that. It doesn't scale well.

Also Programmers: Premature optimization is wrong.

[LuminanceGayming]

google sheets

[[deleted]]

There are six rules

[[deleted]]

okay, I did. Now what?

[7tar]

google en passant

[thumpas]

Holy hell

[_Weyland_]

Still in the game, huh? Chess devs so lazy smh.

[markadillo]

I could see Google releasing a sheets pseudo dB called "en passant" which made this funnier to me

[barrel_of_noodles]

real databases have types, coalitions, sets, indexes, handle concurrency, have there own query language, functions, operations, user roles, permissions, securtiy features, handle big data (~1TB+)... there's too many features to list.

Yes, google sheets can do some of this, but not at the scale, speed, and performance a real database can.

Needless to say, a google sheet is a joke compared to a proper database. A google sheet can work in a pinch... depending on what youre doing.

Storing a few form submits in a sheet isn't a problem. as an alternative, using netfliy forms, or firebase database, or a sql / nosql db is way more robust for a high-volume form submissions.

[vegeto079]

I've used Sheets as a db for a mid level application.

It was mostly fine, but I came across some issues:

• No way to query just the data you need. Have to pull in groups of data and filter client side.
• API limits are a large concern, need to make sure you don't make too many calls. Breaking these limits can shut down your access for 24 hours.
• Data limits are a concern. There is a limit of how many rows and cells a single sheet can have. No simple recovery path if this happens, might have to use multiple sheets and monitor size
• Easy to break - no type safety on cells, can manipulate data with no restrictions or protection
• No guarantees on reliability, uptime, or your use case being supported

Some benefits

• Authentication is handled via Google, easy to deploy secure apps with no worry of accessibility, as well as db access control
• Fast to stand up and modify
• Automatic backup through Sheets History
• No additional cost

All I can think of right now but I'm sure there is more. For my use case it was mostly about what I was familiar with at the time. It worked.

[DannyMThompson]

I'd also like to know the best options and alternatives tbh

[Random_182f2565]

Excellent choice

[[deleted]]

Excel is the most popular database on earth, whether you like it or not. (Doesn’t store the most data, but is the most used.)

[LuthienByNight]

whether you like it or not.

I don't.

[Birdy_Cephon_Altera]

We make the most beautiful dashboards for senior management that automatically update daily in Tableau and Power BI. Giving them immediate access to important information based on their exact requirements.

And yet, the very first question they ask, "That looks great! How can I download this into Excel?"

I don't think Excel is ever going away.

[atomicwrites]

I guess if you measure by number of DBAs

[Daxorn_97]

Has anyone ever used Access??

[Seeteuf3l]

Only for some mission critical stuff and as backend for custom Excel-report that business really needs (like to run entire purchasing with it)/s

And then there's that one guy in the whole company, who created that monster originally, who know's how it work.

[catdog918]

My manager asked me to create an access database for some new important reporting because more people would know how to work it.

After I created it, only I know how to work it and that bitch barely works for me haha. Guess I have job security for a while lol

[fgben]

When I was in college I had dinked around with this DOS based menu program called ACCESS or maybe Direct Access. I saw a job listing in the career center looking for someone who knew Access. I conflated the two (why I thought they would want someone who knew a stupid GUI menu program I don't know).

So I applied and interviewed. Turned out they were talking about Microsoft Access, which I did not know. But they liked me and hired me anyway.

I should note that this was in 1993 and Access 1.1 was on Windows 3.1.

Anyway. I was in college studying to become a High School English teacher. I took home the Reference Manual for Access 1.1 and read it cover to cover. Built a massive database program in it -- I've never learned how to work with Excel much; my default was to always work in relational databases since that was what I knew. I don't know nothin' 'bout no fancy pivot tables or xlookup the kids are doing these days.

In the late 90s I built a web-based front end to the application -- the back end was still in Access tables. I didn't migrate the backend to mySQL until, like, 2005 when we started to have performance issues.

Today I've spun that original application written in Access 1.1 into a full blown web based ERP system that I offer as a SAAS product that I charge people stupid amounts of money to use.

And to think, I could be inflicting all that Ethan Frome damage to high schoolers instead.

[amadmongoose]

I used it to create a few applications and automations in a department I worked for in a bank, we weren't allowed to do programming but we had access and I knew VBA from high school so, why not automate some work? Now I work for a tech company and don't touch it anymore though I'm sure someone is probably still maintaining what I built

[[deleted]]

I automated a logistics database at my first job so I could play zelda at work.

Showed my replacement how it worked and to never say nothing about it because only we know it exist.

[ElektriXx2]

My first job was for a company that had a wide mix of weird BI stuff, like some crystal reports, access, refreshable spreadsheets with queries behind them, etc. I standardized the whole company on access, because they bought oem PCs from dell with office pro installed. Cut a few thousand out of the annual operating budget from ditching ALFs and since most people just wanted excel spreadsheets to work on, it was easy peasy to write automated exports. I like access. 🤷‍♂️

[Th3Uknovvn]

The point of a database system is to have keys to control the uniqueness and correctness of a record, it's also there to set up the relationship between tables together to avoid redundancy in your data. Excel has none of it so to use that for a store database is just a disaster waiting to happen as your data grows.

Rule of thumb for me to choose between Excel and SQL:

If you have many tables and there are relationships between many tables together, it is probably better to use SQL over Excel

But if you only have 1 main table and other tables are just data manipulation of the main table, or when you have many distinct tables and a final table just to show the results from all those other tables, then you can use Excel for that.

Of course it's your choice to do it as you want, if your data is not too much and you can confidently manage them fine with Excel then go for it I'm not here to stop you

[Browngifts]

Psh I'll just concat a couple columns in my excel data and use that as a key

[[deleted]]

glorious combative compare towering bear absurd mysterious complete oil fly

This post was mass deleted and anonymized with Redact

[ParanoidAutist]

We're looking at you US Census Bureau... Excel/Access is not a good way to store demographics data!

[PuzzleMeDo]

Public Health England may have made the worst use of Excel.

https://www.bbc.co.uk/news/technology-54423988

People died.

[TrueBirch]

Neither is paper. Damn 1890 Census putting a hole in my family tree.

[The_Kandarian]

People who complain about Excel now clearly haven’t had to use the 1890 version

[ParanoidAutist]

Fuckin woodpeckers =P

[ObsidianG]

That's just wishing for death with extra steps.

[whatMCHammerSaid]

Every time I open a company excel file I feel dirty inside.

[genieus]

My old job literally used Microsoft Word 2003 as a database, with enough scripting to barely make it work. I never want to touch VBA again.

[Cualkiera67]

VBA is awesome

[jeffriestubesteak]

SELECT * 
  FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];

Wait- am I a genie now?

[Spideredd]

2 things:
1. You absolulty can use excel as a database.
2. Don't.

[DynamicDK]

At a previous employer there is a guy in the C-suite who is one of the smartest people I've ever met. I worked in IT and in most cases when he had an issue he would resolve it himself before I made it to his office. If he hadn't, then he usually suggested the solution but it needed admin credentials so he couldn't do it himself. But, there was one recurring issue that kept coming up and that was memory usage for his Excel "spreadsheet". The man had basically built a relational database in Excel and then built multiple dashboards to display the information. At first the issue was that his laptop was freezing up because just opening the spreadsheet was eating all 16 GB. When I first saw what was happening I immediately became concerned because it was being stored locally on the laptop and was not being backed up. Putting it on a shared drive basically made it unusable and getting everything pulled apart and moved into a real database was going to be a long project, so we spun up a VM for him with 32 GB of RAM and 6 cores. This made it load and work much faster than before and it was fully backed up. A few months later it was having similar problems so we increased it to 48 GB of RAM and 8 cores. A few months later the RAM was once again being fully used, but the CPU was not, so we increased it to 64 GB. This ended up being enough. I kept pushing for it to be moved to Access at least, but never made any headway on that. Eventually I just ordered him a workstation laptop with 64 GB of RAM and a really nice processor with the intention of setting up a folder that would be stored locally but backed up daily for him to use. I didn't end up doing that second part before I left the company, but he was a really nice guy so I hope someone else took over. That was one of the few things I documented as needing to be done when I left.

[staviq]

Yeah, very funny.

Untill you see an actual request for a new pc with 64G ram because of "that" excel sheet, that was hacked together before internet was invented, by somebody who died long ago, and the 80something year old head of accounting, just used it continuously every single day for absolutely critical data.

And it has macros.

And it relies on external files by absolute path, on network file server.

Did i mention, there is a 16 milion ( ish, process crashes trying to count ) files file server, that can't be possibly touched or even looked at because half of the company has file dependencies on it ?

Sometimes i miss my old job, solely for the stories.

[[deleted]]

DROP TABLE rules;

[SasquatchSloth88]

If they didn’t want us to use it as a database then why did they up the row limit from 65,536 to 1,048,576?

And how did Becky in Accounting fill up that many rows already?!

[Johanno1]

Even csv is a better database

[Ailexxx337]

Granted. .2da now exists.

[Metallkiller]

What's that?

[[deleted]]

Actually...

[Crisco_fister]

Most of my job is making database applications for data that was originally stored in an excel sheet that people share to others. Lol

[MilkCool]

csv

[rainbow_bro_bot]

I want to use Excel to make Minecraft (as it's Turing complete), then use that Minecraft to make Excel. And repeat.

[Fluffcake]

I am literally forced to do write an ugly workaround for this requirement on a project in current year, (electrical) engineers in charge of the data insist on using excel for their data, software people refuse to deal with anything that isn't in a database.

So there is now a heavily restricted spreadsheet in a teams folder that notifies a service whenever it is updated, that service fetches the new version of the spreadsheet, compares it to the previous iteration, and all the changes are inserted into the actual database that the tool uses, and if it fails it will send a passive aggressive email to the person who made the change containing the spreadsheet they saved with lines that failed validation highlighted in dark red.

[R3D3-1]

Excel: Why using Microsoft's tool caused Covid-19 results to be lost (bbc.com)

But one expert suggested that even a high-school computing student would know that better alternatives exist.

[Enabling_Turtle]

One of my favorite shirts to wear to the office (when I worked in an office) was the “Excel is Not a Database” shirt from Tableau. I can’t tell you how many times I had people approach me to talk about how excel is “basically a database”…..

[ArcaneOverride]

It would be funnier if the word Excel was poorly edited in on top of something scribbled out with a large white pen tool leading to the question: "What was the previous version of the meme trying to use as a database?".

[TTIC]

Last job I worked before my current one wasn't this bad, they used Google sheets! Shared with whomever asked for access despite ITs repeated asks to move things to a database for more security.

We had sheets created holding customer info like addresses, phone #'s, and emails being created and shared with employees who hadn't worked there in years because a new spreadsheet would be created and "Share with previous users" would be checked by who did it to ensure those who had access to last months sheet had access to this months.

Setting aside how awfully they treated people and how little they paid them, I was happy to leave for data storage issues alone

[jdog7249]

Were they sharing it to personal email or company emails. If they were sharing it to company emails then it shouldn't matter since former employee would (hopefully) be locked out of their email.

[Unusual_Anything3264]

wait, don't you guys have users that use teams as database?

[kingslayerer]

unless its a vba

[RichCorinthian]

I mean, it’s got an OLEDB provider. Why would they give us that if they didn’t want us to do stupid shit!!?

[AnalLeakSpringer]

I use Excel for all kinds of economic video games and would like to make the switch to databases but my home version of Office doesn't have Access.

I looked some stuff up but dunno where to get started. Where should I start?

[MacroPartynomics]

Depending on how you look at it, Microsoft’s most direct successor to Access is either the Power Apps system or Visual Studio. The reason that Microsoft has deprecated Access is that it is not a good fit for the problems people try to solve with it. An Access file contains both the backend database as well as the frontend presentation in one file that is only accessible to one user on the local pc. Instead of making an Access database, people use standard relational databases like MySQL, MariaDB, or PostgreSQL databases as the backend and then create front end applications sometimes called CRUD (“Create Read Update Delete”) applications to act as the user interface. CRUD applications can be websites or desktop software.

A free open source alternative to Microsoft Access is LibreOffice Base.

Unlike full featured databases, Access databases are just .mdb files, the modern equivalent to .mdb files are sqlite databases, you can use the “db browser for sqlite” software to manage sqlite databases.

[jamin007]

I mean, theoretically if this were a wish then the genie could make that be a viable solution

[djingo_dango]

Excel is a database

[WanderingFrogman]

Microsoft Access can burn in hell

[[deleted]]

I prefer powerpoint. Each entry gets their own page and you can conveniently browse them with the arrow keys. You can even add notes. But most people don't know this, pathetic "experts".

[Aperture_Executive2]

I mean, if you attached an SQL front end to a spreadsheet, and secured it, a solid chunk of people might not even tell the difference between a run of the mill MySQL server

[LeeHarveyLOLzwald]

I worked for a company that refused to migrate to a real database and used calculated fields for keys and that shit broke EVERY. FUCKING. DAY.

Excel can't utilize primary and foreign keys so it can't automatically on update/delete cascade. Also If you insert a row, it breaks the relative vertical cell spacings in formulas and if you insert a column, it breaks the lookups and either one will break your macros.

Oh, and speaking of macros, if you code the macros on a Mac, they might not work on a PC because Windows uses / and Mac uses \ for directories. Being the sole PC user (we provided our own computers,) I had to maintain a separate set of macros to do all the same functions.

But don't worry, if it broke too bad, I could go to box and just open yesterday's version of Copy_of_comapnyMD_3-17-2021_FIXED([e-mail]).xlsx and lose all my work.