How to turn a spreadsheet into a business app without losing what you have
SynaptaGrid8 min read
In short
To turn a spreadsheet into a business app, treat each row as a record and each column as a field. Clean the sheet first: one header row, one kind of thing per sheet, and one spelling for each value. Decide the fields and their types, save the sheet as a CSV file, import a small sample, check it against the original, then import the rest and keep the original file untouched. Afterwards, set who may see and change the records and switch history on, so the next wrong edit can be undone. Most of the work is in the cleaning and the decisions, not in the import itself.
Read the transcript
A supplier order sheet: 12 rows, 8 columns, and no database yet.
In Data, create a schema and choose Upload a file.
Pick the CSV. Each sheet becomes its own record type.
The columns become fields, and the dates are recognised.
Create the schema and import the rows in one step.
It shows how many rows came in, and why any were refused.
Every order is now a record.
Open Filters and pick a field.
Filter on Delivery status = Delayed.
The three delayed orders.
Reports start from a template.
Count the orders for each supplier.
Run the report.
Nordlicht GmbH has the most orders: four.
The seven steps
The steps are the same whichever tool you move to. The order matters, because mistakes are cheapest to fix before the first import.
- Copy the original
- Keep the current file exactly as it is. You will compare against it, and you can go back to it.
- Clean the sheet
- One header row with one name per column. One kind of thing per sheet: suppliers in one, contacts in another. Remove merged cells, blank separator rows and notes typed into the grid.
- Make values consistent
- Pick one spelling for each repeated value ("Approved", not "approved" and "OK"), one format for dates, and one unit for each number.
- Decide the fields
- For each column choose a type (text, number, date, yes or no) and the rules: required, unique, or a fixed list of choices.
- Import a sample
- Save the sheet as a CSV file and import ten or twenty rows first. Check every field against the original, especially dates and numbers.
- Import the rest
- Run the full import. Compare the count of rows that went in with the count in the sheet, and look at any rows that did not.
- Set access and history
- Decide who may read and who may change each kind of record, and switch history on so each edit keeps the version before it.
From spreadsheet to records
The problems that show up, and what to do
Most failed conversions fail in the same few places. All of them are visible in the sample import.
| Problem | What it looks like | What to do |
|---|---|---|
| Mixed spellings | The same status appears as "Approved", "approved" and "OK" | Replace them with one value, or define a fixed list of choices for the field |
| Dates in several formats | Some dates import as text, or day and month swap | Write every date in one format, ideally year-month-day (ISO 8601), before exporting to CSV |
| Numbers stored as text | Leading zeros vanish, or totals will not add up | Mark the field as text when the leading zeros matter, as number when they do not |
| Two things in one column | A cell holds a name and a phone number together | Split it into two columns before the import |
| Duplicate rows | The same supplier appears twice with small differences | Pick a unique field, such as a supplier number, and merge the duplicates first |
| A row that will not go in | The import reports fewer rows than the sheet holds | Compare the counts, find the rows that did not go in, fix them in the sheet and import them again |
The old way and the new way
People often try to convert by copying cells into a new tool one at a time. The table compares that with importing a cleaned file.
- Moving the data
- The old way: Retype or paste cell by cell and hope nothing is missed
- The new way: Import a cleaned CSV file and compare the row count with the sheet
- Checking the result
- The old way: Scroll through both and eyeball them
- The new way: Import a small sample first and check each field against the original
- Rules
- The old way: Added later, after bad values are already in
- The new way: Decided before the import: required fields, unique values and fixed choices
- Going back
- The old way: The old file is overwritten or lost
- The new way: The original is kept, and history keeps the version before each later edit
- Sharing
- The old way: Email the new file
- The new way: Give each person access to the records they need
How we decided
In SynaptaGrid Data the import takes a CSV file, turns each row into a record, and reports how many rows went in and how many did not. In the example we ran in our own workspace, both rows of the test file went in, and reading them back showed the right values.
We recommend CSV because every spreadsheet can save it and it carries no formatting that could change what a value means. We chose to report counts rather than to hide failures, because a silent partial import is the worst result: the team believes the data is complete when it is not. The cost is that you have to compare the counts yourself.
We also chose not to promise more than we have run. In November we imported an Excel file of thirty rows and thirteen columns into our own workspace: creating the record type from the file worked out every field type correctly but loaded no rows, and a second step, Import data, then loaded all thirty rows with values matching the file. So it takes two steps today. We have not run files with merged cells, several header rows or thousands of rows, and we have not verified how the import reports the reason for a rejected row, so this guide does not describe one: finding a rejected row means comparing against the sheet.
The records you import are yours to take back out. Export gives you the records, as CSV or JSON, and the identifiers the product adds to each one.
Standards and accepted practice behind this
These are the practices the steps rest on. We align with them. This page does not claim a certification.
- CSV, RFC 4180
- One header row, one record per line, values separated by commas and quoted when they contain a comma or a line break.
- Source: IETF: RFC 4180 (opens in a new tab)
- UTF-8
- Save the file in UTF-8 so names with accents and other alphabets stay intact.
- Source: IETF: RFC 3629 (opens in a new tab)
- ISO 8601
- Write dates as year-month-day, so a date cannot be read as day and month swapped.
- Source: ISO: ISO 8601 date and time format (opens in a new tab)
- Keep a source of truth until verified
- Do not retire the original until the row counts and a sample of records match.
Where SynaptaGrid fits
SynaptaGrid Data imports a spreadsheet saved as CSV, turns each row into a record with a form to edit it, and lets you set permissions for each kind of record. Switch history on for a kind of record and each edit keeps the version before it, so an earlier version can be restored.
History is something you switch on per kind of record and it keeps a limited number of versions, so switch it on before the records start to change. The spreadsheet example shows this on a real record type we ran in our own workspace.
Frequently asked questions
- How do I turn an Excel spreadsheet into a business app?
- Clean the sheet so it has one header row and one kind of thing per sheet, save it as a CSV file, decide the field types and rules, import a small sample, check it against the original, and then import the rest.
- Will I lose my formulas?
- A CSV file holds values, not formulas, so a calculated column is saved as the numbers it showed. Decide whether a column should stay a stored value or become a calculation in the new system before you import it.
- What should I do before importing?
- Keep a copy of the original, give each column one name, use one spelling for each repeated value, write dates in one format, and remove merged cells and notes typed into the grid.
- Can I keep the spreadsheet afterwards?
- Yes. Keep the original until the row counts and a sample of records match. You can also export the records back to CSV whenever someone needs a spreadsheet.
SynaptaGrid
Import your spreadsheet and see it as records
Start with a small sample of your own list and check it against the original.