From Spreadsheet to Hotel PMS: How to Clean Your Data Before Import

Why Imports Fail Before They Begin

The appeal of a data import is that it sounds like a one-step operation: export the spreadsheet, upload to the PMS, done. In practice, the import is a formatting and completeness problem before it is a technical one.

A hotel PMS requires data in defined structures: a guest record needs a first name, last name, and at least one contact field. A room type needs a consistent name, a defined count, and a maximum occupancy. A reservation needs a guest reference, dates, room type, rate, and source channel. Data in a spreadsheet that has grown organically over three or four years rarely meets any of these requirements as-is.

The cleaning step is not optional — it is the difference between an import that loads cleanly and one that requires hours of manual correction after the fact.

Before You Start: Map What You Have to What the PMS Needs

Before touching the spreadsheet, pull the PMS's import template or field requirements. Most systems provide a sample CSV or an import guide that lists the required and optional fields for guests, rooms, rates, and reservations. That document is the target format — the spreadsheet cleaning job is to reshape existing data to match it.

Audit each data category in the spreadsheet:

• How many unique guest records exist, and what fields are filled for most of them?
• How many room names are in use, and do they represent room types or individual rooms?
• Are rate plans documented, or is the rate column just a number?
• How many future reservations are listed, and are they complete?

This audit determines where the cleaning effort is highest and which categories might be faster to re-enter manually than to clean and import.

Cleaning the Guest List

The guest list is usually the most cluttered category. Over several years, a typical small hotel spreadsheet accumulates the same guest entered under different email addresses from different booking channels, name variations (J. Smith vs. John Smith vs. Smith, John), and records from one-time bookings with no usable contact information.

Remove duplicates first. In Excel or Google Sheets, sort by email address or phone number and identify rows where the same guest appears multiple times. Keep the most complete record and delete the rest. If a guest has stayed five times but appears as five separate rows, merge the records.

Standardize name format. Most PMS systems store first name and last name in separate fields. A spreadsheet column containing "Smith, John" or "John Smith (Booking.com)" needs to be split before import. Use the spreadsheet's text-to-columns or formula tools to separate fields cleanly.

Flag incomplete records. A guest row with no email address and no phone number cannot receive automated messages. Decide whether to import these records as-is (they will exist as names with no contact channel) or to skip them. Importing every historical guest regardless of data quality is usually not worth the cleanup it creates.

Filter for recent and recurring guests. Guests who last stayed more than two years ago and have not returned are unlikely to generate value in the new system. Importing them adds volume without adding utility. A practical filter: import guests who have stayed at least twice or who stayed within the past 12 months.

Standardizing Room Names and Room Types

A PMS organizes inventory by room type — a category that groups physical rooms with the same features, rate, and capacity. A spreadsheet typically records individual rooms, often inconsistently: "Room 101," "Deluxe King 2nd Floor," "Ocean View — king," and "OK king" might all refer to the same room type across different rows written by different staff members at different times.

Before importing, define the room types the PMS will use. For a property with 12 rooms, this might be three or four types: Standard Queen, Standard King, Deluxe King, and Accessible Queen. Every physical room maps to exactly one of these types.

With the types defined, audit the spreadsheet for room name variations that refer to the same type and standardize them. The name used in the import must exactly match the name configured in the PMS — a reservation imported as "Deluxe King" will not attach to a room type named "King Deluxe."

Also clarify the distinction between room name and room number. Some PMS systems use a room number for internal assignment and a room type name for booking display. The spreadsheet may conflate these, requiring a split before import.

Preparing Rate Plans

A rate plan defines the price structure for a room type: the nightly rate, the conditions it applies under (dates, length of stay, guest count), and the OTA channel it connects to if applicable.

Most small hotel spreadsheets do not document rate plans as structured objects — they record a number in a rate column with no indication of whether it applies to weekdays, weekends, peak periods, or all of the above. Before importing, decide which rate plans the PMS will use and which rates in the spreadsheet map to each.

Import only currently active rate plans. Historical seasonal rates that no longer apply do not need to transfer. The priority is getting the rates that govern upcoming reservations into the system correctly.

If the property uses different rates by channel — a net rate for Booking.com, a direct rate for phone bookings — verify how the PMS handles this before import. Some systems require separate rate plans per channel; others use a base rate with commission rules applied on top. Importing channel-specific rates into a base rate field produces incorrect numbers without generating an error.

Migrating Future Reservations

Future reservations are the most consequential data to import. A guest with a booking for next month who does not appear in the new PMS is a problem the property discovers at check-in, not during the migration.

Export all future reservations from the current spreadsheet — everything from today through at least 90 days out. For each reservation, the minimum required fields are: guest name, check-in date, check-out date, room type, nightly rate, total amount, source channel, and payment status (deposit collected, balance due, paid in full).

After import, verify every future reservation against the original spreadsheet row by row. Check that dates transferred correctly (date format mismatches are the most common import error), that room types matched, and that payment status is accurate. Any reservation showing as fully paid when a balance is owed — or vice versa — must be corrected before the guest arrives.

For OTA reservations, cross-reference the imported record against the OTA platform's confirmation. The PMS record and the OTA record should show the same dates, room type, and guest name.

What to Leave Behind

The instinct during a migration is to move everything. This produces a bloated import that takes longer to clean and adds noise to a system the team is still learning.

Leave behind:

• Cancelled reservations (records, not active data)
• Guests with a single visit more than two years ago and no contact information
• Historical rate plans from closed seasons
• OTA reservations the platform will push into the PMS automatically once the channel connection is live

Prioritize:

• All future reservations
• Recurring guests and guests with stays in the past 12 months
• Rate plans applying to confirmed or near-term bookings

FAQ

What data should I import when moving from a spreadsheet to a hotel PMS?

The four categories to import are the guest list, room types and names, active rate plans, and future reservations. Of these, future reservations are the most urgent — any upcoming booking that does not transfer correctly creates a problem at check-in. Guest history and historical reservations can be imported selectively or re-entered over time; they do not need to be complete before the first day of live operation.

How do I clean a hotel guest list before importing it into a PMS?

Remove duplicate records by sorting on email or phone number and keeping the most complete version of each guest entry. Standardize name format so first and last names are in separate fields. Flag or remove records with no contact information. Filter the list to include only guests who have stayed more than once or who stayed within the past 12 months — importing every record ever entered adds volume without adding utility.

Why do room names cause problems in hotel PMS imports?

A PMS organizes rooms by room type, not by individual room names. Inconsistent naming in a spreadsheet — where the same type of room has been recorded as "King," "King Room," "Deluxe King," and "Room 101 King" — produces records that do not match any configured room type during import. The fix is to define the property's room types before import and standardize every room name in the spreadsheet to match exactly.

How far out should I import future reservations when switching to a PMS?

Import all reservations from today through at least 90 days out. After import, verify each record against the original spreadsheet to catch date format errors, room type mismatches, and payment status discrepancies. OTA reservations should also be cross-referenced against the OTA platform's own confirmation records — the PMS record and the OTA confirmation must match on dates, room type, and guest name before the channel connection goes live.

What should I not import when migrating from a spreadsheet to a hotel PMS?

Leave behind cancelled reservations, guests with a single visit more than two years ago and no contact information, historical rate plans from closed seasons, and OTA reservations the platform will push into the PMS automatically once the channel connection is active. Importing records with no operational purpose adds cleaning work to a migration that already requires careful verification.