EyeTV episode name not importing to DVDpedia episode name

[Oolfanska]

I've added Episode Name as a custom field. When I import from EyeTV, the series title and episode name are combined into DVDpedia's Title field, and nothing goes into the Episode Name field. Am I missing something?

Edit Jan. 10: I'm sending example files by email.

[sjk]

Not sure it's related but there's been at least one other report of missing attributes during EyeTV imports:

Import from EyeTV

Thanks for the reminder I still need to check it here.

[Nora]

The problem with the import is fixed in the beta, thanks for the files Oolfanska.

[sjk]

Is this beta newer than Beta 5?

Edit: Never mind; I downloaded it to determine that it's newer, but still really wish that wasn't necessary.

[Oolfanska]

Now that the episode names are coming in so beautifully, I am looking at the dozens ... nay, hundreds ... of old episodes that still have their title & episode name combined in the Title field. Is there any way to split those automatically or at least in a faster way than cutting & pasting every single one?

I tried doing it external to DVDpedia by exporting a collection and doing the split in Excel with a text formula (easy), but then I couldn't find any way to get the stuff back into Pedia. "Import collection" created a whole new set of entries that were very pretty but weren't linked to their media files.

If it's not possible, I'll survive , and eventually just slog through the entries, knowing that I'll only have to do it once.

[Conor]

Unfortunately, the SQLite functions (type of database we use) are a bit limited and there is no way to split the episode information out by looking for the dash character. If you have the original EyeTV files you could remove the entries and drag over the EyeTV files again to create new entries with the correct information.

Otherwise, the SQLite can at least be used to copy the title information into the episode field, making it easier to delete the irrelevant information without having to copy/paste.
First make a copy of the database file as backup, located in ~/Library/Application Support/DVDpedia/Database.pediadata
Run the program named Terminal and execute the following commands one line at a time:

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.pediadata Starts up SQLite3 with the DVDpedia database.

update zEntry set zCustom1 = zTitle where zCustom1 is null; Need to replace custom1 for the number of the custom field that you set as episode name.

If you have many of a single series then remove the series name from the newly set episode name with the command (leaving you only with the removing the episode name from the title field by hand):

update zEntry set zCustom1 = replace(zCustom1, 'The Simpsons – ', ''); Update custom1 again for the correct number and change the series name to match.

[Oolfanska]

Unfortunately, the SQLite functions (type of database we use) are a bit limited and there is no way to split the episode information out by looking for the dash character. If you have the original EyeTV files you could remove the entries and drag over the EyeTV files again to create new entries with the correct information.

I would do that if I hadn't already added a lot of other information in DVDpedia that would be wiped out by importing afresh.

Otherwise, the SQLite can at least be used to copy the title information into the episode field, making it easier to delete the irrelevant information without having to copy/paste.
<snip>

Thanks for the detailed instructions; that will help.

[Oolfanska]

Unfortunately, the SQLite functions (type of database we use) are a bit limited and there is no way to split the episode information out by looking for the dash character. If you have the original EyeTV files you could remove the entries and drag over the EyeTV files again to create new entries with the correct information.

Thanks to your other SQLite tips, I was able to get some ideas and solve the problem.

1. Example episode name problem: Numb3rs - Jacked. Want to turn this into just "Jacked."

2. Create the problem string in a temporary custom field:

Code: Select all

update zEntry set zCustom3 = zTitle || ' - ';

This produces the string "Numb3rs - " in the temp field.

3. In another temp field, take the episode name (Custom1) and replace the problem string (Custom3) with nothing.

Code: Select all

update zEntry set zCustom4 = replace(zCustom1,zCustom3,'');

This produces the string "Jacked" in the temp field.

4. Once those are all looking good, put them into the Episode Name field.

Code: Select all

update zEntry set zCustom1 = zCustom4;

Perfect. I'm sure there are more efficient or elegant ways to do this but I'm a SQLite newb and am delighted to have gotten this far.

[Conor]

Thank you for the details. I was not aware of the trick with || to cut the string short. In fact since Google does not search for characters such as periods or the pipe (personal peeve) I am still unsure on what exactly it does? I originally spent a while looking at the substr() function looking for a solution. But I was unable to find a function that would give me the location of the dash character as SQLite does not support charindex().

[sjk]

Thanks for this discussion and ideas for how to post-process DVDpedia's custom Episode Name field to remove unwanted content.

[Oolfanska]

Thank you for the details. I was not aware of the trick with || to cut the string short. In fact since Google does not search for characters such as periods or the pipe (personal peeve) I am still unsure on what exactly it does? I originally spent a while looking at the substr() function looking for a solution. But I was unable to find a function that would give me the location of the dash character as SQLite does not support charindex().

I may be misunderstanding your question, so forgive me if I'm answering something you're not asking! The double pipe is the concatenation character. What my code step is doing is taking the title ("Numb3rs") and adding a space-hyphen-space to make "Numbers - ", which is the extra stuff that I need to delete from the beginning of my episode names. I couldn't find any way to locate the position of a character either, but since the extra junk had a fixed form (title space hyphen space), this method worked fine.

I also tried the ltrim() function, aiming to remove the same string, but apparently ltrim looks at the character string as a list of individual characters to be removed rather than as a unit. So that didn't work at all.

Side note: By the way, I also used the concatenation function to take a bunch of hand-entered episode numbers in 3 custom fields (season number, season episode number, and series episode number) and put them into the Episode field in the format "312-56" (season 3, ep 12, series ep 56), which is how they are coming out of EyeTV now that I created the correctly named Episode field for that data.

[Conor]

Thank you for the explanation. That was very clever, I was looking at the first command only and trying to figure out how the concatenation was cutting the long title string short. Now I understand that you were setting up a custom field to use in the replace later on. I was confused thinking that title actually had "Numb3rs - Jacked" and not just "Numb3rs".

[Oolfanska]

Thank you for the explanation. That was very clever, I was looking at the first command only and trying to figure out how the concatenation was cutting the long title string short. Now I understand that you were setting up a custom field to use in the replace later on. I was confused thinking that title actually had "Numb3rs - Jacked" and not just "Numb3rs".

Yes, I had a library full of "Numb3ers - AAAAA", "Numb3ers - BBBBB", "Numb3ers - CCCCC" etc. (and other shows likewise), where the actual episode titles are AAAAA, BBBBB, and CCCCC.

There's probably a way to do it with fewer steps and custom fields, but it helped me keep straight what I was doing. I'm no programmer so I was just breaking it off into manageable chunks.