Is there a way to import a reference set directly from a column in an Excel sheet?

I have two entities where i try to import a list of entities which reference one or more MainEntites. I have a simple export that shows Name and a list of Codes (reference set) to a main entity. In the Template the Code is set to be Reference to MainEntity.Code (key for only assiciated entity with Ignore if not found). The problem is that Excel Import uses the whole string as Key ("1,2,3" [Key] instead of each "1"[Key], "2"[Key] and "3"[Key]) and then ignores it. I managed to get it to run if I use "Append" on the reference and then do one Code-reference per line, but that is not what I want.    
2 answers


You could import the excel file into a helper entity that mirrors the columns of the spreadsheet.  Once you do that, you can build a microflow to parse through the Reference objects in Column B and create those objects if they don't already exist.



This can be done using the default excel import module without having to create a helper entity and custom microflow. 

According to the FAQ documentation from the XLS Importer:

How do I map reference sets?
This depends on how they are represented in you Excel file. If each element of your reference set has its own row in your sheet, the straightforward mapping should do the right thing. Set the mapping type of the reference columns to “reference”. Set the key type of the columns that form the key values to “Yes, only for the associated object”. The mapping will now automatically make sure no duplicate objects are created. Example of this situation (User N – 0 Group):

Person (key for Person object)    Group (key for Group reference)
John                            Users
John                            Admin
John                            Manager
Jack                            Users
Jack                            Manager


If the reference set is represented by multiple column groups (object entries) on each Excel row, you can select the above mapping for each of the column groups. So, you should have multiple columns mapped to the same object reference and attributes. In this case you should make sure that the key attributes are in the first columns for each reference, otherwise the import will not be correct. Example of this situation (User N – 0 Group):

Person    Group1 (key for Group)    Group2 (key for Group)    Group3 (key for Group)
John    Users                    Admin                    Manager
Jack    Users                    Manager