Ask HN: Graduating from Excel, What's Next?

3 points by VladTheImpalor 2 years ago | 6 comments

I'm a mechanical engineer maintaining component 'part lists' in a number of Excel files. These lists contain the specifications of every component in our systems (pumps, valves, heat exchangers etc.)

The problem is, Excel is useless at answering things like "How many components do we buy from Acne Corp?" or "What is the total cost for all components in Subsystem ABC?" I have to do it all manually for the moment.

But, every time I look into databases, it's either MS Access, which uses VBA for querying (which I'd like to avoid) or Databases with so much boilerplate involved that I feel like it's not something that I could implement over a weekend.

I've worked with SQL before (way back in the days of Borland C++), and am happy to implement something, but what is the bare minimum way of graduating from Excel to something more robust I can run queries against? I'm not a programmer, so the lower the barrier for entry, the happier I'll be. I'm open to all options.

mtmail 2 years ago |

I'd move to MS Access. It allows reporting via a nice user interface, querying with Visual Basic but also SQL (from VBA calls or from outside). https://learn.microsoft.com/en-us/office/client-developer/ac...

Then play with an exteral desktop app writing SQL queries against the database. For example https://www.dbvis.com/database/msaccess/ I'm sure there's open source software doing something similar.

After a couple of months you can go different directions: move database from MS Access to another database engine (MySQL, Postgresql, Sqlite, etc) while the SQL queries should still work. Or build up knowledge in VBA to query directly from MS Excel.

Another factor will be employability, what knowledge will be useful if you switch jobs, and working with collegues, will anybody else be able to maintain what you've build.

VladTheImpalor 2 years ago | |

Thank you for your suggestions! Two comments recommended Access, so I will look into it. Your progression is extremely logical, so thank you for it. I think this is the way I will go. From within Excel, the issue is I have several files, so will need to either combine them into one big one, or link them together.

kejaed 2 years ago |

If you aren’t currently, sounds like you are ready to use an ERP to track such things and link your BOMs to suppliers and purchasing.

You could probably get a BOM into something like Odoo over a weekend to test it out.

brudgers 2 years ago |

VBA for querying (which I'd like to avoid)

Why?

Is this a business reason that has priority over the problems you are trying to solve?

I feel like it's not something that I could implement over a weekend.

Statistically to a significant number of sigmas, it is unlikely you can implement a decent database over a weekend. I'm not saying there are not people who can, but if you were one of them, you probably wouldn't be asking.

Anyway, consultants are an obvious engineering strategy. Good luck.

mmob 2 years ago |

Have you tried using Power Query (Get & Transform) in Excel? I'd be surprised if it couldn't address your requirements for analyzing your data.

victorbjorklund 2 years ago |

Sqlite?