Plex Server Administration With Excel

Import Your Plex Database into An Excel Worksheet

What you need to get started:

1. Plex Media Server With Admin Permissions
2. MS Excel (included with Office 365) installed on your Plex Media Server's Local Machine
3. Beginner knowledge on how to place a button and checkbox on an Exel Worksheet and link them to a Macro

Step-By-Step Instructions:

1. Find the full path to your Plex Database .  See Where Is My Plex Media Server Data Located to find your server file locations. You can also use the Advanced Setting in Scheduled Tasks to get a the location of your backups. My Database is one directory in front of my backups, located here: "V:\Plex\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"
2. Create a Button (btnImportPlexData) that Fires An Excel Macro (btnImportPlexData_Click) when clicked (see Assign a Macro to a Form or Control Button).  Place your button on row one of your Worksheet. Imported Plex data will be placed on row 2 and beyond.
3. Copy and Paste the below code to your button's macro, update the REQUIRED varaibles.
4. Click the button you created in the above step 2 to import your Plex data to your Excel Worksheet.
5. Once imported, you can then search for Movies using advanced Excel filters that will allow you to view your media in a whole new way.

Option Explicit

Enum

   ColNb: MovieNm = 1: Genre: Bitrate: MovieWidth: AudioChannels: Size: Rating: AudienceRating: FileLocation: ReleaseDate: AddDate: Actors: TagLine: Summary: LibraryID End Enum

Enum RowNb: HdrRow = 2: FirstDataRow = 3: End Enum
Dim m_PrevRowSel As Range

Sub ImportPlexDB_Click()

'--- FOR FUTURE REFERENCE, THIS MACRO WAS COPIED FROM SITE: www.automationwithexcel.com ---

'>>> REQUIRED - UPDATE THE BELOW VARIABLE  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' DbFullFileNm : ENTER THE FULL PATH AND FILENAME TO YOUR PLEX DATABASE  
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim DbFullFileNm As String: DbFullFileNm = "V:\Plex\Plex Media Server\Plug-in Support\Databases\com.plexapp.plugins.library.db"

'>>> REQUIRED - UPDATE THE BELOW VARIABLE  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' PlexLibraryNm : USE YOUR PLEX MEDIA SERVER LIBRARY NAME (the Media Library Name that you created and see in Plex)  
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim PlexLibraryNm As String: PlexLibraryNm = "'Movies - All'"

'--- CREATE DATABASE ADODB CONNECTION AND RECORDSET OBJECTS ---
Dim DbConn As Object: Set DbConn = CreateObject("ADODB.Connection")
Dim DbRs As Object: Set DbRs = CreateObject("ADODB.Recordset")
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim SQLStr As String, idx As Long, ColNm As Variant
Application.ScreenUpdating = False

'--- OPEN THE DATABASE CONNECTION ---
DbConn.Open "DRIVER=SQLite3 ODBC Driver;Database=" & DbFullFileNm & "; LongNames=0; Timeout=1000; NoTXN=0; SyncPragma=NORMAL; StepAPI=0;"

'--- CLEAR DATA AND FORMATTING ON THE ENTIRE WORKSHEET ---
If Ws.FilterMode Then Ws.ShowAllData
Ws.Cells.ClearContents: Ws.Cells.ClearFormats: Ws.Cells.FormatConditions.Delete: Ws.Cells.Borders.ColorIndex = xlNone

'--- CREATE THE SQL STATEMENT ---
SQLStr = _"SELECT metadata_items.title || ' (' || metadata_items.year || ')' AS [MovieNm]," & _
"metadata_items.tags_genre AS [Genre], " & _
"media_items.bitrate AS [Bitrate], " & _
"media_items.width AS [Movie Width], " & _
"media_items.audio_channels AS [Audio Channels], " & _
"media_items.size AS [Size], " & _
"metadata_items.content_rating AS [Rating], " & _
"metadata_items.audience_rating AS [Audience Rating], " & _
"media_parts.file AS [File Location], " & _
"date(metadata_items.originally_available_at, 'unixepoch') AS [Release Date], " & _
"datetime(metadata_items.added_at, 'unixepoch') AS [Add Date], " & _
"metadata_items.tags_star AS [Actors], " & _
"metadata_items.tagline AS [TagLine], " & _
"substr(metadata_items.summary,1,500) AS [Summary], " & _
"metadata_items.library_section_id AS [LibID] "

SQLStr = SQLStr & _
"FROM media_items " & _
"INNER Join metadata_items ON media_items.metadata_item_id = metadata_items.id " & _
"INNER Join media_parts ON media_parts.media_item_id = media_items.id " & _
"INNER Join section_locations ON media_items.section_location_id = section_locations.id " & _
"INNER Join library_sections ON library_sections.id = section_locations.library_section_id " & _
"WHERE Library_sections.name = " & PlexLibraryNm & _
"ORDER BY MovieNm, Bitrate DESC, media_items.width DESC, media_items.audio_channels DESC, Size ASC;"

'--- OPEN RECORDSET AND RUN THE QUERY ---
DbRs.Open SQLStr, DbConn

'--- CREATE THE SHEET HEADER ---
For idx = 0 To DbRs.Fields.Count - 1
Ws.Cells(RowNb.HdrRow, idx + 1).Value = DbRs.Fields(idx).Name
Next idx

'--- OUTPUT TO WORKSHEET ---
Ws.Range("A" & RowNb.FirstDataRow).CopyFromRecordset DbRs

'--- FREE RESOURCES ---
DbRs.Close: Set DbRs = Nothing: Set DbConn = Nothing

'--- FORMAT THE SHEET ---
With Ws.UsedRange.Columns

.Borders.Color = RGB(127, 127, 127)
.ColumnWidth = 10
Ws.Columns(ColNb.MovieNm).ColumnWidth = 15
Ws.Columns(ColNb.Genre).ColumnWidth = 16
Ws.Columns(ColNb.Bitrate).NumberFormat = "#,##0"
Ws.Columns(ColNb.Size).NumberFormat = "#,##0"
Ws.Columns(ColNb.FileLocation).ColumnWidth = 20
Ws.Columns(ColNb.Actors).ColumnWidth = 15
Ws.Columns(ColNb.TagLine).ColumnWidth = 15
Ws.Columns(ColNb.Summary).ColumnWidth = 40
.WrapText = True: .VerticalAlignment = xlTop
Ws.UsedRange.Rows.AutoFit: .AutoFit
Ws.Rows(RowNb.HdrRow).AutoFilter

End With

Application.ScreenUpdating = True

End Sub

Find Duplicate Movie Titles

Step-By-Step Instructions:

1. Place a checkbox control (cbFindDuplicateMovies) on row one of your worksheet that fires an Excel Macro (cbFindDuplicateMovies_Click) when checked or unchecked. Place the checkbox on row one of your sheet. 
2. Copy and Paste the below code to your checkbox's macro
3. Check the checkbox to show just your duplicate titles highlighted in red or uncheck it to show all movies.

Sub cbFindDuplicates_Click()

'--- FOR FUTURE REFERENCE, THIS MACRO WAS COPIED FROM SITE: www.automationwithexcel.com ---
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim TitleCol As Range: Set TitleCol = Ws.Range(Ws.Cells(RowNb.HdrRow + 1, 1), _
Ws.Cells(Ws.UsedRange.Rows.Count, 1))

Application.ScreenUpdating = False

'--- CLEAR FORMAT AND FILTER CONDITIONS ---
TitleCol.Cells.FormatConditions.Delete
If Ws.FilterMode Then Ws.ShowAllData

If Ws.CheckBoxes("cbShowDuplicates").Value = 1 Then

'--- HIGHLIGHT DUPLICATE ROWS ---
With TitleCol.FormatConditions.AddUniqueValues

.DupeUnique = xlDuplicate
.Interior.Color = RGB(255, 199, 206): .Font.Color = vbRed: .Font.Bold = True

End With

'--- USE A FILTER TO SHOW ONLY DUPLICATE/HIGHLIGHTED ROWS ---
Ws.Rows(RowNb.HdrRow).AutoFilter field:=ColNb.MovieNm, Criteria1:=RGB(255, 199, 206), _
Operator:=xlFilterCellColor

'--- SORT MOVIE TITLES BY QUALITY PREFERENCE, BEST QUALITY FIRST ---
With Ws.AutoFilter.Sort

.SortFields.Clear
.SortFields.Add Key:=Ws.Range(Ws.Cells(RowNb.HdrRow, ColNb.MovieNm), _
Ws.Cells(Ws.UsedRange.Rows.Count, ColNb.MovieNm)), Order:=xlAscending
.SortFields.Add Key:=Ws.Range(Ws.Cells(RowNb.HdrRow, ColNb.Bitrate), _
Ws.Cells(Ws.UsedRange.Rows.Count, ColNb.Bitrate)), Order:=xlDescending
.SortFields.Add Key:=Ws.Range(Ws.Cells(RowNb.HdrRow, ColNb.MovieWidth), _
Ws.Cells(Ws.UsedRange.Rows.Count, ColNb.MovieWidth)), Order:=xlDescending
.SortFields.Add Key:=Ws.Range(Ws.Cells(RowNb.HdrRow, ColNb.AudioChannels), _
Ws.Cells(Ws.UsedRange.Rows.Count, ColNb.AudioChannels)), Order:=xlDescending
.SortFields.Add Key:=Ws.Range(Ws.Cells(RowNb.HdrRow, ColNb.Size), _
Ws.Cells(Ws.UsedRange.Rows.Count, ColNb.Size)), Order:=xlAscending
.Header = xlYes
.Apply

End With

End If

ActiveWindow.ScrollRow = RowNb.HdrRow
Application.ScreenUpdating = True

End Sub

Delete Duplicate Movie Files 

Step-By-Step Instructions:

1. Place a button control (btnConfirmDeleteDuplicates) on row one of your worksheet that fires an Excel Macro (btnConfirmDeleteDuplicates_Click) when clicked. Place the buttonon row one of your sheet. 
2. Copy and Paste the below code to your button's macro (btnConfirmDeleteDuplicates_Click).
3. Next copy the below supporting functions DeleteMovie and SkipToNextMovie to your code.
4. Click the button to iterate through your duplicate movies, confirming their quality and if they are to be kept or deleted.
5. Update all variable marked REQUIRED.