19
Views
2
Comments
When importing excel - column names matter (sometimes)

This is one interesting observation regarding "Excel to record list" I want to share, because this is not mentioned in documentation.

This article says it somehow matches columns of the excel to the structure, but how exactly? I always thought it simply takes them one by one according to the order. But today I found out that values are not picked up until I change the Excel header name to be exactly as the structure field's Label. After playing a while with it, it turned out that this behavior is involved only if the Excel has different number of columns then the structure has! In my case, I have added a new field to the structure but didn't add it to the Excel - I though it didn't matter because the field went last. But it did. Apparently, there is logic like:

if(ExcelFields.Count == StructureFields.Count) MatchByOrder() else MatchByLabel();

In the second case (matching by label), you can have columns in different order. So there are basically 2 modes to create valid Excel for import:

1. Use exactly the same number of columns put in the same order as the structure fields, and name them anything

2. Use different number of columns, but with exactly same labels, and put them in any order

Thinking about it, maybe this even makes sense. But I wish this was clearly documented! And maybe "Excel to record list" element to have an option which would say which of the modes it expects, or both.


Rank: #68

Very useful information Igor, thank you for sharing.

Champion
Rank: #106

Thanks for sharing, Igor. Very useful. I would recommend to share the feedback on the original  page https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Traditional_Web/Web_Logic_Tools/Excel_To_Record_List