Does it exist some script that allows me to import text files into Numbers and automatically process them? That is, apply filters, conditional formatting etc.
In Numbers AppleScript does not have direct access to Filters, Conditional Highlighting, or Custom Formats. You would have to try GUI-scripting, probably not worth the effort.
The Numbers user interface is streamlined and efficient. Doing what you need to do manually is probably your best option.
Oddly, pretty much every time there is a thread about Excel, somebody posts something negative about its applescript support being deficient.
That said, you can always prepare and save a blank spreadsheet ahead of time —perhaps as a template, and then insert the data by script in some manner. With my version (5), you can have the conditional formatting enabled so the data automatically formats.
However, filters are more problematic. If the filters are enabled it may affect which data is inserted (at least with the clipboard it does). If the document is saved with the filters unchecked then all the pasted data will appear. In such a case, then you’d only have to UI script the activation of the filters tab and then the filter checkboxes. Not great but perhaps not too onerous.
I am not saving the result, I am just viewing it. However, the filters don’t work without data, do they? The TSV-file contains a list of events. I am only looking at the events for today each day. However, the date field contains three different date stamps, today, happening now and (e.g.,) Tues, Dec 12 and you need to have these values in the column you filter on to be able to check them in the filters.
Hi.
The easiest way to import a TSV file into Numbers is simply to open it:
set tsvFile to (choose file of type "tsv")
tell application "Numbers"
activate
set newDoc to (open tsvFile)
end tell
The following is another approach to open a TSV text file in Numbers. You can also directly change various spreadsheet properties with an AppleScript, and these are detailed in the Numbers dictionary. If a particular item is not supported, you would have to use GUI scripting. The following is a rudimentary example that works as expected on my 2023 Sonoma Mac mini.
set theFile to (choose file) -- a TSV text file
tell application "Numbers"
activate
set theSpreadsheet to make new document with data theFile
tell table 1 of sheet 1 of theSpreadsheet
set width of column "A" to 100
set format of column "A" to text
set font size of column "A" to 20
end tell
end tell
BTW, I looked at the Numbers dictionary, and it appears that filtering is not directly supported. So, this would probably have to be done with GUI scripting, or perhaps Mockman’s suggestion is a better approach.
That’s not the point. By saving a spreadsheet ahead of time you can build your conditional formatting and filters in advance and then, just add data. Think of it as a template.
While I think that the first two filter examples can be done, the last one is too specific.
What happens if you create a spreadsheet with some example data and then create a filter using ‘today’, and then delete the data? The filter shouldn’t go away. If you’re only using ‘today’, then you could leave it enabled when you save the empty spreadsheet.
In case it is unclear, this is what I mean to try.
Example csv data in file named sched.csv:
date,winner,ha,loser,ptsw,ptsl
2023-12-07,Detroit Lions,@,Kansas City Chiefs,21,20
2023-12-10,Atlanta Falcons,Carolina Panthers,24,10
2023-12-12,Cleveland Browns,Cincinnati Bengals,24,3
2023-12-12,Jacksonville Jaguars,@,Indianapolis Colts,31,21
2023-12-10,Washington Commanders,Arizona Cardinals,20,16
2023-12-12,Baltimore Ravens,Houston Texans,25,9
2023-12-12,Tampa Bay Buccaneers,@,Minnesota Vikings,20,17
2023-12-10,New Orleans Saints,Tennessee Titans,16,15
Create matching numbers document
- arrange table to match data
- format columns (eg width, wrap, etc…)
- format date explicitly
(eg if input is ‘2023-12-13’ set to ‘Date & Time’ > 2023-01-05 | None) - add conditional formatting, then test
- add filter (eg ‘date’ column, match rule ‘date is today’), then test
- delete all data
- make upper left cell of table the active cell (ie cell A1)
- ensure that filter inspector is active
- save document (and then duplicate for good measure)
Get data onto clipboard
eg from terminal
% cat sched.csv | pbcopy
Open numbers document and paste into cell A1
Check filters box
Package the appropriate parts into a script and then you can see the filtered data with a single click. And if you like, you can presumably even UI script the checkbox.
stemp.numbers.zip (60.1 KB)
1 Like
It’s unfortunate that lagr hasn’t provided an example of the data. However, it appears that there are two tab-separated fields (or columns)–an event field which contains the event and a date field which contains three date stamps. If that’s correct, it may not be possible to filter on the date field.
If the date stamps have some identifiable delineator, one approach would be to parse the data from the text file and to then create a spreadsheet with one event column and three date columns. But that’s based on an assumption that may not be correct.