Import CSV, JSON or Excel into a table

You can bring rows into any table from a file: a CSV, an Excel workbook (.xls or .xlsx) or a JSON list. Before anything changes, you choose where each column of the file goes and whether rows that match ones already in the table are updated or added as new.

Before you start

• Importing works the same on every plan.
• You need a Table widget to import into. To start a new table from a file, add a Table widget first (click Add widget in the header, then Numbers & data > Table) and import into it. See Table widget.
• PlexiDesk reads these files:

Empty lines are ignored, and a column with no name in the file is called Column 1, Column 2 and so on. A JSON file looks like this:

[
  { "Item": "Walking boots", "Category": "Clothing", "Packed": "yes" },
  { "Item": "Water bottle", "Category": "Kit", "Packed": "no" }
]

Import a file

1. Right-click the table's title bar and choose Import CSV, JSON or Excel….
2. Choose your file and click Open.
3. The Import into table window opens. At the top it shows the file's name and how many rows and columns it has.
4. Under Map columns, check where each column of the file goes. See "Map columns" below.
5. Under Match rows on, choose whether rows update or are all added. See "Update matching rows or add everything" below.
6. Read the summary at the bottom of the window, for example 12 to add, 3 to update, 2 new columns.
7. Click Import and the number of rows, for example Import 15 rows.

The button says Importing… while it works, and the window closes when the rows are in. To stop without importing anything, click Cancel, click the ×, or click outside the window.

Map columns

Each column of the file has a box beside it. Choose one of these:

• An existing column, listed under Existing column: the file's values go into that column.
• Create new column: adds a column named after the file's column. A second box appears for its type: Short text, Long text, Number, Checkbox or Date.
• Skip this column: leaves that column of the file out.

PlexiDesk fills in a starting choice for you. A file column with the same name as a table column goes into that column (capital letters do not matter). Every other file column is set to Create new column, with a type suggested from the first rows of the file: Number when every value is a number, Checkbox when every value is true, false, yes or no, Date when every value is a date, Long text when some values are long, and Short text otherwise.

How values fit each type of column

Update matching rows or add everything

Match rows on decides what happens to rows that are already in the table.

• Append all as new rows: every row of the file is added to the table.
• A column, such as an order number or an email address: a file row whose value in that column matches a row already in the table updates that row. Every other file row is added. The window reminds you: "rows with a matching value update in place; the rest are added".

PlexiDesk starts with the first file column that maps to an existing column, or with Append all as new rows if none does. Matching ignores capital letters and spaces at either end of a value.

A few things to know:

• When a row is updated, every mapped column takes the value from the file, including empty values. To leave a table column as it is, set its file column to Skip this column.
• If the same value appears twice in the file's match column, those rows are combined into one, and the later row's values win.
• File rows with an empty value in the match column are added as new rows.
• Matching on a column you are creating in this import adds every row, because the table has nothing in that column yet. New columns are labelled (new) in the list.

The summary line

The line at the bottom of the window tells you what will happen:

• to add: rows that will be added to the bottom of the table.
• to update: rows already in the table that will be updated.
• new columns: columns that will be created.
• empty skipped: rows of the file with nothing in any of the columns you are importing. They are left out.

If there is nothing to import, the Import button is greyed out.

After you import

• New rows appear at the bottom of the table, and new columns at its right end.
• The rows an import added come off together in one step: click an empty part of the desk and press ⌘ Z on a Mac or Ctrl Z on Windows. Undo does not restore values the import updated, and any new columns stay, so check your mapping before you import into rows you already have. See Undo and redo.
• To remove a column the import created, right-click its header and choose Delete column.

If the file will not import

If PlexiDesk cannot use the file, a message appears across the top of the table instead of the window:

Other kinds of files

• Word documents: add a Document widget, which can import a Word .docx file. See Document widget.
• Spreadsheets you want to work on with formulas: add a Spreadsheet widget instead. It can import an Excel .xlsx or a .csv file. See Spreadsheet widget.
• Taking rows out of a table: select cells in the Table view and press ⌘ C on a Mac or Ctrl C on Windows. They paste into a spreadsheet as rows and columns. See Move around a table and copy cells.

Related tasks

• Table columns and field types
• Build a table with AI
• Move around a table and copy cells
• Six views of every table
• Table widget
• More about this widget Coming soon