Cleaning Your Spreadsheet Before You Import

Most bad imports aren't the software's fault. They carry a date column in three formats and a price field with a currency symbol inside it.

Last updated: October 25, 2026

A spreadsheet that a person reads is forgiving. It doesn't mind that one row says 12/03/2026 and the next says 2026-03-12, because a person reads the whole row and works it out. Software doesn't work it out. It reads each column as one type, and the moment two formats sit in the same column, it either refuses the rows or guesses wrong. Cleaning the sheet is the part of an import you control.

Key Takeaways

• Most import failures are data failures. The file carries mixed formats, and the receiving system can only read one.
• Four columns break first. Dates, rates, unit names and availability status account for most of the damage.
• One format per column, no exceptions. A single stray row is enough to change how the whole column is read.
• Dry-run ten rows. Ten chosen deliberately will find what ten thousand chosen at random will miss.
• Keep the raw file. The cleaned version is a working document; the original is your evidence.

The four columns that break an import

Four columns cause most of the trouble, and they cause it in a predictable order.

The first is the date pair — check-in and check-out. A sheet built by hand over two years will contain at least three conventions: day-first, month-first and ISO. Some cells will be text, some will be real dates, and one will be a number like 45000 because somebody typed a date into a general cell.

The second is the rate. Nightly rates arrive with currency symbols, thousands separators, a comma where a decimal point should be, or a trailing currency code. To a system expecting a number, all four of those are text.

The third is the unit name. This is the column people trust most and check least. Unit A, unit a, Unit A with a trailing space, and Unit-A are four different strings, and a system that groups by name will create four units.

The fourth is availability or status. Open, closed, blocked, available, yes, no, 1, 0. Each of those means something to the person who typed it and nothing consistent to anything else.

There's a fifth that isn't a column: the header row. Merged cells, two header rows, a title in row one. If the header doesn't sit in one clean row, everything below it shifts by one.

Dates, prices and unit names

Take the columns one at a time. Not the rows — the columns.

For dates, pick one convention and convert the whole column to it. ISO is the safest choice because it can't be misread: 2026-03-12 is the twelfth of March everywhere. Then check the column's type, not just its appearance. A cell that looks like a date and behaves like text will arrive as text.

For prices, strip everything that isn't a digit or a decimal separator, then decide what the decimal separator is. A sheet that mixes 1,250.00 and 1.250,00 can't be fixed by eye. Pick one, convert, and check the column total against the number you expect.

For unit names, the fix is mechanical. Trim leading and trailing spaces, collapse double spaces, settle on one case, and remove anything that isn't part of the name. Then sort the column and look for near-duplicates sitting next to each other. Sorting is the cheapest duplicate detector there is.

For status, replace words with a single vocabulary you'll use everywhere. Pick one term per state, apply it down the column, and delete the rest. If two words mean the same thing in your sheet, they'll mean two things after the import.

One rule covers all four. Convert the column, then sort it, then read the top and bottom ten rows. Errors cluster at the edges, where old data meets new.

This matters more in a calendar than in a sheet, because a calendar doesn't show you the column. It shows you a night that's open or closed. localsbnb.com connects Airbnb, Booking.com, Agoda and Trip.com into one calendar, so a date that lands on the wrong night shows up as a booking on a night you'd blocked — visible, and next to every other unit.

A dry run on ten rows

Don't test with the whole file, and don't test with ten random rows. Ten chosen on purpose.

Build the ten from the trouble spots. Take the oldest row, then the newest. Then one with a date in a different format, one with a currency symbol, one with a trailing space in the unit name, and one with a blank rate. Add one with a status word you hardly use, one for Unit B, one with a rate change partway through a stay, and one you know was edited by hand.

Put those ten in a separate copy of the sheet, in the same column order as the full file. Move them first, look at what arrives, and compare it against what you sent. You're checking four things: did every row arrive, did the dates land on the right nights, did the rates come through as numbers, and did the units group correctly.

The comparison is the part people skip. A dry run that only asks "did it work" tells you almost nothing. A dry run that puts the sent rows next to the received rows tells you which column is lying.

Then fix the sheet, not the import. Every time you're tempted to correct a row after it lands, ask why the row left the sheet in that state.

What to keep after the import

Keep three files, and keep them separate.

The raw original, untouched, with its original name and the date. That's your evidence if a number ever looks wrong six months later. Don't clean it in place — a spreadsheet that's been edited five times has no baseline.

The cleaned working copy, with the conventions written at the top: which date format, which decimal separator, which vocabulary for status. Anyone who opens it later, including you, needs those three answers before the first row makes sense.

The mapping note, one page. Which sheet column became which field, and what you dropped. Imports fail quietly at the mapping stage more often than at the format stage, because a column can be perfectly clean and still be going to the wrong place.

Then check the calendar, not the file. The file proves what you sent; the calendar proves what the channels received. That's the argument for holding every unit in one place after the move. Unit A and Unit B, side by side, instead of channel by channel. A night that closed in the wrong unit becomes something you can see, rather than something you hear about from a guest.

One last habit worth keeping: a change log at the bottom of the cleaned sheet. Date, what changed, why. Three lines a month. It costs nothing now and answers the question you'll actually ask later, which is never "what's in this cell" but "why is this cell different from the one next to it".

FAQ

Why did only some rows fail?

Because the column was read as one type. The receiving system looks at the column as a whole, decides what it is, and then rejects or converts the rows that don't match. One text cell in a column of dates is enough to make the whole column behave differently.

Can I import straight from the platform's export?

You can, and it's usually better than a hand-built sheet, because the export has consistent types. It still needs a pass: exports carry extra columns, platform-specific status words and timestamps you don't need, and those are exactly what a mapping note is for.

What if the sheet has years of history?

Import the forward calendar first and leave the history out. Old reservations are rarely needed to run the next ninety days, and they're the rows most likely to carry the format drift. Keep them in the raw file and add them later if a report needs them.

A clean sheet isn't a formality before the real work. For most hosts it is the real work, and the move itself takes minutes. Once Unit A and Unit B sit in one calendar beside every connected channel, localsbnb.com makes the next import a smaller job, because there's one place to check instead of four.

---

This article is general guidance for hosts and isn't legal, tax or insurance advice. Spreadsheet conventions, import behaviour and platform data formats differ between tools and change over time. Confirm the accepted format with the provider and check the current terms of each platform you connect before you move live data.