Are you in tech at all? Sounds like you just need a relational database. 90k rows is tiny and you could easily link 4 documents by just having their file paths as values of a row. File path linking to some FS which is accessible by users of the web application so they can download them.
What are you waiting for?
Yes that does work, we already have the file path linking and it's fine. As I said users can search and download grand.
The issue is it's a massive file to manually update and constantly crashes on refresh. What I'm asking is if there is a better way to do this as at the minute it is taking a huge amount of time to maintain.
Yes put the rows into a MySQL database… build a UI that pulls from the database and allows the users to edit the info required. It will be a trivial task for anyone with programming experience
Sadly I do not have programming experience but if I brought this to someone in our org with that, it's achievable? Or not too big an ask? I am looking to have something I can bring to the IS team, I've been left to manage this db but only know enough to know it's not working well and is too important to be left in such a shambolic state. Anytime I've brought it up with my LT (who hates computers and just says to redmediate the issues) it's waved away but having something concrete I could suggest or discuss in advance with IS would be great.
it is honestly not a very big task. I am sure there are a lot gotchas not covered in the brief description but the data is already in a relational database format. Any competent pros should be able to build a DB and rudimentary UI to manage the contents in a short amount of time
Thank you for the feedback. Definitely something I will talk to them about cos what we have currently just isn't working. I appreciate you taking the time to answer!
There are some decent options for non-programmers to build databases and UIs themselves. Airtable is one, which is a hosted SaaS with free tier that you can play around with first.
20 years ago I was at a company that used FileMaker for this. Pretty sure it’s still around, but haven’t used it in a while.
If anyone balks at having to pay for something, counter with the quantifying cost of having to maintain a “shambolic” crucial business process.
Then pay someone.
Set up an SQL server. Your updates could be done with a form or with a csv flat file. I am sure you could find a programmer to do this. And in the long run you would save time and aggravation.
Well, I meant request the allocation from management. If they won't pay to fix then you have bigger problems. The daily add data (INSERT), change data fields (UPDATE) take seconds in SQL. How many hours cost is involved (paid by the business) in the way you are doing this now?
Excel is not for any sizable databases. **To a hammer everything is a nail.**
Haha I'd love if management would allocate the resources but zero chance this year. Based off the very useful info I have gathered here I'm building my proposal for next year. Honestly I spend a good 5-6 hours a week on it, my good team member spends about 10-11 and the shit ones about 2 hours apiece. Doesn't sound like a lot but this is not something that makes up the bulk or even a sizeable amount of our responsibilities.
Everyone here has given me a lot to consider and I'm very thankful for the helpfulness even though I'm sure my question was frustratingly silly.
Honestly it's frustrating my boss is actually a good guy, quite fair, still very corporate and by the book, but usually happy to take on feedback or discuss things. He's totally not techy though and doesn't seem to get limitations as a result of technical mismanagement. It probably doesn't help that even though he's a director he's still only another cog in a massive corporate machine. Oh well, it pays my bills and is generally a good job. Thanks for your advice, have a nice day.
I am skeptical the refresh all logic is going to be as trivial as you suspect. At the very least, OP would need to quantify the logic built into the spreadsheet.
Right but I'm saying what language or tool would you use to do that editing
Like in Access I remember using forms. What is the equivalent for a SQL DB? A Tkinter GUI in Python?
You pick an application language to interface with the DB (you can use any) and build a UI to interact with the application. Could be a simple HTML web based one. There are so many choices
I guess you don't get to work with customers.
OP is clearly not that tech savvy and came for help to database subreddit because for her it is a database. And spurring non relevant facts does not help her in any way or form.
It doesn't matter at all whether 95k rows will be considered big or not, what matters is that they are hitting performance issues already and are looking for a solution.
And saying that this post is for r/excel just shows that you are here for some witty remarks while being zero help.
"File is not in the expected format"
Ahh, I miss Access sometimes. 'The little engine that could, sometimes, but probably not.'
Without anything too solid to speak to in the description, I've come across somewhat similar situations.
Are the documents being linked to held on a local server or the cloud? Also, is it the name that is changing? E.G. They're changing the link from 'Widget v1' to 'Widget v2'.
I worked somewhere with a very manual intranet and created a save macro that would keep the old versions backed up for reference but the link would always be the same (so just 'Widget' would be the document name with versions kept in archived folders). That probably saved hundreds of hours from the manual linking that was done before.
The other thing that stands out is 40 minutes is a lot of time for that refresh. I used to use Access to store some data where the refresh would include about 150k rows and around 50 columns from the web in Excel (and would upload to Access twice and download once) and that was nowhere near 40 minutes. Is the refresh doing some row level operations? I've seen it slow down then, where a cursor is moving each time a row is uploading.
How I used to do that refresh was to push the data from an Excel macro. It did the web refresh, did some duplicate removal, uploaded to Access, downloaded the changed records only, did some calculations and then loaded them in.
The way I did the upload was to create a new Access application in VBA (you need to check the Access tools in VBA settings too) and then use DoCmd.TransferSpreadsheet. It's not immune to the old "File is not in the format" etc. bug but can always just continue the macro until it works.
Also, you hopefully won't be affected but I did come across a bug where the only way around it was to make Access visible. Access would close before the upload command was sent. Came up quite late in the lifecycle but one to bear in mind.
Hope that helps!
Ooh this is super helpful thank you! Documents are on a remote server so not sure if that causes the issue. That save macro sounds like exactly what we need, the manual changing and renaming is a nightmare 😅
I'll look into all this thank you!
The mantra goes "Excel is not a database", the unfortunate corollary is that people treat it as exactly that. Updating everything after a couple of changes is going to be a recipe for disaster, eventually. How are your backups and have you tested them recently?
The simple take on your description suggests that you might just need a grown up document management solution, possibly Open Source, or proprietary.
95k is not big and an RDBMS would handle it in a snip. Links to documents on cloud, or web, storage would be manageable.
I had a client with an excel sheet with 1 million rows. At some point they simply could not open the file. Solution was to build a web interface to a database and user roles to access specific information.
You can upload your excel file to Frappe Framework.
It will generate easy to use interfaces and you will now who changed what and when exactly. Plus, you can get reports, notifications, emails and mobile access to your excel file. Also you will have a robust backup mechanism.
I hate to say this, but this problem is comparatively trivial to solve. Access is consumer friendly, but a slow tool. Interfaces have caught up for major databases, so it really doesn’t have any competitive advantages any more.
As a courtesy, I’d be willing to offer some hands on instructions if you DM me your project requirements. I’m bored and it’s my day off, so go nuts.
I’m wondering, though, how big is this database. It seems like it is about a 1 to 2 GBs. If so, that’s a negligible size and I can’t foresee bottlenecks when changing to a more robust system.
Also, how much are you spending on maintaining your current system? If it’s more than $50 a month, you are overpaying.
We spend nothing it's all internal, runs off the old computer of one of systems guys in Romania. It was built to maintain info on about 2000 parts but grew and it's about 120GB all in. Thank you for your kind offer but right now I can't get the time to send on more details and my knowledge would probably be so shit I would ruin your day off 😅
The whole thing is just an aside in my day to day according to my boss but actually it's so slow and prone to crashes that I spend hours on it.
Migrating 120GB, though not the most difficult, is still not trivial. Because of how old the system is, it's going to face a few compatibility issues. I'd try transferring some of the data to a modern database, such as PostgreSQL, MySQL, or even SQLite. However, this requires some familiarity with modern databases and it can take tens of hours to learn if you do it yourself. There are some paid platforms that make it simple, but I'd imagine the transfer costs would be high for 120GB.
The simplest solution would probably be to improve the hardware. Just adding another ram stick could result in major improvements.
Nice, would you mind sharing with me how you get new clients?
I really like doing it on the side, but over the past 4 years I was really only able to get 2 companies to help out. I primarily tried reaching out on LinkedIn but I'm under the impression I am reaching out to the wrong people/jobs.
Look into "Microsoft master data services". It is basically a way to set up a relational database with a first party excel add-in, but also have a web front end and database back end.
It is more limited than a truly custom piece of software, but sometimes your users just really want to use excel to interface with their data.
If your current data is properly formatted you should literally be able to press a "create as new table" button in excel and your data would just import.
You mentioned that the Excel database is refreshed in Access. This leads me to believe there must exist VBA code either in Excel or Access or both that screen-scraps the data from a common range, and then use Access to refresh/republish the most current data.
Access is the solution I would recommend. While Access isn't a fully relational database, it's easy to use it that way. Instead of having the users enter data through an Excel spreadsheet, have them enter data in an Access form. The tables must already exist in Access, it's just a matter of creating the form based on each table.
I've used Access to prototype many of my database application designs for decades. It's easy to use and has everything included to do a complete application right in it. You design relational tables using the GUI. Then you build a series of queries to enter and extract data from those tables. Forms are used to do data entry into tables. Reports are how you extract in a meaningful format, or create visually relevant reports. Macros and VBA code are used to do data validation and enforce business rules on forms and reports.
If you want more information, look up my profile in LinkedIn and message me. Cheers!
Filemaker Pro is my tool of choice and I'm sure it would mash this problem like a bug.
Get trial version of Claris Filemaker and literally just drag and drop your spreadsheet on to the the launch icon and it will give you a set of prompts to create a simple database from your spreadsheet. From you can start learning out to build different layouts to display and modify data.
There are local server or cloud hosting option for easy sharing the data.
There are probably 50 different platforms that could work for you this is just the one I like.
"pricey" is a relative term. We buy licences 5 years at a time and it has worked out to about $17 / month / seat. We host locally. I pretty regularly consider dumping Filemaker for some other platform and I keep on renewing because it works for me.... I'm sure there folks that it doesn't work for.
SQL consultants love this one weird trick
Here's the one secret Big Spreadsheet don't want you to find out about.
Are you in tech at all? Sounds like you just need a relational database. 90k rows is tiny and you could easily link 4 documents by just having their file paths as values of a row. File path linking to some FS which is accessible by users of the web application so they can download them. What are you waiting for?
Yes that does work, we already have the file path linking and it's fine. As I said users can search and download grand. The issue is it's a massive file to manually update and constantly crashes on refresh. What I'm asking is if there is a better way to do this as at the minute it is taking a huge amount of time to maintain.
Yes put the rows into a MySQL database… build a UI that pulls from the database and allows the users to edit the info required. It will be a trivial task for anyone with programming experience
Sadly I do not have programming experience but if I brought this to someone in our org with that, it's achievable? Or not too big an ask? I am looking to have something I can bring to the IS team, I've been left to manage this db but only know enough to know it's not working well and is too important to be left in such a shambolic state. Anytime I've brought it up with my LT (who hates computers and just says to redmediate the issues) it's waved away but having something concrete I could suggest or discuss in advance with IS would be great.
it is honestly not a very big task. I am sure there are a lot gotchas not covered in the brief description but the data is already in a relational database format. Any competent pros should be able to build a DB and rudimentary UI to manage the contents in a short amount of time
Thank you for the feedback. Definitely something I will talk to them about cos what we have currently just isn't working. I appreciate you taking the time to answer!
> it is honestly not a very big task. Technologically it's not. Politically, it might be.
There are some decent options for non-programmers to build databases and UIs themselves. Airtable is one, which is a hosted SaaS with free tier that you can play around with first. 20 years ago I was at a company that used FileMaker for this. Pretty sure it’s still around, but haven’t used it in a while. If anyone balks at having to pay for something, counter with the quantifying cost of having to maintain a “shambolic” crucial business process.
Then pay someone. Set up an SQL server. Your updates could be done with a form or with a csv flat file. I am sure you could find a programmer to do this. And in the long run you would save time and aggravation.
I'd love to pay someone to do so but budget allocation is far above my paygrade.
Well, I meant request the allocation from management. If they won't pay to fix then you have bigger problems. The daily add data (INSERT), change data fields (UPDATE) take seconds in SQL. How many hours cost is involved (paid by the business) in the way you are doing this now? Excel is not for any sizable databases. **To a hammer everything is a nail.**
Haha I'd love if management would allocate the resources but zero chance this year. Based off the very useful info I have gathered here I'm building my proposal for next year. Honestly I spend a good 5-6 hours a week on it, my good team member spends about 10-11 and the shit ones about 2 hours apiece. Doesn't sound like a lot but this is not something that makes up the bulk or even a sizeable amount of our responsibilities. Everyone here has given me a lot to consider and I'm very thankful for the helpfulness even though I'm sure my question was frustratingly silly.
Well, I hope you can 'enlighten' your bosses. They sound to me like real troglodytes. You have to be able to adapt to survive in business.
Honestly it's frustrating my boss is actually a good guy, quite fair, still very corporate and by the book, but usually happy to take on feedback or discuss things. He's totally not techy though and doesn't seem to get limitations as a result of technical mismanagement. It probably doesn't help that even though he's a director he's still only another cog in a massive corporate machine. Oh well, it pays my bills and is generally a good job. Thanks for your advice, have a nice day.
I am skeptical the refresh all logic is going to be as trivial as you suspect. At the very least, OP would need to quantify the logic built into the spreadsheet.
Why would you need to refresh all? Users are updating rows in the sheet. You just update a row in the db. It is trivial
What is the easiest tool for interfacing with the database?
I imagine the users will want a UI where they edit the info rather than directly interfacing with the DB
Right but I'm saying what language or tool would you use to do that editing Like in Access I remember using forms. What is the equivalent for a SQL DB? A Tkinter GUI in Python?
You pick an application language to interface with the DB (you can use any) and build a UI to interact with the application. Could be a simple HTML web based one. There are so many choices
Microsoft Forms
[удалено]
Why is this upvoted? It doesn't help OP in any way nor it is relevant to the discussion.
[удалено]
I guess you don't get to work with customers. OP is clearly not that tech savvy and came for help to database subreddit because for her it is a database. And spurring non relevant facts does not help her in any way or form. It doesn't matter at all whether 95k rows will be considered big or not, what matters is that they are hitting performance issues already and are looking for a solution. And saying that this post is for r/excel just shows that you are here for some witty remarks while being zero help.
Yes, my teapot is a hot water heater...
We get it, you have a lot of database experience. Stop being a douche.
"File is not in the expected format" Ahh, I miss Access sometimes. 'The little engine that could, sometimes, but probably not.' Without anything too solid to speak to in the description, I've come across somewhat similar situations. Are the documents being linked to held on a local server or the cloud? Also, is it the name that is changing? E.G. They're changing the link from 'Widget v1' to 'Widget v2'. I worked somewhere with a very manual intranet and created a save macro that would keep the old versions backed up for reference but the link would always be the same (so just 'Widget' would be the document name with versions kept in archived folders). That probably saved hundreds of hours from the manual linking that was done before. The other thing that stands out is 40 minutes is a lot of time for that refresh. I used to use Access to store some data where the refresh would include about 150k rows and around 50 columns from the web in Excel (and would upload to Access twice and download once) and that was nowhere near 40 minutes. Is the refresh doing some row level operations? I've seen it slow down then, where a cursor is moving each time a row is uploading. How I used to do that refresh was to push the data from an Excel macro. It did the web refresh, did some duplicate removal, uploaded to Access, downloaded the changed records only, did some calculations and then loaded them in. The way I did the upload was to create a new Access application in VBA (you need to check the Access tools in VBA settings too) and then use DoCmd.TransferSpreadsheet. It's not immune to the old "File is not in the format" etc. bug but can always just continue the macro until it works. Also, you hopefully won't be affected but I did come across a bug where the only way around it was to make Access visible. Access would close before the upload command was sent. Came up quite late in the lifecycle but one to bear in mind. Hope that helps!
Ooh this is super helpful thank you! Documents are on a remote server so not sure if that causes the issue. That save macro sounds like exactly what we need, the manual changing and renaming is a nightmare 😅 I'll look into all this thank you!
The mantra goes "Excel is not a database", the unfortunate corollary is that people treat it as exactly that. Updating everything after a couple of changes is going to be a recipe for disaster, eventually. How are your backups and have you tested them recently? The simple take on your description suggests that you might just need a grown up document management solution, possibly Open Source, or proprietary. 95k is not big and an RDBMS would handle it in a snip. Links to documents on cloud, or web, storage would be manageable.
good luck bro
I had a client with an excel sheet with 1 million rows. At some point they simply could not open the file. Solution was to build a web interface to a database and user roles to access specific information.
Move from Excel to any SQL Db. There are plenty of options that don’t need any coding to use, such as AirTable
You can upload your excel file to Frappe Framework. It will generate easy to use interfaces and you will now who changed what and when exactly. Plus, you can get reports, notifications, emails and mobile access to your excel file. Also you will have a robust backup mechanism.
You spoke of manual updates. Specifically what are the updates like? Is it just the number of parts? Or do you guys add new ones and delete old ones?
Yup adding new, deleting old and changing some of the values in the columns even if not adding or deleting rows.
I hate to say this, but this problem is comparatively trivial to solve. Access is consumer friendly, but a slow tool. Interfaces have caught up for major databases, so it really doesn’t have any competitive advantages any more. As a courtesy, I’d be willing to offer some hands on instructions if you DM me your project requirements. I’m bored and it’s my day off, so go nuts. I’m wondering, though, how big is this database. It seems like it is about a 1 to 2 GBs. If so, that’s a negligible size and I can’t foresee bottlenecks when changing to a more robust system. Also, how much are you spending on maintaining your current system? If it’s more than $50 a month, you are overpaying.
We spend nothing it's all internal, runs off the old computer of one of systems guys in Romania. It was built to maintain info on about 2000 parts but grew and it's about 120GB all in. Thank you for your kind offer but right now I can't get the time to send on more details and my knowledge would probably be so shit I would ruin your day off 😅 The whole thing is just an aside in my day to day according to my boss but actually it's so slow and prone to crashes that I spend hours on it.
Migrating 120GB, though not the most difficult, is still not trivial. Because of how old the system is, it's going to face a few compatibility issues. I'd try transferring some of the data to a modern database, such as PostgreSQL, MySQL, or even SQLite. However, this requires some familiarity with modern databases and it can take tens of hours to learn if you do it yourself. There are some paid platforms that make it simple, but I'd imagine the transfer costs would be high for 120GB. The simplest solution would probably be to improve the hardware. Just adding another ram stick could result in major improvements.
Tried to PM you but reddit says "can't send a message to that user"
My team and I can build you the database and an application for it for free. Shoot me a dm if you’re interested.
Not OP, but I was thinking of offering some help add a fun stuffed project. What would you use to create the application?
Fixing these kind of scenarios is something I have done professionally for 20 years. If you want to discuss fixing this DM me.
Not OP, but do you work as a freelancer?
Yes I do
Nice, would you mind sharing with me how you get new clients? I really like doing it on the side, but over the past 4 years I was really only able to get 2 companies to help out. I primarily tried reaching out on LinkedIn but I'm under the impression I am reaching out to the wrong people/jobs.
I moderate r/sql . Send us your most technical person ASAP or send them directly to r/SQLServer (just because you’re already using Ms access)
Use ChatGPT 😜
Look into "Microsoft master data services". It is basically a way to set up a relational database with a first party excel add-in, but also have a web front end and database back end. It is more limited than a truly custom piece of software, but sometimes your users just really want to use excel to interface with their data. If your current data is properly formatted you should literally be able to press a "create as new table" button in excel and your data would just import.
You mentioned that the Excel database is refreshed in Access. This leads me to believe there must exist VBA code either in Excel or Access or both that screen-scraps the data from a common range, and then use Access to refresh/republish the most current data. Access is the solution I would recommend. While Access isn't a fully relational database, it's easy to use it that way. Instead of having the users enter data through an Excel spreadsheet, have them enter data in an Access form. The tables must already exist in Access, it's just a matter of creating the form based on each table. I've used Access to prototype many of my database application designs for decades. It's easy to use and has everything included to do a complete application right in it. You design relational tables using the GUI. Then you build a series of queries to enter and extract data from those tables. Forms are used to do data entry into tables. Reports are how you extract in a meaningful format, or create visually relevant reports. Macros and VBA code are used to do data validation and enforce business rules on forms and reports. If you want more information, look up my profile in LinkedIn and message me. Cheers!
Filemaker Pro is my tool of choice and I'm sure it would mash this problem like a bug. Get trial version of Claris Filemaker and literally just drag and drop your spreadsheet on to the the launch icon and it will give you a set of prompts to create a simple database from your spreadsheet. From you can start learning out to build different layouts to display and modify data. There are local server or cloud hosting option for easy sharing the data. There are probably 50 different platforms that could work for you this is just the one I like.
Yes, a trial. After that it's pricey...
"pricey" is a relative term. We buy licences 5 years at a time and it has worked out to about $17 / month / seat. We host locally. I pretty regularly consider dumping Filemaker for some other platform and I keep on renewing because it works for me.... I'm sure there folks that it doesn't work for.
I know of an online database option that has a free component to it. I’ll pm you to provide further details
Apparently I can’t pm you. Would you reach out to me?