Author Topic: PVDB to Excel export (or tab seperated CSV)  (Read 1319 times)

0 Members and 1 Guest are viewing this topic.

Offline Chris64

  • Member
  • *
  • Posts: 21
    • View Profile
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).

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


mgpw4me

  • Guest
Re: PVDB to Excel export (or tab seperated CSV)
« Reply #1 on: December 23, 2015, 03:39:08 am »
Verrry interesting.

Running PVD in -debug mode there is an SQL option on the HELP menu.  Unfortunately, the Sql dies on line 13 in PVD.  No big deal.

I'm looking at converting my database to a different stand-alone (SqlLite) and displaying it via a minimal server (maybe Mongoose) with PHP...so I can customize the display via HTML / CSS / JAVASCRIPT, but still pull data from the database, not just display static pages.  In the process, I'll want to do some database reformatting.  Sql is one of those languages where I am aware of what can be done, but can't write code without a template / cheatsheet, so verrry interesting.

Thanks for contributing.

 

anything