What happens when you import a spreadsheet into Appdor
Most teams do not start with an empty database. They start with a spreadsheet that everyone edits and nobody fully trusts. This is what happens to that file when you bring it into Appdor, including the parts that do not come across.
Getting the file in
In an application, the Add table dialog includes From Scratch, Import a Spreadsheet and Paste data among its options. The file picker accepts CSV, TSV and plain text files, JSON, and Excel-family workbooks (.xlsx, .xls and .ods). A file from Google Sheets works the same way: export it as .xlsx or .csv first, as the interface hint says.
Two behaviours are worth knowing before you pick a file.
If the workbook has several sheets, Appdor asks Which sheet? and tells you that only the sheet you choose is imported. It does not quietly take the first one. Dismissing the dialog cancels the import.
The importer also checks what the file actually is, not just what it is called. A file named .csv that is really a zip archive is refused with a message telling you to rename it or export it again. For a text file whose encoding it cannot determine, it asks you to pick one.
There is a second door for whole workbooks. Under Start from a template, the Import from another tool section includes Import from Excel / Google Sheets (upload a file), where each sheet becomes its own table.
Paste data is the small-scale route. You copy cells from a spreadsheet, paste them in, and the first row is treated as the header. The dialog says nothing is uploaded; the paste goes through the same preview as a file.
The preview is where you decide
Nothing is written until you have seen the Import preview. It shows the table name, the row and column counts, and for every column an editable name and a type selector. The types on offer are text, longtext, number, currency, percent, checkbox, date, datetime, email, url, phone and select.
The types are proposed from the values in each column, and the code is explicit that every proposal is a suggestion you can override. Several of its choices are the kind a person would otherwise get wrong:
- A column with only a few distinct values, repeated, is proposed as a select.
- A column of day/month/year dates is decided by evidence:
25/12/2024proves day-first because there is no twenty-fifth month. If nothing in the column settles it, the preview says "Day or month first? Nothing in this column settles it" rather than guessing. - The same applies to decimal separators. A column where every number reads like
1,234could be a decimal comma or a thousands separator, so it is flagged "Decimal separator unclear — check this column before importing", because the two readings differ by a factor of a thousand. A column that uses both conventions is flagged as used inconsistently. - Values with a leading zero, such as postcodes and phone numbers written the national way, are not read as plain numbers, because the zero could not be recovered afterwards.
Each cell in the preview shows the value that will be stored, and where that differs from the file's own text, the original sits underneath it. Cells that do not fit their column are marked "does not fit — will be dropped". Before writing, if some values do not fit, Appdor offers to import those rows anyway with the cell left blank. Otherwise the whole row is skipped.
If you will import similar files again, Save as template keeps the column mapping for next time.
What does not come across
A spreadsheet has no schema, so the types above are inferred, not declared. That is the first thing to review.
Formulas are the second. The workbook importer's own note says a formula column "was a formula; its last calculated values were imported and will not recalculate". Cell addresses such as C2 or SUM(D2:D40) do not translate into a table with no cell addresses. You will need to rebuild those calculations as formula fields.
Relationships are the third. If a formula looks up another sheet, the workbook importer does not create a link on its own. It raises a note, "looks up … — confirm this link before it is created", and leaves the decision to a person. Rows above a header row are skipped with a warning, and blank or duplicate headings are renamed.
Adding rows later
The first import creates a table. After that, the table's Import rows… action brings in more rows from a .csv, .tsv or .xlsx file. You map each file column to a field (or choose "Do not import"), choose what to match existing records on, and choose what happens when a value does not fit: leave the row out or blank the cell. The preview counts rows to add, update, skip and reject before anything changes. After the run, an Import receipt reports what was added, updated and failed, and offers errors.csv and report.csv to download. If you match on nothing, every row is added as new.
Larger imports can run in the background. The interface then tells you that you can close the page, with progress in Import history, where a job can be paused, resumed or cancelled. Smaller imports offer Undo this import? at the end. The background route has no browser left to ask you, so plan for the undo question before you start a large one.
A sensible first test is modest: import one sheet, correct the types, spot-check a few records against the source, and only then bring in the rest.