Formatting grandpa's TV overview

[ScoIaro]

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!

[#2]

@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.

[#3]

@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.)

[#4]

@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. 

[#5]

@Riny_van_Eekelen Thanks again!

Yes, that's actually what I described in my original posting. Sending the extract via email provided the best inital output for my purpose. I copy/pasted the email content into Excel, and used the second tab to display only the data I needed in the right order, however, that's when I started wondering if I could automate it with some sort of formula in order to simply copy/paste the data and Excel would create the correct list on tab 2 automatically, based on the content of column A (as I'm only interested in data of the lines that have content in A).

If that's not possible I'll happily have Mum send me the email once she's chosen the shows and do the rest (deleting the superfluous lines and adjusting the font, size and layout) manually myself - compared to Mum's previous workflow this will be much quicker already. 

I just thought Excel had a more elegant solution for the second part too, so that once the Excel file is set up, she could copy/paste the email content and use the adjusted data in the second tab without having me to work on the file as well. 

[#6 · Accepted solution]

@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.

[#7]

@Riny_van_Eekelen I took a look at Power Query and will definitely give it a shot!
(On top of that I contacted the Klack.de website and asked them to change the ANSI extracts to Unicode, so let's see who's quicker solving the problem, me or them. ^^)

Thank you very much for taking the time to check this and reply to me!