In my project users maintain data in Excel tables (with proper row and column headers). With time however, the Excel tables get pretty large. The amount of numerical data is not too large (~10mb per person over a year), however because the data is stored on their own computers, it gets lost say when they leave or after a project ends.
I know that Excel provides ODBC drivers for most Databases. Right now, I am trying to develop a prototype to demonstrate that the data can be stored on a central server where multiple people can directly read and write from. To keep things simple, I was just planning to write some VBA/Python code based on (https://stackoverflow.com/a/3768327/4752883), which would take the row and column based data from a specific sheet and dump it in the database. There are other ways to do it as well, such as using using a direct connection to MySQL (https://www.youtube.com/watch?v=E_kn71R9BL0)
However, I see a problem here. Say
userB pull data the
tableA from the MySQL database into Excel.
userA makes a change to the table (creates
tableB) and writes it to the
MySQL database using the macro. At the same time,
userB also has the same table open in Excel. He makes a change to the table (creates
tableC) and writes to the same row and
userA. This would lead to overwriting
userAs data. This is not intended behavior, as ideally
userB would know
that the database has been updated by
userA (similar to how when co-authoring
is enabled in a Excel workbook shared in OneDrive). However, during my
research, I didn't see a obvious manner by which
userBs Excel instance would
immediately update and notify
userB when the data in the MySQL database is updated by
These are 2 ways I can think of for overcoming the issue:
userAhas already updated their
tableBto the MySQL database. When
userBattempts to write their version of the table (
tableC) to the database using the macro, instead of blindly writing to the table, first check if there is any existing data in that row and column of the table in the MySQL database. If there is, then warn
userBthat there is new data in the MySQL database which hasn't been updated in his instance of Excel. Then fetch the data from the server into a separate worksheet (
SheetB). Then, they can update the main sheet/table to be pushed in
Sheet mainfrom the extra data in
SheetB. The excel macro keeps a record that
userBhas updated their Excel macro in the last 5 mins (for example), and if they have, it assumes its ok to overwrite the MySQL table with
userBs version of the table.
Lets say we have a starting table,
tableAin the database.
userApulls from the MySQL database into the Excel workbook. Then then modify the table (create
tableB) and push the data to a separate table
tableDis the differential between
userBpulls from the database and makes changes (creates
tableC). When they push from
tableCto the MySQL database, they create
tableE, which is the differential between
userBcan make changes to their individual tables
tableCas much as they want, for a certain period of time. At periodic intervals the delta
tableEare appended on to
tableA(using MySQL events https://dev.mysql.com/doc/refman/8.0/en/events-overview.html). In this scenario, neither
userBwould be able to change anything that was already present in the table when they pulled from the MySQL database in the first instance, but they can append to the table.
I can see that this is going to get more complicated, if there are more users. However, it seems to me that this issue should be pretty common and must have been
solved in databases. I have come across the
file-locking but I don't see how
it solves the problem of not overwriting
Is Option#1 or Option#2, the best we can do or is there more sophisticated techniques(already inbuilt into SQL database) by which mutliple users can read and write to a database from Excel, or other app?
Just to provide some more details to address @danblack and @Hila-DG's answers, one of the other requirements is that all the data from different users also need to curated into a central location, so that somebody who maynot be working on a specific worksheet and if they have proper access can run some job on that data as well.