Recent

Author Topic: Summarizing spreadsheet data with an SQLite database  (Read 2709 times)

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Summarizing spreadsheet data with an SQLite database
« on: August 25, 2021, 01:37:25 am »
Dear ALL,

Today my problem is the following: I have a spreadsheet with data on plant specimens collected in some plots. The spreadsheet is read using the truly great FPSpreadsheet component (https://wiki.freepascal.org/FPSpreadsheet). I want to summarize the data in the spreadsheet, generating a matrix containing the counts of the number of specimens in each species and each plot, saving the summary to a comma-delimited file and then loading that file in a stringgrid.

I would like to achieve all the above using an "in-memory" SQLite table with SQL queries for the data counts, however I could not devise a clear way of doing that. Could you wizards give me some hints?

I attach a sample project, including the original data spreadsheet in Excel97 format and a summary matrix in csv format, which I hope may provide a more clear idea of my problem and needs. I also attach an hopefully illustrative screenshot.

Thanks in advance for any assistance you can provide!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Delphi 7.0 Personal Edition
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19.1, Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Summarizing spreadsheet data with an SQLite database
« Reply #1 on: August 25, 2021, 10:10:47 am »
Why so complicated via SQLite3? The entire task can be solved by less than 100 lines of code within fpspreadsheet - see attached modified project.

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: Summarizing spreadsheet data with an SQLite database
« Reply #2 on: August 25, 2021, 02:03:44 pm »
Dear @wp,

Thanks a lot for your code. It really solved my problem. I would not expected it could be done entirely with FPSpreadseet without requiring SQL queries! In fact, it makes the string grid even redundant, since using a second worksheet for displaying the matrix already does what I need (and it can easily be exported to a csv file anyway).

But... I would still like to be able to have a way to filter rows ("records") in the spreadsheet, using several criteria in SQL-like queries (eg., for displaying only the plants belonging to a certain family, or all specimens from a given family collected at a certain locality, etc.) Is there a way of doing this using the FPSpreadsheet component only? But perhaps it should be better to open a separate topic for this question, since it may be of interest for other users.

With best wishes,

UCSD Pascal / Burroughs 6700 / Master Control Program
Delphi 7.0 Personal Edition
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19.1, Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018