NEWBY: How to divide the columns of a large Excel sheet over several Data Properties of different Data Items

Support
1

Is there a way to divide the columns in a data sheet over several Data Properties of different Data Items?

I have a large Excel sheet with all kinds of data. The data in the Excel sheet have some relations between columns. So I made a normalised data model with several Data Items and their connections. I am able to import the full excel sheet, but I would like to put several columns from the Excel sheet as Data Properties in different Data Items. Is there a way to import them directly into these Data Properties? Or do I import the full Excel sheet and then put the data into the different Data Properties? And how would I do that?

Thanks!

2 Likes
2

Hi again,

The way Triggre works with Excel sheets is that each worksheet represents a data-item. It is possible to import an Excel file with multiple datasheets at once. Simply redistribute your columns from the one datasheet to different datasheets that have been named after the data-items you have created in your normalized model. Move or copy each column in the right datasheet, according to your datamodel in Triggre.

For example:
Say you have a datasheet named “Customers” containing personal and address info and you want to store the personal data in a data-item Person and the Address in a related data-item Address. You create two additional datasheets, named “Person” and “Address” respectively.
Then select all columns containing the data you want to store in Person and move them to the “Person” datasheet. You do the same with the columns containing the Address data and move those to “Address”. Of course you could also re-use the “Customers” datasheet by renaming it to “Person” and only move the Address data to a new sheet. Whatever works best for you.
Now you need to create a relation between the two sheets, to tell Triggre how to reference them. You do this by using a unique property in Person to point to from Address. This can be any property, as long as it is unique. In this example we use the property Name as the ID for the Person. In you data-item Address you have created a reference property with the name “ofPerson”. In your “Address” datasheet, add a column with the name ofPerson_Name. In your “Person” datasheet you add the extension “[ID]” to the columnheader Name, thus naming it Name[ID].

This way, Triggre wil import the “Person” sheet first, leaving the reference to Address empty. Then it will import the “Address” sheet, creating a reference between the created Address and the already present Person by using Name as an identifier. This will also set the reverse reference in Person.

However, depending on the number of datasheets you end up with this is a complex way of importing, and there are some things you need to consider when doing this:

  1. When importing, Triggre can only handle references to records that already have been imported. So in our example you place the “Person” sheet before the “Address” sheet. Otherwise Triggre wil produce an error because the referenced value couldn’t be found. When using more datasheets, use the right order to import them to prevent these errors.
  2. Make sure that all data-items have a unique ID column (when using ‘duplicates not allowed’). If they don’t, add one and fill it. Note that the ID column also needs to exist in the data-item in Triggre!
  3. If you are copying columns from your ‘master’ datasheet into the datasheets you want to import, make sure to delete the ‘master’ sheet before importing. Also make sure to transform all formulas in your datasheets into values before removing the ‘master’ sheet to prevent unexpected errors in your data. If you are re-using your ‘master’ sheet as one of the data-item imports, this of course is not necessary.
  4. You do not have to use all your defined properties in the datasheet, however the columns you do use must have a name that refers to a property in your datamodel.

Important: The example and these remarks apply to importing data using ‘duplicates not allowed’.

Hope this helps!

3 Likes
3

Wow! That looks like a lot but not too complicated. I am going to build a test set to try to make this work! Once again, thanks a lot for your quick answer!

3 Likes