Forum Discussion
Formatting grandpa's TV overview
Hi. My Mum used to spend hours putting together a Word file with all the TV shows (channel, time, show title) that my grandpa may be interested in as he's almost completely blind and cannot read the regular TV program anymore. (Letters too small and too much irrelevant crap.)
Mum creates his individual TV program by hand, painstakenly copy/pasting the data from the TV calendar site into a Word file and then fighting with the formatting. (Before she did it by hand and typed it up which took even longer.)
Then it's printed out and stapled together for grandpa. This way he only has relevant shows to check in huge letters to read in his reading machine.
I told her I'd try to find a better/quicker way to do this, thinking about Excel right away.
Basically I found out that with the TV calendar site she already uses (klack.de) instead of copy/pasting the data manually she can create a TV planner which is a favourite list of the shows she marks for my grandpa. The csv files the site creates are pretty useless, however, when I have the site send the list via email and simply copy/paste the complete email content into Excel I get something I can work with. The extract looks like this:
| 3sat | 27.12.20 | 17:00 | Genosse Don Camillo |
| Komödie, I, F, D 1965 | |||
| Laufzeit: 105 Minuten | |||
| Original-Titel: Il compagno Don Camillo | |||
| Mit: Fernandel, Gino Cervi, Saro Urzì, Gianni Garko, Marco Tulli, Silla Bettini | |||
| Regie: Luigi Comencini | |||
| Die beiden Streithähne Don Camillo und Peppone liefern sich eine weitere Runde in ihrem ewigen Kampf: Um seinem Kontrahenten eins auszuwischen, ... | |||
| ZDFinfo | 27.12.20 | 18:45 | Die Vermessung der Erde |
| Wissenschaftsserie, D 2019 | |||
| Folge: 1 | |||
| Laufzeit: 45 Minuten | |||
| Original-Titel: Terra X |
Now what I'm trying to do is create a list with only the relevant data (channel, time, title) on a second tab, using "=Programmliste!A1", "=Programmliste!C1" etc on tab 2. to display the data in the right order for a clean list.
A1 (A9): Channel
C1 (C9): Time
D1 (D9): Show/movie title
| 3sat | 17:00 | Genosse Don Camillo |
| ZDFinfo | 18:45 | Die Vermessung der Erde |
My problem is that there's a lot of useless data in between, and unfortunately the number of lines between the titles varies depending on the program (news show, movie etc), so I cannot just make a template that always leaves out 7 lines between the relevant info.
I'm thinking maybe there's a way to create a template by using "ISTEXT" to find out if there's text in column A.
--> If it's true it displays the content of A, C and D of this line on tab 2.
--> If it's not true (if the cells are empty) Excel will leave them out.
But that's where my ambition overtakes my knowledge of the program.
Do you have any idea how this can be achieved?
Or maybe you have a much better way in mind to arrange this data in a meaningful/elegant manner?
Thank you!
ScoIaro Sorry, overlooked your comment explaining that you had used the email option already. The automation can be achieved if you can/want to use Power Query. Rather than sending and e-mail, download the marked programs as an XML file (although the extension becomes .rss).
Then in PowerQuery, you can connect to the downloaded file do some transformations once and the next time it will all go automatic. Tried it works great, but it will have to be adopted to your own system.
6 Replies
ScoIaro You mention that you have CSV files, downloaded from the site. Once in Excel, you can convert the data into a structured table, filter out the blanks and do the formatting you need.
The attached file contains an example. If this is not a workable solution for you, perhaps Power Query can help. Looked around at the site but it seems that it does not provide an overview of all channels for the entire day and/or for multiple dates. Or I just couldn't find it. If there is a view on the site that creates the desired table, perhaps you can provide the link to it.
Riny_van_Eekelen Thank you very much for your efforts!
Looked around at the site but it seems that it does not provide an overview of all channels for the entire day and/or for multiple dates.
That's actually not necessary - you can create individual csv files based on the "starred" shows, which is ideal as it condenses the material down to what grandpa is (or may be) interested in, however, my problem with the csv files is that the special characters are not displayed correctly and I would need to replace them all, depending on how many are used overall (usually Mum's TV program covers about two weeks, with several dozens of shows to choose from...a ton of special characters).I attached two csv files from that site as example.
One of them actually comes close to what I'm trying to do, but I guess it would overall still be easier/quicker to delete the superfluous lines from my earlier copy/paste example instead of manually replacing ü with ü, ö with ä, ß with ß etc. throughout a complete document. (For some reason the site does not offer Unicode setting for the csv files.)ScoIaro OK, understood. Perhaps you can get the data across in a different way. I went to "Ubersicht" and marked some programs using the "Merken" button after you clicked on the program.
Then, in "TV-Planer" I choose all marked programs to be sent as an e-mail. What I then get (and I'm not in Germany) is neatly formatted and includes all German specific characters, without the need to find and replace. Now you can create the table and filter out the unwanted rows.
