A conditional WRITE to an external file from Calc

[ve3oat]

I don’t know anything about Macros, but I am in need of some way to perform a conditional WRITE operation to another Calc file from the data in my main Calc file.

The situation is a large (over 8000 line) Calc file where a recorded value occasionally exceeds a given threshold value, and I want to extract every occurrence of this condition and record it in a separate Calc file.

Say, a line in my main file (Voltage-date.ods) is this :

A544        B544        C544    D544    E544    F544    G544
2023-04-07	02:30:00	2.5000	7.1042	124.7	124.1	125.4

So my test is logically something like :
IF(G544>125) THEN WRITE (A544,B544,C544,D544,E544,F544,G544) to OV-date.ods

And, after a time, the separate file might look something like this :

A              B          C          D          E           F         G
2023-04-04    12:18:00   7.1500     4.2200     122.0       121.4      125.2      
2023-04-07 	  02:30:00	 2.5000	    7.1042	   124.7	   124.1	  125.4
2023-04-13    05:42:00   3.1000     3.1900     123.8       122.9      126.1

…

Is there an easy way to do this, or will it involve a lot of code that I might not be able to figure out eventually?

[vib]

This is possible to do in python as a script. I am not sure about in a basic macro.

See: Python LibreOffice Programming Calc Chapter 21. Extracting Data of the OOO Development Tools (OooDev) project.

I am the author of OooDev and I think you needs would make a good example to put on LibreOffice Python UNO Examples.

If you want to go this route I am willing to help you and also make this demo (not with your data, of course).

[joshua4]

Two clarifications questions:

1. Does the OV-date file need to be an ODS/ODF file, or can it be CSV (comma-delimited values)?
2. Is the OV-date file read continuously (say, by automation equipment), or can it be produced as a batch job during user interaction with Voltage-date?

[ve3oat]

@vib – Thank you very much for your reply. I have never coded in Python and the examples look much more complicated that I think I can absorb easily at this stage. I think this won’t be an easy solution for me as I might need a lot of help to do it. Let me think about this for a bit. (Must find my book about Python first. )

@joshua4 – You have asked good questions.

1. The external file does not need to be ods or odf, and .csv would be very easy to deal with.
2. There is no automation equipment involved at this stage, so, Yes, the OV-date file can be produced during user interaction with the voltage-date file.
   Does that lead to an easier solution??

Thanks to both of you for your responses, which have helped me to clarify the requirement even in my own mind.

[vib]

Working with the API is complex in many cases for python and basic.

While there would be a bit of complexity in your use case it would be nearly as overwhelming as the documentation is suggesting. Don’t let the documentation get you too overwhelmed.

[robleyd]

Would using a standard filter, outputting the results to a separate sheet, solve the first part of the problem? Once the filtered data is on a separate sheet you could a) save the sheet as CSV or b) copy it elsewhere as required.

[Villeroy]

91253.ods (29.8 KB)

If it still needs to be a separate file (why?):

1. Create a new spreadsheet document.
2. menu:Sheet>Insert from file… and specify the filtered sheet of the source file.

Next time:
Refresh the database range, save the file, open the other file. Either you will be prompted to refresh the link or you call menu:Edit>Links… and click [Update]

In the field of desktop computing, databases on sheets are the most popular misconception of the past 3 decades.

[sokol92]

Another easy option:

1. Apply autofilter with the required conditions.
2. Copy the (filtered) cells to the clipboard.
3. Paste in the desired location of the target file.

[Villeroy]

@sokol92

Apply autofilter with the required conditions.

“Autofilter” works with = operator only. @ve3oat wants to filter values >125.

[sokol92]

@Villeroy

“Autofilter” works with = operator only

In the Autofilter for any field, you can select Filter by Condition / Standart Filter...
At the same time in another field, for example, you can select values.
Is there something wrong?

[ve3oat]

Wow! Thank you, everyone! I learned something from each of your comments and suggestions!

Never having used Filters before, I had not even considered the possibility of using a filter. Filters, of course, is an easy-to-implement and obvious (once one considers it) solution to my task, as suggested by @robleyd and @sokol92. So, I selected the data to be filtered, specified a Standard Filter with the necessary criterion, and with the filter output to begin at a cell in the same sheet (as suggested by @Villeroy, why go to a separate file anyway?), and let the filter do its work. Bingo! Saved me several hours of manually extracting the data. And I learned some very useful things while doing this, as well as thinking that I should learn to do some things in Python (thanks, @vib).

Thank you again, All of You.