Skip to main content

Import records from a spreadsheet

Goal​

Load a spreadsheet into a table, without typing the rows in one at a time.

Prerequisites​

  • You can create records in that table. Importing is creating, so if the list has no New button it has no Import data button either.
  • Your spreadsheet is saved as CSV. In Excel: File → Save a Copy → CSV UTF-8.

Get the file ready​

Two things are worth doing before you start, because both are much easier in the spreadsheet than afterwards:

  • Put the column headings in the first row, and name them after the fields. The platform matches a heading to a field by name — including the name without its prefix, so a column called Age finds the field par_age — and every column it recognises is one you do not have to point at by hand.
  • Take out the rows you do not want. Everything in the file is imported.

Blank lines are ignored, so trailing empty rows are not a problem.

Import it​

  1. Open the list you want to add to.
  2. Press Import data on the command bar.
  3. Choose your CSV file. The panel reads it and shows you what it found.
  4. Check the separator. The platform works it out from the heading row and says which it chose: a comma, a semicolon, a tab or a pipe. If your file was saved on a machine that uses a comma for decimals it will be semicolons — and if the whole file has come through as one column, this is why. Change it and the panel re-reads the file.
  5. Check the mapping. Every column is listed with its heading, the first few values actually in it, and the field it is going into. The values are the part to read: a heading only says what somebody called the column.
    • The fields it recognised are already filled in. Change any that are wrong.
    • Leave a column out by clearing its field. A spreadsheet nearly always has a column the table does not have, and that is fine.
    • A field marked with an asterisk is required.
  6. Press Import.

What comes back​

The panel says how many rows became records and how many did not.

A row that fails fails alone. A file of four thousand rows with twelve bad ones imports three thousand nine hundred and eighty-eight of them and hands you back the twelve — with the line number your own spreadsheet shows in its gutter, and a sentence saying what was wrong with each.

Press Download the rows that failed to get those rows as a CSV, with an extra _error column on the end. Fix them in that file, delete the _error column, and import it again: the rows that already worked are not in it, so nothing is duplicated.

What goes in a cell​

The field holdsWrite
TextAnything. Spaces at the ends are trimmed.
A number1234, -7, 12.50. Thousands separators are fine: 1,234.5. A decimal point, not a comma.
Yes/NoYes, No, true, false, 1, 0, Y, N, on, off.
A date2024-03-01, or 2024-03-01 14:30. Gregorian.
A choiceThe option's own words — Open, Closed — exactly as they appear in the list.
A lookupThe name of the record it points at: the account's name, the contact's name.
A multi-table lookuptable:id, e.g. account:7c1f…. A name alone cannot say which table.
A file or a pictureNothing — these cannot come from a spreadsheet, so the field is not offered.

An empty cell leaves the field alone. It does not clear it: the record simply says nothing about that field, so whatever default the table has applies.

A lookup is matched by name, ignoring capitals. If two records have the same name the platform cannot tell which you meant, so those rows fail rather than being pointed at whichever one came back first.

Limits​

  • Ten thousand rows at a time. Each row goes through exactly the same checks a record you typed goes through, which is what makes an import trustworthy and also what makes it take a moment. Split a bigger file and import the pieces.
  • The panel lists the first two hundred failures; the downloaded file has all of them.

Troubleshooting​

  • The whole file came through as one column. — the separator is wrong. See step 4.
  • The first column's heading is not recognised but the others are. — Excel writes an invisible mark at the start of the file. The platform removes it, so if this still happens the heading and the field name genuinely differ; point at the field by hand.
  • Every row failed with the same message. — that is a mapping mistake rather than a data one. Read the first failure, go back, and fix the column it names.
  • A date is refused. — write it as 2024-03-01. A date written the way your region writes it is ambiguous (is 03/04 March or April?), so the platform does not guess.
  • There is no Import data button. — you cannot create records in this table, or your role does not hold the Import data privilege in this environment. Ask whoever looks after your roles.