In the company I’m employed at we’re currently planning our service level agreements based on the different contracts, wondering how we can do justice to the situation of various public holidays of the German states.
Now I’m sitting here and wondering, how to easily create a single data source in excel for the public holidays in Germany and import them all at once. There should be a solution to make a bulk import but I’m not able to find something on the world wide web.
So now I’m on my own. To my luck there are some default schedules for the U.S. Holidays on which I can orient myself.
The entries shown there are very well. But what’s with the dynamic holidays like Easter Sunday? – One solution came around in mind: Create an own schedule with all dynamic days for this and then create an parent-child relationship. For this it should be possible to create an excel file and then import everything all at once.
Export source data
First of all, I viewed the schedules table (cmn_schedule) where all schedules are stored. To export this table, you only need to click on one column option , select Export and then Excel (.xlsx).
To export the second table, I had to find the name. For this, I opened an schedule entries entry and right clicked on Show – ‘name’. After this, a small window appeard and I was able to see the table name (cmn_schedule_span):
I searched for this table by typing in cmn_schedule_span.list in the navigation field and hit enter. I exported this table like the one before.
Now I have the data of both, the schedules and the schedule entries.
From now it would be easy – I thought.
After I viewed the Content frame, I saw a related list which handles the parent/child relationships. This is a requirement as well. To find this table, I had to search a lot. But I’ve done it.
You need to klick in the table, e.g. Child Schedules on the Column Options , Configure and then select table. A new form is shown and you can see the table name cmn_other_schedule. Now I searched again in the navigation field to view the entries and export them.
Now I have all three source tables where I can orient myself:
Cmn_schedule.xlsx
– all schedules are stored here
Cmn_schedule_span.xlsx
– all schedule entries are stored, like the holiday “Neujahr” and it’s value, even if and when it’s repeated.
Cmn_other_schedule.xlsx
– all parent child relationships are stored here
Plan the data
I have opted for a detailed and yet simple method. For each object I make an own schedule where either the entry is stored or at least the reference to the child schedule.
So I had to gather all the information. You can see them in the following list:
- Allerheiligen
- Augsburger Friedensfest
- Baden-Württemberg
- Bayern
- Berlin
- Brandenburg
- Bremen
- Buß- und Bettag
- Christi Himmelfahrt
- Deutsche einheitliche Feiertage
- erster Weihnachtstag
- Fronleichnam
- Hamburg
- Heiligabend
- Heilige drei Könige
- Hessen
- internationaler Frauentag
- Karfreitag
- Mariä Himmelfahrt
- Mecklenburg Vorpommern
- Neujahr
- Niedersachsen
- Nordrhein-Westfalen
- Ostermontag
- Ostersonntag
- Pfingstmontag
- Pfingstsonntag
- Reformationstag
- Rheinland-Pfalz
- Saarland
- Sachsen
- Sachsen-Anhalt
- Schleswig-Holstein
- Silvester
- Tag der Arbeit
- Tag der deutschen Einheit
- Thüringen
- Weltkindertag
- zweiter Weihnachtstag
In the second step I need the relationship of the individual schedules.
| Parent Schedule | Child schedule |
| Baden-Württemberg | Heilige drei Könige |
| Baden-Württemberg | Fronleichnam |
| Baden-Württemberg | Allerheiligen |
| Bayern | Mariä Himmelfahrt |
| Bayern | Allerheiligen |
| Bayern | Heilige drei Könige |
| Bayern | Fronleichnam |
| Berlin | internationaler Frauentag |
| Brandenburg | Reformationstag |
| Bremen | Reformationstag |
| DE consostent Holidays | DE consostent dynamic Holidays |
| Deutsche einheitliche Feiertage | Ostermontag |
| Deutsche einheitliche Feiertage | Pfingstmontag |
| Deutsche einheitliche Feiertage | zweiter Weihnachtstag |
| Deutsche einheitliche Feiertage | Tag der Arbeit |
| Deutsche einheitliche Feiertage | Tag der deutschen Einheit |
| Deutsche einheitliche Feiertage | Silvester |
| Deutsche einheitliche Feiertage | Karfreitag |
| Deutsche einheitliche Feiertage | Christi Himmelfahrt |
| Deutsche einheitliche Feiertage | Heiligabend |
| Deutsche einheitliche Feiertage | Ostersonntag |
| Deutsche einheitliche Feiertage | Pfingstsonntag |
| Deutsche einheitliche Feiertage | erster Weihnachtstag |
| Deutsche einheitliche Feiertage | Neujahr |
| Hamburg | Reformationstag |
| Hessen | Fronleichnam |
| Mecklenburg Vorpommern | Reformationstag |
| Niedersachsen | Reformationstag |
| Nordrhein-Westfalen | Fronleichnam |
| Nordrhein-Westfalen | Allerheiligen |
| Rheinland-Pfalz | Fronleichnam |
| Rheinland-Pfalz | Allerheiligen |
| Saarland | Mariä Himmelfahrt |
| Saarland | Fronleichnam |
| Saarland | Allerheiligen |
| Sachsen | Buß- und Bettag |
| Sachsen | Reformationstag |
| Sachsen-Anhalt | Heilige drei Könige |
| Sachsen-Anhalt | Reformationstag |
| Schleswig-Holstein | Reformationstag |
| Thüringen | Reformationstag |
To plan the data for the entries, I searched the web to find a list where every holiday in germany and the corresponding states are stored. After a short time I found what I was looking for. On the web page of excelformeln (http://www.excelformeln.de/formeln.html?welcher=3) is a well explained forumla to identify the Ostersonntag. Based on this holiday, it’s easy to calculate each other dynamic holiday. There is also a file stored which already provides every holiday in each state. A nice benefit!
With this file it was very easy for me to plan the holidays and transform them into the file.
The most important fields for the entries are the following in a specific format:
| Field name | Example |
| Name | Neujahr |
| All day | TRUE |
| Repeat on | 1 |
| Start date time | 01.01.2019 00:00:00 |
| End date time | 01.01.2019 23:59:59 |
| Class | Schedule Entry |
| Month | 1 |
| Monthly type | Day of the month |
| Repeat every | 1 |
| Repeat until | 0 |
| Repeats | Yearly |
| Schedule | Deutsche einheitliche Feiertage |
| Show as | Busy |
| Type | Busy |
| Yearly type | Day of the year |
Import the update sets
Warning: it’s necesarry to import the files in the following order. If not, the importing process will go wrong:
Cmn_schedule
Cmn_schedule_span
Cmn_other_schedule
To import the updates, I first have to load the data. Go to System Import Sets -> Load Data.
I created a new table like displayed in the image and hit submit:
The data was successfully loaded. Now I have to create a transform map. Therefore click on the Next steps Create transform map. After I provided all necessary information, I clicked on Auto Map Matching Fields.
Sometimes you have to use the Mapping Assist to map the source field to the correct target field. After everything has been mapped, you can Transform.
Now you can view the Schedules list and validate the import.
Now repeat this with the other two tables:
Cmn_schedule_span.xlsx
Load Data:
– Import set table: Create table
– Label: cmn_schedule_span holiday import
– Source of the import: File
– File: cmn_schedule_span.xlsx
Create transform map:
– Name: cmn_schedule_span holiday import
– Target table: Schedule Entry [cmn_schedule_span]
Auto Map Matching Fields
Mapping Assist (optional) NOTE: check Name field
Transform
Cmn_other_schedule.xlsx
Load Data:
– Import set table: Create table
– Label: cmn_other_schedule holiday import
– Source of the import: File
– File: cmn_other_schedule.xlsx
Create transform map:
– Name: cmn_other_schedule holiday import
– Target table: Other Schedule [cmn_other_schedule]
Auto Map Matching Fields
Mapping Assist (optional)
Transform
Please validate the new Schedules. Now you and we are able to create SLAs to provide correct functionality based on your needs.
I don’t want to deprived the files:
Download
Fix IT - today. 