How can I import data from Excel?

How can I import data from Excel?

You can import data from Excel using the "Add many records" function in a type view, with tabular data.



To help you structure array data in Excel, here is a sample function to create a JSON list from a new-line-separated cell. Swap the semicolons, ";", with comma ",", depending on the language settings you use in Excel: 
 =IFERROR(IF(FIND(CHAR(10);C2);"["""&SUBSTITUTE(C2;CHAR(10);""",""")&"""]";IF(C2<>"";C2;""));IF(C2<>"";C2;""))

For importing traits (called subdocuments in TerminusDB), this is an example of what you put in a cell for structured data (this example is for the markdown property of the "Statement" type that is part of the standard type library. Adapt to your type and data, it's in JSON/JSON-LD format.

{"@type": "Markdown", "markdown":"# Source\n\nImported text in JSON Document"}

Each row in the "Add many records" tabular data importer is converted into JSON format, and the type set by the type you are adding instances of. All data is type-checked as you enter it, including the JSON data provided. The error paned shown in the video is an example, and was triggered because the type "Note" was not "LinkedNote" that the Excel file was formatted for.

Note that the table shows row numbers starting with 1, whereas the type-checker starts uses row index starting with 0. Not fixed yet, and something to be aware of.

A simple workflow for importing data:

  1. If you are unsure, you can always create a branch to try in isolated mode.
    1. Remember to create a data product pointer and switch to the new branch.
  2. Create any data structures to reuse (Traits, Enums)
  3. Create a type to create instances of, with fields, referencing Traits and Enums as necessary)
  4. Show the Type view, and in Actions, click "Add new records"
  5. Copy the data you create from Excel, or create it manually
  6. Fix any import errors shown in the error window if any
  7. Click Add
  8. Your records are now created
  9. If you need to undo and redo the operation:
    1. go into Version Control of your data product
    2. use branching
    3. reset to a specific point in history to start over from where you were before.

Importing data is useful in a number of scenarios!