Idea needed for Publisher: From external data to table layout with less effort

[DaThomax]

Anyone here with some hacking skills?  Or someone that has experience with that kind of use case?

Overall situation: One one side, we have an online calendar tool that holds lots of events. On the other side, there a print product to inform people about these. Common thing, right? 
The challenge is that the data transfer is a) manual, b) time-consuming, and needs c) some add'l action to add icons for events that have one or more special categories assigned.

And: We need to do this six times a year, so it's really worth to put some automation into it.

The target layout is attached: A table with 5 columns (4 locations + 1 Date/Info) and several rows (one per date). Number of locations is fixed, number of rows depends on number of different dates with events; number of entries per cell depends on events. Usually, the table is not that crowded as in the example 🙂 

Now I'm searching for some idea how to automate this.

Minimum Valuable Product (MVP): Fill the table with data, but without formatting and with some kind of marker what icons to add.
Medium Situation: Have table with data and icons
Best case: Have table with data, text formatting and icons (position of icons will be done manually)

It's clear that we need "something" that takes the calendar data and transforms it into "something" that can be imported into Publisher. But...what format to use that fits either layout needs, or can be created easily - or even both?

Any idea is appreciated - it doesn't need to be a complete solution!

[Hangman]

Hi @DaThomax and welcome to the forums,

Technically it should be possible to achieve this in Publisher using the Data Merge facility but a lot depends on the formats you can export the source calendar data to.

Can you provide more details about the calendar you're using and any file formats to which the calendar can be exported? If you can export to Excel or CSV or even JSON it's quite likely this would be relatively straightforward though having the source data to run some tests would be helpful...

[DaThomax]

Basically, all I have is a .ics file (ical format). Since I need to transform that anyways, I can bring it to any format 🙂 but have no ready-to-use file yet 😞 

I did not realize until now that Data Merge would also work for tables and adding icons? If you have a link to a demo/explanation I could proceed further 🙃

[Hangman]

If you Google iCal to CSV converters and experiment with a few of them you should find you can convert your exported iCal data to a CSV which you could then edit as required in Excel, Google Sheets or Apple Numbers depending on your platform which would or should give you a file you can then use to as your Data Merge Source...

For your images, you could include a URL to the graphic files as part of the Excel or CSV file which you can then use to populate your table...

I have no doubt it will take a bit of experimentation and tweaking to get the template just right but take a look at a few of these YouTube videos for Data Merge ideas and let us know how you get on and of course feel free to ask questions if there's anything you can't figure out or provide a sample file if something isn't working as expected...

https://www.youtube.com/results?search_query=affinity+publisher+data+merge