Forum > Databases

Summarizing spreadsheet data with an SQLite database


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 ( 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,

Why so complicated via SQLite3? The entire task can be solved by less than 100 lines of code within fpspreadsheet - see attached modified project.

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,


[0] Message Index

Go to full version