Programm to show a filtered list of a csv file.

[eTobey]

Hi,
i need the ability, to filter data in a csv file in realtime. I wont work with libre office, as the filter is removed after each update on the data link.

Anyone knows a program that could do some realtime filtering?

[PA0PBZ]

Reading the title I immediately thought 'any spreadsheet can do that' but apparently that is not going to work for you.
Care to explain a bit more, like for instance what do you do with the filtered results and what is the update rate?

[brucehoult]

Grep, awk, perl, python, ruby, ... ?

[radiolistener]

Basically almost any language can do that - octave, python, js, c, pascal, basic, etc... 

You can even connect it to ZX Spectrum and do it in Z80 asm 

[Ian.M]

Write a macro for LibreOffice Calc, that removes the filter, deletes the previous filtered data, imports the CSV file, sets up any named ranges required to match the number of lines of new data from the CSV, and reapplies the filter.   Its *NOT* rocket science - any competent programmer, even one unfamiliar with LibreOffice, could  adapt to their dialect of BASIC and knock out the code in under a day. Any MS VBA / Visual Basic programmer will find it even more familiar.  Most of the stuff you need to know can be found by using the macro recorder to record the actions to perform the task manually, then looking at the code it generates to parameterise it so it takes the number of lines of old data from the current spreadsheet contents, and the number of lines of new data from the contents post CSV import.

[eTobey]

Actually i dont want to write any more scripts... i want get to work on my microcontroller. I already wrote enough scripts/programs for the shortcomings of the SDS800X HD.

I dont write another script for a program that checks another program that checks my original program/software that i want to develop. Its a rabbithole...

[abeyer]

It would help if you'd actually reply to the request for more details about your requirements/use case, then, if you're looking for something that does exactly what you want out of the box.

I'd take a look at https://github.com/dbohdan/structured-text-tools?tab=readme-ov-file#csv and maybe https://til.simonwillison.net/sqlite/one-line-csv-operations

[Smokey]

this is exactly the type of job for chatgpt.  paste your csv data format, and how you want it filtered, and it will generate a script in a language of your choosing. 

[SiliconWizard]

I dont write another script for a program that checks another program that checks my original program/software that i want to develop. Its a rabbithole...

Well, then don't. Do as everyone does in the software industry these days, do not check much, ship fast and see what happens. It may not always work, but it's good money.

[Nominal Animal]

Actually i dont want to write any more scripts...

Ah, copy that.

I was going to post an example filter in Python, but that would have been a script, of course.

I have also created sheets in LibreOffice/OpenOffice/Excel that filter the rows of another sheet using an arbitrary filter criteria, but that requires constructing some very crafty formulae and filling basically all cells of a sheet with them, so I think that's out too.

Too bad.  I guess your best bet is pasting the data to ChatGPT or similar, and hope it hallucinates something useful for you.

[brucehoult]

I guess you could hire someone in India or Congo or something to filter the data.

You'd have to give them precise instructions on what exactly you mean by "filter" .. some sort of sequence of the things they'd need to do.

[paulca]

What do you mean by the terms:

"Data link"

and

"Realtime"

I'm not answering without this.

[eTobey]

To clarify some details:
Filter:
I have many "id's" that also are "34" and "36". I want to filter them, meaning, they should not appear in the list anymore. Only the other "id's" should be in that list.

Realtime:
It should show the list from the data link (the data that is in a csv file and which that program should show), as it currently is, with a maximum delay of like 2 seconds.

Libreoffice almost did that. Almost...

Well, then don't. Do as everyone does in the software industry these days, do not check much, ship fast and see what happens. It may not always work, but it's good money.

Good idea  . Problem is: I want to use this product too, and it should never fail.

chatgpt wants me to install stuff for this task. Not what i want...


In the meantime i got my DSLogic Plus:
Tried looking at the CAN decode list: Its a bloody useless! Every detail of the CAN frame is in one column. 
Now i had to create a script after all.  Still its quite cumbersome to save the data every time. 

I am almost to a point to want to get rid of this hobby alltogether. Working on a bigger project is very frustrating, if the testequipment/software is not that useful (for the affordable ones). Writing a script for just so basic things every day...   

[PA0PBZ]

Writing a script for just so basic things every day...   

That's exactly where scripts are for, doing basic things all day that you don't want to do manually...

[Nominal Animal]

In Linux/Android/Mac/BSDs, this would be a trivial thing, combining inotifywait, fsnotifywait, or fswatch (documentation) and a simple Python script.

The Python script would run the watcher in a subprocess, reporting changes to the target directory.  On close_write or moved_to event affecting the source CSV file, the script re-reads it, saving the filtered results to a target CSV file.  The latency depends on the total CPU and I/O load on the machine, but is a small fraction of a second in all but fully CPU or I/O loaded situations.

Python 3 has a built-in csv library, whose excel and excel-tab dialects with csv.QUOTE_NONNUMERIC quoting is rock solid for Excel, LibreOffice, and OpenOffice.  I've done this several times for various large datasets in real life.

I'd need to write only about thirty lines of code to achieve this.  Add another twenty or so for a minimal Qt/GTK/Tkinter GUI window for stopping the script, and maybe showing some info about the last modification seen; with a "filter now" button too.  Essentially, as long as the script is running, any changes to the source CSV file will result in the filtered CSV file to be updated, with sub-second latency.

For Windows, the issue is detecting the file-close events.  You see, if the source CSV file is written to in-place, then each write to the file will affect the timestamp, and your script may read it before it has been fully written.  You can work around this somewhat by adding a couple of seconds of latency, which is not needed on other operating systems.  (Even fswatch uses ReadDirectoryChangesW in Windows, and that does not have a reliable close_write detection, unlike Linux/Android/OSX/BSD interfaces used.)  One can use ReadDirectoryChangesW via the pywin32 Python module, so for the few good programs that actually create a temporary file for the new contents, and only when completed rename/link/move it over the existing file, the extra latency is not needed.

Personally, I don't use Windows nor have Windows installed even on a virtual machine, so I cannot help you there any further.

Another approach would be to use an actual database, link the LibreOffice sheet to the database, and have the database read (or a script triggering data updates) the source CSV file when changed.  I prefer to work with files myself, because then I can more easily verify and transfer the whole thing, not depending on an external server.

[eTobey]

Writing a script for just so basic things every day...   

That's exactly where scripts are for, doing basic things all day that you don't want to do manually...

I spend many bucks to have a tool to give me some basic ouput like "ID, databytes, ackbit". I guess i have to spend even more bucks? Why are so often simple basic things not implemented/working? I want to write on my firmware, and not scripts. And thats not all: i wrote a script now, but still i have to do stuff by hand...

This is my script, which converts a rather usless (in my case) list (why do the columns of each row even have no relation at all?):

Code: [Select]

import os
import glob
import time
import re

# Define the pattern and the timeframe (in seconds)
pattern = 'decoder--*.csv'  # Change to your desired pattern
timeframe = 1   # 1 hour in seconds

output_file_path = 'currentFrames.csv'

# Get the absolute path of the current script
current_script_path = os.path.abspath(__file__)

# Get the base path (directory) of the current script
basepath = os.path.dirname(current_script_path)

print("Script running")

global newestFile

while 1:
	# Get the current time
	current_time = time.time()

	# Find files matching the pattern
	files = glob.glob(pattern)
	
	newestFile = None

	# Filter for recently created file
	for file in files:
		try:
			# Get the creation time (on Windows) or last modification time (on Unix-like systems)
			creation_time = os.path.getctime(f"{basepath}\\\\{file}")  # Use os.path.getmtime(file) for Unix-like systems
		except:
			print(f"EXEPT: {file} ")

		if current_time - creation_time <= timeframe:
			newestFile = open(file, 'r')
			
	if not newestFile:
		continue
		
	#wait until the file has been written
	time.sleep(3)

	# Open the output file for writing
	output_file = open(output_file_path, 'w')
	global lines
	
	startPattern = r'[0-9]+,(.*),Start of frame'
	idPattern = r'^.+Identifier: [0-9]+ \((0x[0-9a-f]+)\)'
	lengthPattern = r'.+Data length code: ([0-9]+)'
	dataBytePattern = r'^.+Data byte [0-9]+: 0x([0-9a-f]+)'
	ackPattern = r'.*ACK slot: (.+)$'

	# Open the recent file
	
	lines = newestFile.readlines()
	
	global startTime
	global dataBytes
	
	
	
	for line in lines:
		timeMatch = re.search(startPattern, line)
		
		if timeMatch:
			startTime = float(timeMatch.group(1))/1000/1000
			sTimeString = (f"{startTime:.3f};").replace('.',',')
			output_file.write(sTimeString)
			dataBytes = None
			
		idMatch = re.search(idPattern, line)
		if idMatch:
			id = int(idMatch.group(1),16)
			output_file.write(f"{id};")
			
		lengthMatch = re.search(lengthPattern, line)
		if lengthMatch:
			length = lengthMatch.group(1)
			#print("")
			
		dataMatch = re.search(dataBytePattern, line)
		if dataMatch:
			dataByte = int(dataMatch.group(1),16)
			output_file.write(f"{dataByte};")
			
		ackMatch = re.search(ackPattern, line)
		if ackMatch:
			output_file.write(f"{ackMatch.group(1)}\n")
			
		
	output_file.close()

The source file looked like this:

Code: [Select]

Id,Time[ns],CAN-FD: Fields
1,3756800.00,Start of frame
2,3758800.00,Identifier: 36 (0x24)
3,3782800.00,Remote transmission request: data frame
4,3784800.00,Identifier extension bit: standard frame
5,3786800.00,Flexible data format: 0
6,3790800.00,Data length code: 8
7,3798800.00,Data byte 0: 0x11
8,3816800.00,Data byte 1: 0xfd
9,3834800.00,Data byte 2: 0xbe
10,3852800.00,Data byte 3: 0xb4
11,3868800.00,Data byte 4: 0x6a
12,3884800.00,Data byte 5: 0x33
13,3900800.00,Data byte 6: 0x95
14,3916800.00,Data byte 7: 0x5d
15,3932800.00,CRC-15 sequence: 0x0c70
16,3962800.00,CRC delimiter: 1
17,3965400.00,ACK slot: ACK
18,3967400.00,ACK delimiter: 1
19,3969400.00,End of frame

This is what i wanted:

[paulca]

Filter:
I have many "id's" that also are "34" and "36". I want to filter them, meaning, they should not appear in the list anymore. Only the other "id's" should be in that list.

Realtime:
It should show the list from the data link (the data that is in a csv file and which that program should show), as it currently is, with a maximum delay of like 2 seconds.

Its okay, it's still a batch.  Realtime would involve having the file open for read and write and reading rows while something else is inserting them.  If you have completed and closed file, it's a batch and 100 times simpler.

Depending on the particulars of the CSV file, such as "Does it contain any escaped comma?"  I would either just pipe it through egrep or in the case of potential "full" implementation of CSV with escaping, quoted columns etc...  a Python CSV reader.

In the case of egrep a reg-exp should be fairly easy to construct which matches content in a set numbered column.  This can be inverted of course.

With a python CSV reader you will basically get a row iterator, you can do your filter in if statements, or you can usually wrap it in a collection operation like .filter(Predicate) and one line it.

It sounds very simile to a "daily task" to me.  Examples would be to find all log statements from the last test run that mention a certain string.  If you have skills in bash, python, perl and know how to operate a commandline, you would typically not even save these, but just execute them as "onliners", knowing they will be in your command history for a few weeks anyway if needed again.

[Nominal Animal]

All DSView/PulseView protocol decoders have this format, because of the protocol decoder API (see HOWTO for details).  The exact columns vary –– not all use the second column for time in nanoseconds, for example ––, but they all tend to produce one row per field, instead of one row per record.

Parsing a CSV file is trivial in Python:

    import csv
    with open(filename, 'r', newline='') as in_stream:
        input_csv = csv.reader(in_stream, dialect='excel')
        for fields in input_csv:
            # you have len(fields) elements in fields in current record

The trick is to construct a state object or dictionary, filling in the members from different records, and writing the state out (if filtering says it is to be kept) in CSV format.

For nicely formatted output, consider

    with open(filename, 'x', newline='') as out_stream:
        output_csv = csv.writer(out_stream, dialect='excel', quoting=csv.QUOTE_NONNUMERIC)

followed by a

        output_csv.writerow(fields)

for each record, where fields is the list (array) of fields in this record.