With the rooming list / import file feature, you can import all guests and reservations for an allotment from an Excel spreadsheet in just a few easy steps.

Step 1: In protel Air, an import template (Microsoft Excel 2007 table with the ending xlsx) becomes available to be downloaded for the allotment at a certain status.
Step 2: Provide the spread sheet to the booker and get it filled in, then upload it into protel Air again.
Step 3: Executing the import of the uploaded spread sheet is creating reservations for all the guests and picking up from the allotted rooms automatically.

Example

The allotment contract was sent to the booker of an allotment for signing. It is signed and returned a few days later together with the acceptance of the terms and conditions. Rooms are blocked for the allotment and only reservations are missing.

To simplify the process, the reservation agent sends the booker an Excel spreadsheet in which the booker can enter the names of the guests and all other relevant information. The clue: The Excel table can be created directly in protel Air individually for the respective allotment.

The reservation employee therefore generates the Excel table for the relevant allotment and sends it to the booker for completion. A few days later the completed Excel spreadsheet comes back. The reservation employee can finally set the allotment to ”Open for Pickup”, which allows to generate guest reservations on the allotted rooms.

The Excel spreadsheet is then uploaded, checked, corrected manually if necessary, and finally imported for the allotment.

After successful import, all reservations for the listed guests have been generated in the system!

Watch our Video Tutorial to learn more!

Accessing the Rooming List Import function

The import and export of rooming lists is started individually for each allotment.

Call up the allotment (Reservation > Allotments > Edit allotment) and click on the rooming List / Import file button at the bottom left of the window

Please note!

The rooming list button is only active if a certain number of rooms has been created for the allotment (see section "Allotment") and the allotment status has been set to Open for Pickup!

Afterwards, a wizard guides you through all the necessary steps.

Download template

Reservation > Allotments > Open allotment > Button "rooming list"]

The first step is to download a template. Using the template, protel Air provides a editable rooming list in Excel format, which already contains all relevant columns for entering guests.

Click on Download Template - see the image below.

Then protel Air creates an Excel file (xlsx) which is placed in the default download directory of your web browser. The Excel file has the same name as the allotment for which it was created.

Example

For the allotment "IPM 2018" with the ID "12" ...

... the Excel file "IPM 2018 12.xlsx" is placed in the download directory.

Notes on the xlsx file format

The file extension xlsx is the Excel file format of Microsoft. The xlsx format is supported from Excel version 2007 and higher.

There are numerous programs that you can use to open and edit Microsoft Excel 2007 spreadsheets with the extension xlsx. Some programs have the characteristic to convert xlsx files into other file formats. Therefore, make sure to use a program that retains the xlsx format when saving the table!

The following programs were tested and released in connection with the rooming list feature of protel:

Libre Office Calc
Google Docs / Spreadsheets
Microsoft Excel from 2007 and higher

protel does not guarantee the compatibility of the import function with Excel files edited and stored in programs other than those listed above.

Structure of the Template

The Excel file is designed to enter up to 50 reservations - see the screen shot below:

Each row corresponds to a reservation record that will be created for the allotment: One guest profile and one reservation are created per line (several if the number of rooms is the same).
Each column in the table corresponds to a database field in protel Air.
Some entries require a specific format or input so that the entered data can be correctly assigned during subsequent import. For this reason, the Excel file is provided with a mouse over help: If you click on the column header, you receive a message informing you which entries in which format are expected in the column (the functionality of the mouse over help depends on the program or program version used).
Incorrectly entered values can be corrected manually before the import (see below: Data Import).
In general: The more carefully the table is filled in the first time , the less rework is required later!

Columns and their meanings

The following list contains all fields protel Air provides for import. By showing and hiding columns, you can determine which fields are to be contained in the Excel file (see section Showing and Hiding Columns).

Mandatory fields: Columns marked as mandatory fields must be filled in. The reservation data record cannot be created without entering the mandatory field .

Dropdown lists: If certain elements are made available in a cell via a dropdown list, no other elements than those contained there may be entered!

Column	Mandatory field	Format/selection	Explanation
ID		Figure	Sequential number beginning with 1. This number is already filled in and may not be changed!
Last name		Free text	Last name of the guest. Entering the last name is mandatory.
First name		Free text	First name of the guest.
Middle name		Free text	Middle name
Second last name		Free text	Guest's second last name
Salut.		Drop-down list	Guest's personal title. An element must be selected from the list. NO free text input!
Gender		Drop-down list	Guest's gender. An element must be selected from the list. NO free text input!
Birthday		Date	Guest's date of birth. The date of birth must be entered in a valid date format.
Language		Drop-down list	Language of the guest. An element must be selected from the list. NO free text input!
Nationality		Drop-down list	Nationality of the guest. An element must be selected from the list. NO free text input!
Passport		Free text	Identity card number, passport, etc.
Arrival		Date	Date of arrival. The date of arrival is mandatory.
Departure		Date	Date of departure. The date of departure is mandatory.
Number of Rooms		Figure	Number of rooms for this guest. This information is mandatory.
Number of Adults		Figure	Number of adults for this guest. This information is mandatory.
Number of Children		Figure	Number of children for this guest.
RT		Drop-down list	Room type to be booked. An element must be selected from the list. This information is mandatory. NO free text input! Only room types that have been configured in the allotment are offered for selection here!
Arrival time		Time	Planned time of arrival.
Departure time		Time	Planned time of departure.
Notes		Free text	Additional information for the reservation.
VIP codes		Drop-down list	Guest's VIP code. An element must be selected from the list. NO free text input!

Example Room type

Please make sure that you correctly enter the room type! If the allotment has been configured for one (or more) room type(s), the Excel table automatically suggests the appropriate room type as a selection -see the following screen shot. PLEASE DO NOT ENTER ANY FREE TEXT HERE!

If the user enters a room type different from the one offered here, the corresponding reservation data record cannot be assigned correctly during import and must be edited manually.

Example First name

The input of the first name is free, here no certain inputs / input formats are expected.

Example Salutation

Only the salutations configured in the protel Air system data are accepted as input for the salutation.

The user therefore has the option of selecting the salutations from a dropdown list:

Show and hide columns

You can easily hide columns that you do not need for the import and template. It is important that you do not hide any mandatory fields, as these are required for error-free import.

How it works

Open an allotment that has been configured and is open for pickup, but for which a rooming list has not yet been imported.
- Configured = For the allotment, corresponding rooms must be configured in the "Allotment" tab.
- Open for pickup = The status "Open for pickup" must be set for the allotment (checkbox)
Click the rooming list button.
Then click Download Template.
Stay in the window Rooming list.
Click Browse to open the empty template you just created (the template is uploaded).

In the next window you can set which data should be used for the creation of the rooming list by showing and hiding the columns.

Example

In the following example, we assume that all columns are displayed (delivery status) and that certain columns are to be hidden.

After you have imported the template into protel Air, the view Step 2 - Assign data opens.

You can hide columns which you do not require as follows:

In the Preview section, move the mouse pointer over the column header of any column and then click on the small arrow - see illustration:

Choose Columns from the drop-down menu.

Columns that are activated in the template are marked with a check mark.

To hide a column, uncheck the checkbox.

In our example, several columns are hidden at once - see the screen shot below:

Attention!

Never hide columns that are mandatory fields because they are required for error-free import! Mandatory fields are:

ID
Last name
Arrival
Departure
Number of rooms
Number of adults
Room type

The columns displayed in the window are refreshed based on the selections made.

Now click on Save current list view.

Close the window by clicking Exit.

Test Template

Now test whether the Excel file for the rooming list is created according to the new column settings.

In the allotment whose window is still open, click Rooming List.

Then click Download Template.

Open the created Excel file and look at the columns. The file created in the example above looks like this:

This is the direct comparison with the columns displayed in step 2 - Assign data:

As shown in the screen shot, each column in the Excel spreadsheet is assigned to a column in the Rooming list dialog box.

Mapping

If required, the mapping between the protel Air database fields and the Excel columns in the Rooming list dialog box can be changed - see the following screen shot:

On the left you see the protel Air database field - on the right in the dropdown list the imported Excel columns.

By selecting an element from the dropdown list, the mapping can be adjusted or redefined if necessary.

Send template to the booker

Depending on the internal procedures in the hotel, the template will be sent to the booker of the allotment, together with a request to fill it in and return it to the hotel. When you send the template to the booker, please inform the respective employee about the filling aids in order to get back an Excel table that has been filled in as correctly as possible.

Fill in template

The template can be filled in either by the booker or by the hotel, depending on the method used. The user should have basic knowledge of spreadsheet programs.

Open the file with one of the programs listed above (see notes on the xlsx file format).
If necessary, adjust the width of the columns to make them easier to read.
Fill in a row in the table for each guest.
The ID column is already prefilled and should not be changed.
Some columns already suggest the values accepted by the hotel software; please select an appropriate element from the list.
Date specifications (e.g. arrival and departure date) must be entered in a valid date format (e.g. 01.12.2018).
Times (e.g. arrival and departure) must be entered in a valid time of day format (e.g. 16:00).
Note the filling aids in the Excel file: If you click on the column header, you will receive a message informing you which entries in which format are expected in the column (the functionality of the filling help depends on the program or program version used. protel recommends the Libre Office Calc or Google Spreadsheets to display the filling help).

Save template

Once all guests have been entered with all the relevant information (note the mandatory fields!), the Excel file can be saved.

Please do not change the format (xlsx) or the name of the file!

Import Template

Reservation > Allotments > Open allotment > "rooming list" button > Upload file]

When you have completed the rooming list or received the completed list from the booker, you can import the file into protel Air.

Step 1: Upload file

Call up the allotment (Reservation > Allotments > Edit allotment) and click on the Rooming list button at the bottom left corner of the window.
Click on the Browse... button.
Select the file filled in by the booker and click Open.

The file is now uploaded.

User feedback

If the import file contains information that cannot be assigned correctly, you receive a corresponding message.

Example: A reservation cannot be imported because the reservation has been assigned to a rate code which is not valid due to a rate restriction. In this case you receive one of the following messages.

MLOS has been set or rate code has been closed:
Message: Rate code not valid for reservation date range. Please check rate restriction in force.

Rate code is closed to arrival (CTA):
Message: Rate code not valid for reservation date range because closed for arrivals restriction has been set for this rate.
Rate code is not valid for the reservation date range.
Message: Rate code not valid for reservation date range. Please check configuration of rate setup.

Step 2: Mapping data

When mapping data you can set and check which column is mapped from which table to which database field in protel Air.

On the left side you see the database fields, in the selection field beside it you see the available table columns of the Excel file.

You can hide columns you don't need or display missing columns.

If you display a column, you will need to reassign it. If you hide a column, you can delete the assignment (click on the small cross in the dropdown list).

If all fields have been correctly mapped, click Continue.

Step 3: Validate and edit

The window in step 3 displays the imported reservation data records and gives you an overview of how many fields have been inserted.

In the view, you can add missing entries "inline", i.e. directly in the table, or correct incorrect entries.

To do this, click on the respective row and column and enter the desired value - see the following screenshot (Arrival date is changed in the screenshot).

Salutation, gender, language, nationality and room type can be taken from a dropdown list. Here the values configured in protel Air are automatically offered for selection.

When you have checked all the fields and corrected or added any errors, click Import.

You then receive a message telling you how many reservations were imported successfully.

If the import was successful, click the Exit button.

If the Import was not sussesful, you can click Back to check the data once again.

The imported reservations are displayed in the Reservations tab of the allotment - see the following screen shot:

Tip: If the reservations do not appear immediately in the Reservations tab, close the allotment and reopen it!

NOTE!

With each import the contained data (guests and reservations) are newly created! A repeated or multiple import of the same Excel file can result in corresponding data duplicates.

FAQ

Which data is imported?

For each row in the Excel table at least one reservation and one profile is created in protel Air with the functions specified in the table columns.

The profiles are created with the Marked for deletion flag. This prevents excessive duplicates from being created in protel Air.

Guest files marked for deletion are hidden from the guest search by default. They are only displayed if "files marked for deletion" are explicitly displayed in the guest search.

The reservations are created with the reservation codes specified in the allotment.