Export to Excel - DVDpedia

[AFG034]

Is there a way I can export my movie collection with all the data and fields into Excel, than edit it and import it back in when I'm done?

I tried searching for this but didn't find much.

[AFG034]

I can also use Numbers, so which ever is easier is fine.

[Nora]

When you export in tab or comma delimited (CSV) format, the program will use whatever columns you have displayed in the table view so if you want to export all fields you'd have to display all columns first. (The quickest way to do that is via the View menu > View Options.) The actual export command can be found under File menu > Export collection... > Text.

To re-import the data, use the Import collection command found under that same File menu.

[Conor]

Bringing back the data will bring it in as new entries from the import. Depending on what you need to do with the data, you might be better off using the SQL backend that is used as a database. It's pretty powerful and can do a lot of what you might want to do in Excel or Numbers.

[AFG034]

Thanks Nora. I tried it and like Conor said, it added everything as new entries.

Connor, I'm pretty much looking for the easiest way for me to go into my movies and update the information (actors, directors, producers etc) since many of my movies didn't transfer over the data. How would I do the SQL? If there something I can read?

Also, since I moved my data folder to Dropbox to share on both my cpu and laptop, will I still be able to use SQL?

[Conor]

Dropbox is not an issue, the underlying the database stays the same even while on Dropbox.

Comes down to three options. The first one and easiest, is to add data to the new import. If you select all the entries that you have now imported and use the "Movie -> Update from -> Doghouse" it will go online and get all the missing info such as covers, that your original entries had. You can then replace the old entries with the new imported entries and it will have the data you imported. The disadvantage of this entries is that if you have any data that was relationships such as links to movie files or to other entries in DVDpedia, borrowed history, seen history, these will be lost. As this was not data that could originally be represented in the text export that went to Excel.

Option two is to export the data from Excel as SQL statements that will update the database directly. This can seem daunting but not as crazy as it seems. Since you already have the data in Excel updated, what you want is to massage the export so that it looks like this:

Code: Select all

UPDATE zentry SET zdirector = "My Director" WHERE ztitle = "My Title";

This requires that you have a unique column in Excel that will let you find the movie, such as the title if it has not changed or UPC Code, collectionID, IMDb number, etc.

For example to update the directors you would export two columns, the director and the title. And then in a text editor do a find and replace, so that you end up with the SQL command above.

Find \n replace with ;\nUPDATE zentry SET zdirector = "
Find , (or whatever the divider is in the export) replace with " WHERE ztitle = "

The first and last line need to be fixed as the find and replace would leave the first one without the beginning and the last one without the closing semi colon. To run the file with all the update commands against the database you would copy and paste the following command into the program called Terminal in your /Applications/Utilities folder.

Code: Select all

sqlite3 /Users/fa/Library/Application\ Support/DVDpedia/Database.dvdpd < /Users/me/Desktop/MyFileWithCommand.txt

On the above you have to update the path from the default /Users/fa/Library/Application\ Support/DVDpedia/Database.dvdpd to your Dropbox location. You can type "sqlite3 " and then drag and drop the file Database.dvdpd to add the path, then " < " and drag and drop the text file with all the commands.

It goes without saying that before any of that, make a copy of Database.dvdpd file, as that contains all your info. You can then repeat the above for actors and producers.

Code: Select all

UPDATE zentry SET zstarring = "Actors" WHERE ztitle = "My Title";
UPDATE zentry SET zproducer = "My Producer" WHERE ztitle = "My Title";

if you are feeling bold about your find and replace abilities or Excel export abilities you could even export all the info into a single line that would be:

Code: Select all

UPDATE zentry SET zdirector = "My Director", zstarring = "Actors", zproducer = "My Producer" WHERE ztitle = "My Title";

The third option is much like the second option in that you can ask for a textual representation of the database in a single text file. It would be created with the following command in Terminal:

Code: Select all

sqlite3 /Users/fa/Library/Application\ Support/DVDpedia/Database.dvdpd .dump > /Users/me/Desktop/MyInfo.sql

You can then manipulate it in any text editor even import the zentry bit into Excel and export it again in the same format. So that you end up with the same file format, but with all the new values. This one does require some serious text editing skills, but all depends on how your initial data you want to import is set up. You can then wipe your database and reimport it with the changes with the following two commands

Code: Select all

rm sqlite3 /Users/fa/Library/Application\ Support/DVDpedia/Database.dvdpd
sqlite3 /Users/fa/Library/Application\ Support/DVDpedia/Database.dvdpd < /Users/me/Desktop/MyInfo.sql

As usual the path needs to be updated to the Dropbox version. I would likely choose the second option, although scary at first, you have the data in Excel already and you should be able to export those commands as they are only repetitive info to tell the database what to do. You can even try a few movies commands in the text file to make sure it's working the way you hoped. All the field names in the database are what you would expect but with a "z" at the beginning.

[AFG034]

Wow, thank you for the response.

There's definitely a lot of information to take in and everything seems scary haha. I'll be trying a couple of movies at first to get the process down.

In option 1, you're basically saying do the work in excel and import it in so that I'll have two libraries. Use the feature you mentioned to get the covers than delete the old library?

[Conor]

In option 1, you're basically saying do the work in excel and import it in so that I'll have two libraries. Use the feature you mentioned to get the covers than delete the old library?

Yes. But keep in mind you lose any data that did not go into Excel, such as relationships to other movies and last seen history type data.

[AFG034]

I'm okay with that, as I didn't use the watch history all that much.

I really appreciate your help.

[AFG034]

Hey Connor, just so you know, I did a little test run of the first option but cover update didn't work. Any reason why it wouldn't?

I am able to copy and paste the cover from the old library, so there's still an option.

[Conor]

Was this using the "Movie -> update from" command? That one should be able to bring in the covers. The "Download covers from ->" contextual menu command is a lot stricter as it needs an identifying ID such as the dog tag or Amazon number, so that is can be 100% of the cover match.

The export as CSV does not include the path to the cover image. But if you build your own text template(CDpedia Example) (DVDpedia example) you can include the cover path as well. The link is for CDpedia but the process is the same, it's to build your own export that will include all the fields as well as the cover path.

With that info in Excel, when importing back there is a "Cover Image URL" import mapping that will then copy the existing cover.

[AFG034]

Yes that was using the update feature.

I'll try the cover link issue. Thanks.

[AFG034]

Was this using the "Movie -> update from" command? That one should be able to bring in the covers. The "Download covers from ->" contextual menu command is a lot stricter as it needs an identifying ID such as the dog tag or Amazon number, so that is can be 100% of the cover match.

The export as CSV does not include the path to the cover image. But if you build your own text template(CDpedia Example) (DVDpedia example) you can include the cover path as well. The link is for CDpedia but the process is the same, it's to build your own export that will include all the fields as well as the cover path.

With that info in Excel, when importing back there is a "Cover Image URL" import mapping that will then copy the existing cover.

Connor - Would I just have to copy that code (edit my info in of course) than put this in a new field on the excel spreadsheet? Would each one be different for each movie?

[Conor]

Yes. Each movie has a unique ID, that is also the cover image name. You can use the whole path as exported, or just use the unique ID and then create the path from the id. As all the covers are in the same folder with the "ID.jpg" naming convention. But taking the full path as exported and making it a column in Excel is likely easier.

[AFG034]

When I did the export, the image path did not come over. Do you know which category I would need to export?