0

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 userA and 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 column of 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 userA.

These are 2 ways I can think of for overcoming the issue:

  1. Lets assume userA has already updated their tableB to the MySQL database. When userB attempts 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 userB that 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 userB has 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 userB s version of the table.

  2. Lets say we have a starting table, tableA in the database. userA pulls from the MySQL database into the Excel workbook. Then then modify the table (create tableB) and push the data to a separate table tableD (where tableD is the differential between tableA and tableB). userB pulls from the database and makes changes (creates tableC). When they push from tableC to the MySQL database, they create tableE , which is the differential between tableA and tableC. Both userA and userB can make changes to their individual tables tableB and tableC as much as they want, for a certain period of time. At periodic intervals the delta tableD and tableE are appended on to tableA (using MySQL events https://dev.mysql.com/doc/refman/8.0/en/events-overview.html). In this scenario, neither userA or userB would 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 userAs changes.

Question:

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.

closed as too broad by MDCCL, hot2use, Md Haidar Ali Khan, mustaccio, Vérace Sep 8 '18 at 13:06

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 6
    Sounds like you need a cloud based spreadsheet editing system (Google, Office365, others?) more than a database. – danblack Aug 27 '18 at 4:31
  • @danblack, thanks for your comment. If we do use the Excel workbooks in a cloud based editing system, what would be the best practice to get the data into a central location from all users, so as to be able to do some kind of ETL? – alpha_989 Aug 27 '18 at 23:29
4

This is a very good question, you've obviously done your research. The solution isn't to use Excel since Excel doesn't have write-back capabilities!

I am going to elaborate on @danblack answer: You need a cloud based spreadsheet editing system, (google, office 365, dropbox, other) The Cloud Based Spreadsheet will be shared among the relevant stakeholders When a cloud based spreadsheet is updated it is updated across the board. If user A added something and User B added something then both changes are maintained. If user A add something and User B delete that same row then you can see the changes and ask them to clarify, etc .

Think about it this way: if your problem sounds simple you need to find a simple solution

  • appreciate your response.. yeah, actually that was my first recommendation. The reason I was thinking of storing the data in a database is because, when we keep the data in cloud based excel sheets, there will be 100s of such excel sheets over time stored at different locations. We also need to get the data entered in a specific table into a location from which somebody who didn't create the spreadsheet can do some kind of ETL or create models. – alpha_989 Aug 27 '18 at 23:08
  • To solve this problem I was thinking maybe I could create workbooks for each individual/groups in very specific OneDrive location for each group. Then run a nightly cron job to pull data from those excel workbooks into a central database, if the workbooks are not open. Do you see any problems with this approach? – alpha_989 Aug 27 '18 at 23:15
  • @alpha_989: only issue would be you have to be very specific what are the allowed columns in the Excel spreadsheet. People have the tendency to add a column in the end for their own calculations, etc. If you're using the spreadsheet for the basis of the ETL process then that's a different story – Hila DG Aug 28 '18 at 0:51
  • Makes sense. Yeah.. For the particular sheet in the users excel workbook, from which the data will be uploaded to the database, I was planning to create a more "strict" table, such that users can't add tables without headers etc (maybe using some kind of form). – alpha_989 Aug 28 '18 at 2:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.