Show Posts
1
Development / PVDB to Excel export (or tab seperated CSV)
« on: December 21, 2015, 01:31:47 am »
I thought I'd share a nice table export of PVDB to Excel with the FlameRobin SQL Tool (http://www.flamerobin.org/).
Windows download from http://sourceforge.net/projects/flamerobin/files/flamerobin-snapshots/0.9.x/.
It will output all your movies and series (with filenames and paths) in a somehow ordered manner :-)
Requirements: PVD database must be connectable via a firebird server (> v2.0).
- Save the above code to something like "Movie and Series Export.sql" with a text editor.
- Load the file (some commentaries are included) into FlameRobin after having
connected to your "Personal Video Database" (via firebird server).
- After execution of the statements make all records visible in FlameRobin (not only the first 300).
- Now select ALL records from the edit menu and copy them to clipboard (might take a while)
- Open an Excel sheet, mark 1st column / 1st row and press CTRL+V to insert your exported records.
- If you don't need UNC paths, comment these 2 lines:
* from the SELECT statement: -- , (replace (a."path", 'M:\MYMOVIES', '\\movieserver\share\MYMOVIES\')) AS "unc"
* from the GROUP BY statement: -- , "unc"
- I used this basically for learning, so the SQL statements might be improved ;-)
- This might also serve as a basic SQL export script - change it to fit your requirements.
Thanks for your attention and have fun...
CG
Windows download from http://sourceforge.net/projects/flamerobin/files/flamerobin-snapshots/0.9.x/.
It will output all your movies and series (with filenames and paths) in a somehow ordered manner :-)
Requirements: PVD database must be connectable via a firebird server (> v2.0).
Code: [Select]
-- ## NOTE: Comma seperator at the BEGINNING of a line
-- ## allows for easy disabling certain lines!
SELECT
IIF (c."title" IS NULL, a."title", c."title") AS "title"
, IIF (c."origtitle" IS NULL, a."origtitle", c."origtitle") AS "otitle"
, IIF (b."season" IS NULL, '', b."season") AS "S"
, IIF (b."epnum" IS NULL, '', b."epnum") AS "E"
, IIF (c."title" IS NULL, '', a."title") AS "etitle"
, IIF (c."origtitle" IS NULL, '', a."origtitle") AS "eotitle"
, a."year" AS "year"
-- ## list directors in column "director(s)", if [null] set to '' (empty string)
, LIST (DISTINCT IIF (e."name" IS NOT NULL, e."name", ''), ', ') AS "director(s)"
-- ## list languages in column "lang(s)", if [null] set ''
, LIST (DISTINCT IIF (g."value" IS NOT NULL, SUBSTRING(LOWER(g."value") FROM 1 FOR 3), ''), '|') AS "lang(s)"
, a."rdate" AS "released", a."imdbrating" AS "IMDB", a."rating" AS "rate", a."orating" AS "orate"
, a."url" AS "url", a."path" AS "path"
-- ## put your replacement string for local drive name to a unc resolved one here
-- ## adapt this line: i.e. 'M:\ALL_MY_MOVIES\' to '\\server\share\ALL_MY_MOVIES\'
, (replace (a."path", 'M:\MYMOVIES', '\\movieserver\share\MYMOVIES\')) AS "unc"
, a."mid" AS "a.mid", a."epid" AS "a.epid", a."series", b."mid" AS "b.mid", b."epid" AS "b.epid"
-- ## for debugging or more infos
-- , LIST (DISTINCT IIF (d."pid" IS NOT NULL, d."pid", ''), ', ') AS "d.pid(s)"
-- , LIST (DISTINCT IIF (f."lid" IS NOT NULL, f."lid", ''), ', ') AS "f.lid(s)"
FROM MOVIES a
LEFT JOIN EPISODES b ON a."epid" = b."epid"
LEFT JOIN MOVIES c ON b."mid" = c."mid"
LEFT JOIN CAREER d ON a."mid" = d."mid"
LEFT JOIN PEOPLE e ON d."pid" = e."pid"
LEFT JOIN AUDIO f ON a."mid" = f."mid"
LEFT JOIN LANGUAGES g ON f."lid" = g."lid"
WHERE
d."ctype" = 1 -- ## only directors [=1]
AND (d."pos" < 6 OR d."pos" IS NULL) -- ## limit the number of directors, default=6 [starts at 0]
AND (f."pos" < 5 OR f."pos" IS NULL) -- ## limit the number of languages, default=5 [starts at 0]
-- AND a."series" = 0 -- ## if set [0], do not display series container
-- ## user defined
-- AND (a."title" LIKE '%James Bond%' OR c."title" LIKE '%James Bond%')
-- AND (a."title" LIKE '%Doctor Who (2005)%' OR c."title" LIKE '%Doctor Who (2005)%')
GROUP BY
"title", "otitle", b."season", b."epnum", "etitle", "eotitle", a."year"
, a."rdate", a."imdbrating", a."rating", a."orating"
, "url", "path"
, "unc"
, a."mid", a."epid", "series", b."mid", b."epid"
- Save the above code to something like "Movie and Series Export.sql" with a text editor.
- Load the file (some commentaries are included) into FlameRobin after having
connected to your "Personal Video Database" (via firebird server).
- After execution of the statements make all records visible in FlameRobin (not only the first 300).
- Now select ALL records from the edit menu and copy them to clipboard (might take a while)
- Open an Excel sheet, mark 1st column / 1st row and press CTRL+V to insert your exported records.
- If you don't need UNC paths, comment these 2 lines:
* from the SELECT statement: -- , (replace (a."path", 'M:\MYMOVIES', '\\movieserver\share\MYMOVIES\')) AS "unc"
* from the GROUP BY statement: -- , "unc"
- I used this basically for learning, so the SQL statements might be improved ;-)
- This might also serve as a basic SQL export script - change it to fit your requirements.
Thanks for your attention and have fun...
CG