We frequently use the import wizard to import files into lists. We tend to use a view here with an instead of trigger, so we can resolve lookups through the view, validate the data and automatically map the column names with the import wizard. This all works tremendously well, but we would like to add something extra.
If rows fail in the import wizard, you can export them later. Unfortunately, what you can't see is why these rows failed. They are color-coded, but you can't display a textual explanation for them. We would like to be able to use the tsf_send_message to return a message, which is updated with the corresponding row in the export with failed records. This way, an end user can see more clearly what needs to be changed and can then try to re-import the export with failed rows.
This is just a possible solution, if there is a better idea to inform a user of the errors, without getting a popup for each row or aborting an import with an error message, that would be very nice.
What I mostly advise when dealing with Excel or CSV imports is to create a staging area and work with a two-stepped approach:
Step 1: Load the data into the staging area where you accept any input from the excel. So even if you expect a number and the user provides a character string you accept this data
Step 2: Process the loaded data from the staging area into your actual tables. This way you can have fine-grained control of what data you load, what checks you do, etc.
Furthermore, when you add an ‘error-column’ into your staging area, you can store all the errors there. This is more convenient because there could be multiple errors on on row.
For example one field is provided as a string while expecting a number and another field should have been a date instead of a number. This way you can provide feedback on both columns to your user in one go.
Hope this helps.