move data to another field?

Talk to other Pedia users about the programs, share tricks and tips or ask questions about existing features.

move data to another field?

Postby MikeB » Thu Jan 20, 2011 10:49 am

When I started using DVDPedia several years ago, there was no field for "location". I made a custom field and called it location, so I have it. Now I would like to move the data in the custom location field to the regular location field. Is there a way to do this automatically?
MikeB
Addicted to Bruji
Addicted to Bruji
 
Posts: 73
Joined: Wed Apr 09, 2008 7:51 am
Location: The Netherlands

Re: move data to another field?

Postby sjk » Thu Jan 20, 2011 12:01 pm

I think the only way to do this is by directly modifying the SQlite database (e.g. using sqlite3), similar to methods/examples in other topics (search for sqlite* to find 'em). Conor or Nora may be able to provide the specific commands; I'm a db dunce. :)
User avatar
sjk
Addicted to Bruji
Addicted to Bruji
 
Posts: 529
Joined: Sat Feb 21, 2009 9:01 pm
Location: Eugene

Re: move data to another field?

Postby Conor » Thu Jan 20, 2011 8:16 pm

SJK is correct, since it's not a common occurrence there is no GUI interface yet to transfer an entire field to a new field, you need to issue an SQL command. Although it sounds daunting it will take no more than 2 minutes.

1. Quit DVDpedia.

2. In your Finder navigate to your database file in ~/Library/Application Support/DVDpedia/Database.dvdpd and select it and choose duplicate under the File menu simply for backup purposes.

3. In your Applications-->Utilities find the program called "Terminal" and start it up.

4. Copy the following lines and paste them into TextEdit and update "zCustom1" to the correct number of the custom field you want to move then copy paste the amended commands into the Terminal (you can count the number of the custom field left to right in the DVDpedia preferences):
Code: Select all
sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zLocation = zCustom1 where zLocation is null;
.exit

5. Launch DVDpedia and enjoy. If things went well you can empty out your old custom field and reclaim it by selecting all your entries and using the multi edit, check the custom field and leave it blank.
User avatar
Conor
Top Dog
 
Posts: 5195
Joined: Sat Jul 03, 2004 12:58 pm

Re: move data to another field?

Postby MikeB » Wed Jan 26, 2011 6:09 am

Thanks for the reply, both of you, but from the command you gave, Conor, will this delete the entries or move them? I currently have two fields both named "Location". I am trying to move the data from the custom field (named "Location") to the regular field named also "Location". Should I first rename the custom field to something else? Or have I misunderstood and the command you gave will work without renaming anything?
MikeB
Addicted to Bruji
Addicted to Bruji
 
Posts: 73
Joined: Wed Apr 09, 2008 7:51 am
Location: The Netherlands

Re: move data to another field?

Postby Nora » Wed Jan 26, 2011 6:21 am

The zCustom1 (or whichever Custom field it is that you renamed to 'Location') will tell the program exactly which field it is, regardless of what you might have named it. The display name is kept in the preference file and the database only knows the field as zCustom1-10. So do make sure you pick the right number.

The command as Conor wrote it out will only overwrite the data in those fields where there is NO information, i.e. if one of your regular location fields already holds data, it will NOT overwrite that. If you want to overwrite all data regardless then you'd have to change the commands to:

Code: Select all
sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zLocation = zCustom1;
.exit


If you want to check that you got the right custom number you can use the following command in between the first command "sqlite3 ~/Library/..." above and "update zEntry set".
Code: Select all
select zCustom1 from zEntry;

Will print out a long list of the values in custom1.
User avatar
Nora
Site Admin
 
Posts: 2149
Joined: Sun Jul 04, 2004 5:03 am

Re: move data to another field?

Postby MikeB » Fri Feb 04, 2011 11:16 am

Thanks both of you for the help. It took me twice to get the command to work because I had forgotten that the "real" database file is in my Shared fold, not in my home folder. But, once I changed the path in the command, it worked fine.

One reason I wanted to do this was to add the location field when I do a text export of the database (I put it on my iPhone). I use the MySmallList template.
MikeB
Addicted to Bruji
Addicted to Bruji
 
Posts: 73
Joined: Wed Apr 09, 2008 7:51 am
Location: The Netherlands


Return to PediaTalk