Excel Import- Allow ( , Comma) separated values and allow only number for certain columns in the excel

0
Hi everyone, we have a requirement where we are importing financial data using excel import. The excel contains yearly columns for entering numbers (Financial info). If we insert comma separated value for eg: 2018 yr= 12,100  . It only imports 12 from the entire value and skips the value after comma. Need a solution to allow the entire value with comma. Also we are calculating Total of this yearly data from 2018 to 2020. The second point is that it is allowing characters like “abcd” to be inserted and after importing it is converting this abcd value to a number i.e “12” or “11”. Need a solution that it will validate only for numbers in this 2018 to 2020 columns , and not take characters or special symbols.   If anyone knows how to achieve this 2 requirements, Please Let me know.      
asked
1 answers
0

To my understanding, you are importing a CSV into a mendix persistent entity with a template matching to the entity's attribute types (integer/decimal, string, etc)

But for the integer (or decimal field) thousands grouping commas are breaking it.

 

My suggestions, (if possible) remove thousands grouping in the excel while exporting the CSV. If that is not an option, or you have existing files to process; I would recommend to create a non persistent helper entity which has similar attributes but all of them are as string. Then modify your existing excel import templates & microflows to use this new helper entity. This way, mendix will import our CSV without parsing / processing and create non-persistent entities.

 

Afterwards, you can loop over the non persistent entity list and parse numbers & strings with your own microflow.

 

for the thousands grouping number for example;

1) remove comma from the value string

Create String variable ($CommaStrippedValue)

    $CommaStrippedValue = replaceAll($NonPersistentHelper/Value, ',', '')

 

2) parse it into Integer or Decimal (which ever you have in your original entity)

Create Object -> $NewPersistentEntity

 Attribute Value = parseInteger( $CommaStrippedValue)

answered