Question

Excel Importer: need suggestions for checks

0

I need to validate the uploaded file to the correct amount of columns but also the correct order based on the header (first row). Than I would like to check the datatypes in the uploaded excel rows. For instance: the current version of the Excel importer stuggles with the American date format (it will insert (and show) the UTC date. So 2015-1-1 will result in 31-12-2014 (and trust me I tested a lot..) Does anyone would like to share experiences/best pratices in their projects?

asked 2015-03-18

Pim van der Noll

1 answers

Jasper van der Hoek · Answer 1 · 2015-03-18

If you want to evaluate the header structure I would recommend leveraging the functionality that is used to create an template from an excel sheet.
When you look at the microflow behind the buttons you can see a template is setup and a Java action creaItes column records. You could re-use this functionality and compare the created columns with the column definition from your correct template. Once everything has been validated you can delete the temporary template and columns.

For evaluating data types, are you using the 'microflow parser' that you can setup with each column? That microflow allows you to parse the plain data from excel to the exact type as you want to receive. For example if dates are stored as text in a notation different from what the exporter expects you can use a microflow to parse the date with the correct localization.

Also I'm currently in the process of re-doing a complete unit test of the module while creating an upgrading that is compatible with the new decimal type in 5.14. I did find issues with the localization of dates. The issues should be fixed now, but if you can provide me with some examples of what you know isn't working I can try and incorporate those in the standard unit tests. And maybe fix your issue before releasing the next version.