Example or procedure to Import/Export content that includes relational data

Help
1

The docs state that import of relational data from a file via the Data Studio is supported, but having trouble getting this to work:
Info note on this page: Import Export | Directus Docs

3

I think importing relational data as it stands, while it works and makes sense, is very complicated when you need to do it often.

Example:
Suppose there are

  • Lockers (static in their nature) and
  • Students (dynamic, specially at the start of the year).
  • Departments (dynamic, specially at the start of the year).

Before a new school year starts I receive all the students, departments they belong to and assigned lockers. So here is my procedure right now to make it all work:

  • import student data, without any lockers and department information, just basic values that are defined for a Student collection (Name, Surname….)
  • import departments
  • export Students to CSV (since I need to update them with Lockr and Department)
  • export ID and Name of Lockers
  • export ID and Name of Departments
  • create new Excel/Google Spreadsheet
  • import all 3 collections as separate sheets
  • add two more columns in Students sheet, called department_id, locker_id
  • use XLOOKUP on Students for locker_id and department_id to find their IDs from the other sheets, based on Students sheet “Locker” and “Department” values
  • save as CSV
  • import

This could be so much simpler, if importer would be smart enough and find related ID based on the actual value, not just simply updating the fields with values from the CSV. Usually, importers also provide mappings, where one can select which columns to map to what and don’t need to know column names from DB in advance.

And this is just a “simple” example.
I also have Assets… these re specially problematic as they have a lot more relations.
They have an asset type association, belong to rooms, have network IP relations….
And I have to export all of them, do XLOOKUPs - it really is a lot of work, and it is quite often.

Not to mention, usually there are two types of users in these environments.
Admins (like me), who build the collections, flows, logic… and others, actually dealing with data.
I can’t see a world where they are smart enough to do all this on their own, so I need to do it for them.

Maybe I am missing something and there’s a better way of doing it? Simpler, nicer?
I also know some someone will say: but Directus is CMS - I will ignore that :slight_smile: hehe

Thanks for any tips.

2

As far as I know, you can import CSV or JSON files — the only thing you need to do is match or map the columns to the fields of your collection.

Regarding relationships, since it’s SQL-based, you just need to match the ID of the related item in the relationship column. Directus then expands this into a full collection reference. But don’t be mistaken — only the ID is actually stored in the database. :slightly_smiling_face: