Forum Discussion
Formatting grandpa's TV overview
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.
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.
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.
