base data import from excel

[jdsz]

Welcome. For several months, he has been trying to solve his problem, primarily looking for answers here and there to the problems I encounter. So I thought that maybe I am not good enough in this matter and someone will help find, propose a simple and rational solution that I will try to strive for.

SITUATION:
Several users (User 1, User 2, User 3 etc.), each has access to a shared network - a shared disk. Each user stores his “case base” in an Excel workbook on his computer (WorkbookUser1, WorkbookUser2, etc.)

MAIN NEEDS:
All users can search for cases according to various criteria.

THIS WAS A SOLUTION:

1. I saved a macro to each user’s workbook, which saves a copy of the file to a shared disk every time it is edited and saved. (CopyWorkbookUser1, CopyWorkbookUser2 etc.) This saves me having to turn on users’ computers from which I would have to download data to search them.
2. I created the “main” workbook (CentralWorkbook) on a shared drive that I use to search for information. Opening this workbook (CentralWorkbook), downloads the sheets of each user (CopyWorkbookUser1.SheetsU1, CopyWorkbookUser2.SheetsU2 etc.), joins them into one sheet into one large list of cases in which I search for specific data.

As you can see, this is quite a difficult, time consuming and problematic solution.

So I thought I would use an accesa that would not burden the system so much. Unfortunately, not everyone has an accesa license and therefore I need to use the libreoffice database.

At first, I thought in LoBase to create tables for each user from whom they retrieve data from their workbooks (CopyWorkbookUser1.SheetsU1 → tableU1 in LoBase, CopyWorkbookUser2.SheetsU2 → tableU2 in LoBase etc.). And then I will search these tables in LoBase.
Unfortunately, there are problems again:

If CopyWorkbookUser1.SheetsU1, CopyWorkbookUser2.SheetsU2 were in LoCalc data for tableU1, tableU2 would download automatically. Unfortunately, they are in xls … and I don’t know how to import CopyWorkbookUser1.SheetsU1, CopyWorkbookUser2.SheetsU2 to tableU1, tableU2 first. Secondly, how to make them imported automatically …

Or maybe just from the beginning, I created the wrong assumptions, which is now repenting …
Please, do any of you professionals dealing with this every day give me tips, advice and ideas on how to solve my problem in the best and easiest way.

[erAck]

Readability hint: start a new paragraph now and then by hitting Enter twice, i.e. have a blank line between.

[jimk]

Could you dump the data from Excel into CSV or dBase formatted files? Then the data could be imported into LO Base.

Have you tried opening the .xls files in LO Calc? That would not be a good idea for editing the files but might work for reading them as long as they are not too complex. You might need to right-click on the file and select Open With… so that it does not default to opening in Excel.

Ideally, convert the spreadsheets to the .ods format and use LO Calc only. Then many convenient ways of moving the data into LO Base are available. But people may not be willing to do this.

[keme]

Do the users need to have the case base as a file on their own computer, or are they always connected to shared network and might use a web based shared database instead?

What kind of cases are we looking at? Software bugs? Sales pitches? Projects?

[jdsz]

thank you for your time and hint. I tried to write vba in Excel, and after closing it automatically saves copies in csv. But how do you automate updating data changes in a LoBase table?
(For now I have opened the saved csv file in LoCalc and added it as a source to LoBase. But when I change the data in the source spreadsheet, they do not change in LoBase)

[jimk]

Create a shared folder and dump the data from Excel into CSV files in that folder. Then create a new LO Base file and Connect to an existing database... Text. Select the folder where the CSV files are located. Now you can search through all of the data using Base.

The disadvantage is that queries can only work with one table at a time using this method.

To import a CSV file into a Firebird Embedded table automatically, you would need to write a macro. Learning how to write Base macros is not easy. However, you can search this site for information and ask questions if you get stuck. Keep in mind that embedded databases are easily corrupted.

Instead of importing data with a macro, it would be better to use a split database setup, with PostgreSQL or MySQL for example. The database would need to run on a server. Then load the data into MySQL, after which it can be viewed and queried fully in LO Base.

[jdsz]

thank you again, I know I can’t do without you. I analyzed and thought about accepting suggestions (expressed and implied). Can I ask you for advice on how best to solve my problem? Ver3 would be the best solution for my users, but I expect it to be very difficult. Version 1 seems the best, have you encountered such solutions? Can you give some examples or similar solutions that I could use to solve my problem? once again, thank you very much

[jimk]

While I don’t know your situation, my recommendation is to get rid of spreadsheets altogether, because they are not designed to maintain data coming from various sources. Use a database to store the data, and then set permissions on who can edit and view what data. LO Base has forms and queries, or you could create a web front end to the database.

[jimk]

The only way I would use a spreadsheet in your diagram is to generate it from the main table as a read-only copy. So, in ver 3, move the four excel workbooks to the right side of the main table, and draw lines from the main table and going right, pointing to the workbooks.