Recent

Author Topic: Multiple spreadsheets combination (merge/appending rows; union of columns)  (Read 272 times)

tatamata

  • Hero Member
  • *****
  • Posts: 776
    • ZMSQL - SQL enhanced in-memory database
Dear Pascal friends,
This is something I am thinking for a while. There is common business need to combine multiple spreadsheets into one common, master spreadsheet.
Typically, this means to combine columns from multiple source datasets, so that rows are appended into new spreadsheet having union of columns, from all datasets and rows are being appended into master dataset from all source datasets.
Thinking about this, there are two possible approaches: one is to rely on columns naming, so that columns are mapped implicitly by their name, another approach would be to provide explicit columns mapping.
Surprisingly, this is not covered at all by major open-source free spreadsheet software! Neither LibreOffice Calc, neither Gnumeric, neither Calligra Sheets, nor any others have any feature covering this common business need!
Not only that, but even software intended to be specialized for data profiling and data cleansing, like OpenRefine or DataCleaner, are short when this task comes.
On the contrary, Microsoft Excel has answer on this, but only with Power Query. Needless to say, it works very good with small datasets, but becomes crazy with larger datasets.
Anyway, I was surprised to find that Google search on this topic is a total mess, not much useful content to find. If you try to find any useful VBA code, you will find lot of crappy source code assuming that input datasets have same scheme, which is total nonsense. It is almost never like this in real life! This assumption is so, so wrong!
Only useful VBA code I found so far is this: https://danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
I am considering to start programming something in Lazarus, but before I start, I would like to know is there anything already available as a staring point?

tatamata

  • Hero Member
  • *****
  • Posts: 776
    • ZMSQL - SQL enhanced in-memory database
Re: Multiple spreadsheets combination (merge/appending rows; union of columns)
« Reply #1 on: February 24, 2023, 07:44:08 pm »
I have implemented it in the MightyQuery.

Source code (CodeTyphon v. 8.00):
https://matasoft.hr/MightyQuery.7z
Compiled binaries for Linux and Windows:
https://matasoft.hr/MightyQuery_Binary.7z

 

TinyPortal © 2005-2018