Page 1 of 1
re-import
Posted: Mon May 27, 2013 11:38 am
by lankysam
This has probably been answered elsewhere, but I have been unable to track it down, so apologies in advance. I am trying to import data for a new custom field into my existing DVDPedia db. What field am I matching on? Is the Collection ID the primary key? IMDB id? Title? I am pretty certain I have done this before but I can't for the life of me remember what I did.
Thanks, as ever.
Re: re-import
Posted: Tue May 28, 2013 10:50 am
by Conor
A merge of new data from an import into existing entries is not possible via the regular CSV/Text import. You have to run such an import directly on the database in SQL format. This can be a bit complicated but it would be formatting the data in the form of SQL updates and then using Terminal to copy paste the import. It's very flexible and would allow the matching key to be any other column, including Collection ID. It would look something like this:
Code: Select all
sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
UPDATE zEntry SET zCustom1 = 'some data' WHERE zCollectionID = '1';
UPDATE zEntry SET zCustom1 = 'more data' WHERE zCollectionID = '2';
...
.exit
Do make a backup of the Database.dvdpd file before starting. As powerful as the SQL syntax is, it can easily remove all your data.
Re: re-import
Posted: Tue May 28, 2013 2:18 pm
by lankysam
so if i were modifying, say, 100 movies/dvd's i would paste in 100 UPDATE statements keying on (in my case) collectionID and modifying the custom field of my choice?
I gather that the field name for my custom field is the one I have actually assigned to it, e.g. 'TSPDT Rank', not CustomText1? are spaces permitted?
thanks Conor
Re: re-import
Posted: Wed May 29, 2013 3:12 am
by Conor
That would be correct. Although the name of the custom field is the internal one and not the name that you gave it. To find out which custom field 'TSPDT Rank' is, simply hover your mouse over it under the main menu "DVDpedia -> Preferences -> Fields (tab)". The real name will appear in a pop up, so if it's custom 4, the command would be:
Code: Select all
UPDATE zEntry SET zCustom4 = 'more data' WHERE zCollectionID = '1';
If you have a program like TextWrangler regular expressions are perfect for converting a list of Collection ID's and more data into an
update statement. You would use parenthesis to bracket the match of both fields in a regular expression and then the replace with the substitution variables. For example if you had a tab delimited list, it would be:
Find regular expression:
replaced by:
Code: Select all
UPDATE zEntry SET zCustom4 = '\1' WHERE zCollectionID = '\2';
If it all gets too complex, email me both the Database.dvdpd and the file with the extra information and I can merge them for you.