AI App With Database

Spreadsheet to database: when to move and how to migrate

The signs that a spreadsheet has become a database in disguise, the hard limits of Excel and Google Sheets, and a step-by-step migration plan that keeps your data intact.

Floot ·

Key Takeaways

  • Size is rarely the first problem. Excel allows 1,048,576 rows per sheet and Google Sheets up to 20 million cells. Most teams move because of duplicate records, edit collisions and missing permissions long before they hit those limits.
  • Repeated data is the clearest signal. If the same customer, product or address is typed on many rows, you are maintaining a relational database by hand.
  • Design first, import second. Split the sheet into tables on paper before moving anything. Importing a messy sheet into a database just gives you a messy database.
  • Clean in the spreadsheet, not after. Fix spellings, dates and duplicates while the data is still in a tool you know, then export a clean CSV per table.
  • Run both for a short overlap. Keep the old sheet read-only for a week or two, compare totals, then retire it so there is one source of truth.

Quick-Start Prioritization Framework

Your situationStay in a spreadsheet?Suggested move
One person, one list, mostly readingYesAdd data validation and a backup copy
Several people editing the same rows dailyProbably notShared database tool with record-level history
Same customers or products repeated on many rowsNoRelational database with linked tables
Customers or staff need their own logins and viewsNoAn app with a database and accounts
Numbers feed invoices, payroll or inventory countsNoDatabase with required fields and checks

Spreadsheets run a remarkable share of small businesses, and for good reason: they are free, flexible and everyone knows how to use them. The trouble starts when a sheet quietly turns into a system. Several people edit it, other sheets pull from it, and one bad paste can wreck a month of records. This guide covers how to tell when it’s time to move, what to move to, and a migration plan you can follow in an afternoon for a small sheet or a week for a messy one.

Signs your spreadsheet has become a database

A spreadsheet is a grid that trusts its users. A database is a set of linked tables with rules. When you find yourself inventing rules by hand, you have outgrown the grid.

You copy the same details onto many rows

An order sheet with the customer’s name, phone and address on every order is the textbook case. Each copy is a chance for a typo, and a change of address means hunting every row. In a database, the customer lives on one row and each order points to it.

People overwrite each other

Sorting while a colleague is typing, pasting over a filtered range, or two people editing the same cell are everyday spreadsheet accidents. Databases write one record at a time and can keep a history of who changed what.

You need different views for different people

If you’ve made copies of the sheet so that each salesperson sees only their accounts, or you are nervous about giving a contractor access to the whole file, you need permissions at the record level. Spreadsheets share files; databases and apps can share rows.

Lookups are holding things together

A web of VLOOKUP or XLOOKUP formulas between tabs is a relationship being simulated. It works until someone inserts a column or renames a tab.

The hard limits worth knowing

Most small businesses move for the reasons above, but the technical ceilings are real, and some catch people by surprise.

LimitExcelGoogle Sheets
Rows per sheet1,048,576No separate row limit stated; counts toward cells
Total sizeColumns up to 16,384 per sheetUp to 20 million cells or 100 MB
Number precision15 significant digitsSee Google’s documentation
Long textSee Microsoft’s documentationCells over 50,000 characters removed when converting from Excel

The 15-digit trap

Microsoft’s documentation says Excel keeps a maximum of 15 significant digits, and digits past the 15th become zeros. Long account numbers, tracking numbers and card-like IDs can be silently changed. The same page explains that Excel removes leading zeros by default, so a ZIP code like 02139 becomes 2139. A database column defined as text keeps both exactly.

What to move to

There are three broad destinations. The right one depends on who uses the data and whether it needs to power an app.

OptionGood forTrade-off
Spreadsheet-style database tools (Airtable, Baserow, NocoDB)Teams that want linked tables in a familiar gridPaid plans usually priced per user, with record limits per plan
An app with its own databaseCustomers or staff who sign in and see their own dataTakes more setup, or an app builder that does it for you
A hosted SQL database (PostgreSQL, MySQL)Developers, or when you already have oneYou need someone to build the screens and logins

Ask who will touch the data

If only your internal team edits records, a spreadsheet-style database is often enough. If customers, drivers or clients need to sign in and see only their own records, you need an app with accounts and permissions on top of the database.

Step 1: Design the tables before you move anything

Take the main sheet and list every column header. Group the columns by the thing they describe. Each group becomes a table.

A worked example: a cleaning company’s jobs sheet

Say the sheet has these columns: Date, Client, Client phone, Address, Cleaner, Cleaner phone, Service, Hours, Rate, Paid. Grouping them gives four tables. clients: name, phone. properties: client, address (a client can own several). cleaners: name, phone. jobs: date, property, cleaner, service, hours, rate, paid. Client phone and Cleaner phone now live once each, and a client with three properties no longer has to be typed three times.

Choose IDs

Each table needs a unique, permanent ID. If your sheet already has customer numbers, keep them. If not, let the new system generate them and use them to link tables during import.

Step 2: Clean the data while it’s still in the spreadsheet

Cleaning is faster in a tool you already know. Make a copy of the file and work on the copy.

  • Trim spaces and fix capitalization so “Acme Ltd ” and “acme ltd” match.
  • Merge duplicate clients and decide which phone and email are current.
  • Convert every date to one format, ideally YYYY-MM-DD.
  • Replace free-text statuses with a short fixed list, such as scheduled, done, cancelled.
  • Split cells that hold lists into separate rows.
  • Fill or flag blanks in fields that will be required.

Protect IDs and codes first

Before cleaning, format columns like ZIP codes, phone numbers and account numbers as text. Microsoft’s guidance is to import such columns as text so Excel doesn’t strip leading zeros or round long numbers.

Step 3: Export one CSV per table and import in order

Create a separate sheet for each table, with one header row whose names match the new system’s field names. Export each as CSV in UTF-8. CSV is a simple format, described in RFC 4180: commas separate fields, and a field containing a comma, quote or line break is wrapped in double quotes. Spreadsheet exports handle this for you, but it is why a stray line break inside a notes cell can still confuse a careless importer.

Import parents before children

Tables that others point to go first. In the cleaning example: clients, then cleaners, then properties (which point to clients), then jobs (which point to properties and cleaners). If you import jobs first, there is nothing for them to link to.

Map names to IDs

In the jobs CSV, replace the client name with the client’s new ID. A lookup in the spreadsheet does this quickly. Linking by name is fragile: two clients named “Smith” will merge.

Test with a small batch

Import 20 rows first. Open several and compare them with the source. Check dates, money amounts, accented names and anything with a leading zero. Then import the rest.

Step 4: Verify, overlap and switch

A migration isn’t finished when the import succeeds. It is finished when people trust the new system.

Reconcile totals

Compare row counts per table and a few sums, such as total hours last month and total unpaid amount. If the spreadsheet says 1,214 jobs and the database has 1,209, find the five before going further.

Make the old sheet read-only

For a week or two, keep the spreadsheet available but locked. People can check old records but can’t add new ones there. Then archive it. Two live copies of the truth is the situation you were trying to escape.

Set up backups on day one

Find out how your new system exports data and how far back it can restore. Download a full export after the migration so you have a clean starting snapshot.

Common mistakes

Most failed migrations fail for the same handful of reasons.

Importing the sheet as one big table

It feels faster, but every duplicate and inconsistency comes along, and you lose the main benefit of moving.

Opening the CSV in Excel before importing

Double-clicking a CSV lets Excel reformat dates and strip zeros, and saving it bakes those changes in. Inspect CSVs in a text editor or import them through Excel’s Get Data, From Text/CSV option with columns set to text.

Leaving the old sheet editable

Someone will keep updating it out of habit, and the two will drift within days.

Skipping permissions

Moving to a database is the moment to decide who sees what. Don’t recreate “everyone can edit everything” by default.

If you’d rather not do the migration yourself

If you’d rather hand off the build, AI app builders such as Floot let you upload the cleaned CSV and ask the AI to create the tables and import it into the app’s own Postgres database, with logins on top. The design and cleaning steps above still apply, and they make that import go far more smoothly.

Frequently Asked Questions

When should I move from a spreadsheet to a database?

When several people edit the same data, the same details are repeated across many rows, you need different people to see different records, or mistakes in the sheet now cost money. Row limits are usually the last reason, not the first.

How many rows can Excel and Google Sheets handle?

Excel allows 1,048,576 rows by 16,384 columns per worksheet. Google Sheets allows up to 20 million cells or 100 MB for spreadsheets created in or converted to Sheets. Performance often degrades well before those limits with heavy formulas.

Can I keep using a spreadsheet as the front end to a database?

Some tools sync a database to a spreadsheet view, and many database tools look like spreadsheets. The important part is that one system holds the authoritative copy and enforces the rules.

Will I lose my formulas?

Formulas don’t migrate as formulas. Calculated values like totals are usually recomputed by the database or app from the underlying rows, which is more reliable than stored formulas.

How do I stop Excel removing leading zeros from my CSV?

Don’t open the CSV by double-clicking. Use Data, From Text/CSV and set those columns to Text before loading, as Microsoft’s support article describes, or check the file in a plain text editor.

How long does a spreadsheet migration take?

A single clean list can move in an hour. A multi-tab sheet with years of duplicates usually takes several days, most of it spent cleaning and checking rather than importing.

Sources