File manager with spreadsheet-like functionality for OS X
December 16, 2021 10:57 AM   Subscribe

I’ve been hoping for some time to see a file manager that allowed for sorting and filtering by extended metadata attributes. For example, a music folder with MP3s might have columns for Artist, Genre, Year, etc. and allow the user to filter down to a certain genre over a certain time period, then sort the results. Sort of like spreadsheet functionality for files. I know this is kind of a niche feature, but it would be awesome for a lot of use cases (academic papers, e-books, sysadmin scripts, and so on).
posted by dcotter to Technology (7 answers total) 1 user marked this as a favorite
 
Best answer: Not OS X, obviously, but Windows Explorer lets you do this.

Taking your example query, this is my music folder searched recursively for music tracks in the "rock" genre released between 1970 and 1980, then sorted by year of release. The metadata is stored in the files' ID3 tags. The query is: kind:music genre:rock year:>=1970 year:<=1980
posted by Klipspringer at 11:52 AM on December 16, 2021 [1 favorite]


Best answer: Apple Spotlight may get you there, though I'm not too familiar with GUI tools that will do what you need for music, outside of Smart Playlists in the Music app (formerly called iTunes).

Instead, if you have some command-line chops, you could look at using the mdls and mdfind tools to investigate and filter on metadata keys and values for files stored on OS X filesystems.

For example:
% mdls 01\ -\ The\ Lamb\ Lies\ Down\ on\ Broadway\ \(New\ Stereo\ Mix\).mp3   
kMDItemAlbum                           = "The Lamb Lies Down on Broadway (2007 Stereo Mix)"
kMDItemAlternateNames                  = (
    "01 - The Lamb Lies Down on Broadway (New Stereo Mix).mp3"
)
kMDItemAudioBitRate                    = 278665
kMDItemAudioChannelCount               = 2
kMDItemAudioSampleRate                 = 44100
kMDItemAudioTrackNumber                = 1
kMDItemAuthors                         = (
    Genesis
)
kMDItemComment                         = "Amazon.com Song ID: 208356170"
kMDItemContentCreationDate             = 2020-12-18 14:54:36 +0000
kMDItemContentCreationDate_Ranking     = 2020-12-18 00:00:00 +0000
kMDItemContentModificationDate         = 2020-12-18 14:54:46 +0000
kMDItemContentModificationDate_Ranking = 2020-12-18 00:00:00 +0000
kMDItemContentType                     = "public.mp3"
kMDItemContentTypeTree                 = (
    "public.mp3",
    "public.audio",
    "public.audiovisual-content",
    "public.data",
    "public.item",
    "public.content"
)
kMDItemCopyright                       = "© 2008, 1974 Anthony Banks Ltd., Philip Collins Ltd., Peter Gabriel Ltd., Stephen Hackett Ltd., Michael Rutherford Ltd., under exclusive license to Atlantic Recording Corporation."
kMDItemDateAdded                       = 2020-12-18 14:54:46 +0000
kMDItemDateAdded_Ranking               = 2020-12-18 00:00:00 +0000
kMDItemDisplayName                     = "The Lamb Lies Down on Broadway (New Stereo Mix)"
kMDItemDocumentIdentifier              = 0
kMDItemDurationSeconds                 = 294.4
kMDItemFSContentChangeDate             = 2020-12-18 14:54:46 +0000
kMDItemFSCreationDate                  = 2020-12-18 14:54:36 +0000
kMDItemFSCreatorCode                   = ""
kMDItemFSFinderFlags                   = 0
kMDItemFSHasCustomIcon                 = (null)
kMDItemFSInvisible                     = 0
kMDItemFSIsExtensionHidden             = 0
kMDItemFSIsStationery                  = (null)
kMDItemFSLabel                         = 0
kMDItemFSName                          = "01 - The Lamb Lies Down on Broadway (New Stereo Mix).mp3"
kMDItemFSNodeCount                     = (null)
kMDItemFSOwnerGroupID                  = 20
kMDItemFSOwnerUserID                   = 503
kMDItemFSSize                          = 10310319
kMDItemFSTypeCode                      = ""
kMDItemInterestingDate_Ranking         = 2020-12-18 00:00:00 +0000
kMDItemKind                            = "MP3 audio"
kMDItemLogicalSize                     = 10310319
kMDItemMediaTypes                      = (
    Sound
)
kMDItemMusicalGenre                    = "Rock"
kMDItemPhysicalSize                    = 10530816
kMDItemRecordingYear                   = 1974
kMDItemTitle                           = "The Lamb Lies Down on Broadway (New Stereo Mix)"
kMDItemTotalBitRate                    = 278665
All those kMDItem* keys have values you can use to filter on.

Every time you add a file to your filesystem, or modify it, OS X Spotlight will update its metadata database in the background, so that you can search your filesystem for key-value pairings of interest.

For instance, to look for songs recorded in 2001:
% mdfind "kMDItemRecordingYear = 2001" | head
/Users/brains/Music/Music/Media.localized/Deerhoof/Half Bird/13 The Forty Fours.mp3
/Users/brains/Music/Music/Media.localized/Deerhoof/Half Bird/12 Rat Attack.mp3
/Users/brains/Music/Music/Media.localized/Deerhoof/Half Bird/11 Xmas Tree.mp3
/Users/brains/Music/Music/Media.localized/Deerhoof/Half Bird/10 Littleness.mp3
/Users/brains/Music/Music/Media.localized/Deerhoof/Half Bird/09 Carriage.mp3
...
You can do boolean operations on the command line, e.g.:
% mdfind "kMDItemRecordingYear = 2001 AND kMDItemMusicalGenre = Rock" | head
...
The examples above are for music files, but you could investigate other types of files for metadata keys specific to other types of documents, like journal articles, scripts, ebooks, etc.

Some more examples of Spotlight and command-line queries are available here: https://superuser.com/questions/51122/how-to-search-with-spotlight-more-effectively/412090
posted by They sucked his brains out! at 2:11 PM on December 16, 2021 [3 favorites]


Look at Path Finder. I’m not at home with my Mac, so I can’t look, but it’s an orthodox file manager that may give you additional sorting options. Costs money, though.
posted by lhauser at 3:46 PM on December 16, 2021


macOS lets you define searches from Spotlight into Smart Folders, but it's not ad-hoc like you asked.
posted by k3ninho at 2:50 AM on December 17, 2021


Document management has components of organization and of retrieval.

Organization - Path Finder and Forklift are document managers that allow you to sort by file metadata (date, size) and, crucially, to filter by file name. Like Windows Explorer, they display file metadata but do not allow the user to define their own metadata.

Virtually all of the non-Apple Music apps (e.g., Swinsian, Clementine) will separate out song metadata for MP3 files, but only for MP3 and other music files.

Retrieval - No matter how your files are organized, the premier tool for fast retrieval of files using file names is HoudahSpot. It is built atop the Spotlight index, so any changes made to file names, etc., will be immediately retrievable.

For features that provide most of what you seek, though without a spreadsheet interface, take a look at DevonThink Pro.
posted by yclipse at 3:54 PM on December 17, 2021


Response by poster: The suggestion to use mdls and mdfind was helpful, thanks (as were the other answers, each in its own way). But what I was hoping for was a) a GUI file explorer that b) allows for sorting & filtering on c) customizable metadata attributes that d) runs on OS X. So as useful as the other answers may prove to be, I'm still looking...
posted by dcotter at 9:27 AM on December 18, 2021


Maybe take another look at HoudahSpot, as I think it ticks those boxes to some degree.
posted by They sucked his brains out! at 9:33 AM on December 20, 2021


« Older USA medical primary and secondary insurance...   |   How important are looks to a relationship? Newer »

You are not logged in, either login or create an account to post comments